0

I am trying to create a block of code that allows one to receive the name of an unknown workbook, and reference data from it. Referencing by index number doesn’t seem like a viable option as users will often have other workbooks out at the same time. The unknown workbook is already open, but I need to retrieve its name. I am using a FileDialog to allow the user to select the desired workbook, save the filepath as a string, then use it to reference the workbook.

When I attempt to reference the workbook, I get a Subscript Out of Range error. Directly inputting the name of the workbook instead of a variable worked just fine, but attempting to use a variable didn’t work. I tried using quotes, no quotes, and double quotes, but the error remained the same.

Here is my code. What exactly was I doing wrong?

Sub ObtainExternalWorkbook()

'Declare variables
    Dim filepath As String
    Dim WK As FileDialog
    Set WK = Application.FileDialog(msoFileDialogFilePicker)

'Acquire file name.
  MsgBox ("Please select the desired file to be used.")
        With WK
            .AllowMultiSelect = False
            .Title = "Select the desired file."
            If .Show = False Then
                Exit Sub
            End If
        filepath = .SelectedItems.Item(1)
        End With


'Use file name to activate workbook.
    Workbooks(" & filepath & ").Activate
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
FEZ_R
  • 5
  • 3
  • 1
    You need to open it before you can activate it. – braX Jan 29 '20 at 12:51
  • @braX: Op said "the unknown workbook is already open", so while it might be useful to add some code to check that it actually is open (and an Excel-file). I don't think that's the issue. – Beek Jan 29 '20 at 13:10

1 Answers1

1

Considering you say the workbook is already open, this has to be the case to activate, the problem is your variable filepath. this returns the full path and name. This SO post gives you a function to extract only the filename and extension.

Try filling a filename variable via that function after a file has been chosen and then Workbooks(filename).Activate should work.

Beek
  • 376
  • 1
  • 10