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🙂