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
Dick Moffat's Spreadsheet and BI Blog 
I find those quotes to be a pain to decipher, why only two quotes at the end and five at the start?
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.
Yeah I’ve used that too … Just another way to skin the cat I guess. Thanx for that.
Thanks Dick, I guess yours does away with using all those quotes 😉
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 🙂
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