Using Workbook.Sheets.Range to reference file path in Excel

Question

First, is what I'm doing here logical? Second, I keep getting an error on my single quote that begins my filepath.

stuff = Workbooks('\\public\Documents\Amazon Retail\Analysis\[US Retail Quick Reference.xlsx]').Sheets("Quick Reference").Range("A1")

1) change `'` to `"` (but then you may need a double backslash?: `"\\public...`) 2) if `stuff` is to reference a `Range` then use a `Set` keyword before `stuff` — DisplayName, Apr 02 '18 at 19:06
Done, but it still gives me a "Subscript out of range" which means It can't find my file (the file path is 100% accurate). — SavageBandit, Apr 02 '18 at 19:14
What is the error that you're receiving? Including it in your answer helps everyone... — Evan Darwin, Apr 02 '18 at 19:15
one thing only is really _"100% accurate"_ in human life; but that is too sad to remember ... double check the path! (may be with a `Dir()`). — DisplayName, Apr 02 '18 at 19:15
Run-time error '9': Subscript out of range. Checked the file path a dozen times. — SavageBandit, Apr 02 '18 at 19:16
If that file is already open, you have to reference it by its *name* only, not the full path. If the file isn't yet open, you need to open it first (and then refer to it by its name only). — David Zemens, Apr 02 '18 at 19:21
The file isn't local. Is there a way to reference a non-local file path? — SavageBandit, Apr 02 '18 at 19:24
**You can't use *`Workbooks`* to refer to a file that isn't already open**. (*`Workbooks Object (Excel)`*: A collection of all the Workbook objects that are *currently open* in the Microsoft Excel application.) ...when referring to a file is **is** open, you shouldn't use the path (as you'll get an error.) — ashleedawg, Apr 02 '18 at 19:25
@DavidZemens showed you the right path, valid for local and not local files — DisplayName, Apr 02 '18 at 19:25
You cannot have two files with the same *Name* open in a single instance of Excel. This is only possible if you're using multiple instances of Excel, in which case the problem becomes more difficult because you need to identify which instance (of however many) may have the file open, and that's far more complicated using `WinAPI` calls like `FindWindow` etc. — David Zemens, Apr 02 '18 at 19:39

David Zemens · Accepted Answer · 2018-04-02T20:09:02.380

Assuming you have a single instance of Excel, and are not using multiple instances of Excel:

If that file is already open, you have to reference it by its name only, not the full path. If the file isn't yet open, you need to open it first (and then refer to it by its name only).

Change this:

stuff = Workbooks('\\public\Documents\Amazon Retail\Analysis\[US Retail Quick Reference.xlsx]').Sheets("Quick Reference").Range("A1")

To This:

stuff = Workbooks("US Retail Quick Reference.xlsx").Sheets("Quick Reference").Range("A1")

Ensure stuff is declared as a String or possibly as a Variant type (in case A1 might contain non-text or error values).

If you don't know at runtime whether the file is or may be open, then you can fancify your code like so:

Function IsWorkbookOpen(path as String, name as String) As Boolean
    Dim wb as Workbook
    On Error Resume Next
    Set wb = Workbooks(name)
    If wb.FullName = path & name Then
        IsWorkbookOpen = True
    End If
End Function

And then do like:

Dim path as String, fileName as String
path = "\\public\Documents\Amazon Retail\Analysis\"
fileName = "US Retail Quick Reference.xlsx"
If (IsWorkbookOpen(path & fileName)) Then
    stuff = Workbooks(fileName).Sheets("Quick Reference").Range("A1").Value
Else
    ' Do Something Else // UNTESTED:
    stuff = ExecuteExcel4Macro("'" & path & "[" & fileName & "]" & _
          "Quick Reference'!" & Range("A1").Address(True, True, -4150))

    ' or:
    ' Dim wb as Workbook
    ' Set wb = Workbooks.Open(path + fileName)
    ' stuff = wb.Sheets("Quick Reference").Range("A1").Value
    ' wb.Close

End If

For the "Something Else", I'd recommend using the ExecuteExcel4Macro method for obtaining value from a closed workbook.

Using Workbook.Sheets.Range to reference file path in Excel

1 Answers1