Referencing Workbook-Level Range Names In An Inactive Workbook

I was never satisfied with all the stuff on the Internet about referencing Workbook Level Named Ranges in other open spreadsheets.  So a looong time ago I dinked around and came up with this syntax:

Range(activeworkbook.name & “!Accounts”).address

That worked great for about two minutes until I realized that this wouldn”t work for files with spaces in their name …  So I came up with this syntax:

Range(“‘” & activeworkbook.name & “‘!Accounts”).address

Notice the subtle apostrphes :-)….

Turns out this will work with every conceivable file name (assuming uyou’re using an English version of Windows).  I’ve used it for years and have never seen anyone else mention it.

Other examples of thos technoque are:

For Each c In Range(“‘” & ThisWorkbook.Name & “‘!tblParentTabs[ParentFile]“).Cells

x=Range(“‘” & “Master Data Migration template _08-21-14 v2 – CHUNK #1 DM1.xlsx” & “‘!Accounts”).address

I know how this works … now someone’s gonna jump on here and propose an alternative syntax and show ways that this won’t work  .. but I think I’ll stick with this one as it works for me and has safely for more than a decade.  You decide I guess :-)

Dick

 

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to Referencing Workbook-Level Range Names In An Inactive Workbook

  1. Giorgio says:

    I find those quotes to be a pain to decipher, why only two quotes at the end and five at the start?

  2. Dick says:

    It’s double-single-double name double-single+morestring+double. So it’s “five” on each end, but the morestring is in the middle of the last five.

    I use ActiveWorkbook.Names(“Accounts”).RefersToRange.Address

    Not sure what breaks that, but it hasn’t happened to me yet.

    • Biggus Dickus says:

      Yeah I’ve used that too … Just another way to skin the cat I guess. Thanx for that.

    • Giorgio says:

      Thanks Dick, I guess yours does away with using all those quotes ;-)

      • Biggus Dickus says:

        Yes it does .. I just like this syntax as it keeps me in the range().value or .offset or . Columns(1) style ….

        It’s really 6 of one 1/2 dozen of another but mine works and is about as long so I thought someone might like it . i should know better than post shit like this because someone always has another way and I really don’t give a shit …

        Dick was right to post that but you can use whichever you want .. Just thought it might suit someone else – not wanting a diacussion of the reliative “purity” of one or the other :-)

  3. Ken Puls says:

    Giorgio, it’s not. It’s quote-apostrophe-quote at the start, and quote at the end. There’s a huge difference there to Excel. In Excel everything between two quotes is treated as text. In this case, Dick is trying to build a ‘workbook_name’!range_name format

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s