How to reference a user defined workbook and worksheet excel VBA

Question

I have a snippet of VBA code where I have the user select a file to refer to for a vlookup, and assign this to a variable, UserFile. Late on in the code, I have a snippet that wants to refer to a sheet in UserFile.

It works when I put in the actual name of the file, how can I make it work so that it refers to UserFile, as opposed to the named file (it changes daily)

This works:

Sub Macro5()
        Dim MyFile As String
        UserFile = Application.GetOpenFilename()

        ActiveCell.Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(RC[-5],'[M2606170810.xlsx]Availability'!R3C1:R321C24,9,0)"
        ActiveCell.Select
        Selection.Copy
        ActiveCell.Range("A1:A37").Select
        ActiveSheet.Paste
    End Sub

This does not (only change is in the vlookup formula):

Sub Macro5()
    Dim MyFile As String
    UserFile = Application.GetOpenFilename()

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-5],'[UserFile]Availability'!R3C1:R321C24,9,0)"
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Range("A1:A37").Select
    ActiveSheet.Paste
End Sub

I tried Reference an excel sheet from another workbook without copying the sheet but to no avail.

I want the option for the user to select the correct file to refer to, and wanted to add this in the flow

You declare `MyFile` and assign `UserFile`, for starters. – Mathieu Guindon Jun 28 '17 at 16:36 — Mathieu Guindon, Jun 28 '17 at 16:36

Domenic · Accepted Answer · 2017-06-28T16:47:35.697

2

You'll need to split the path and filename so that it can be placed within the formula in the appropriate spots. Also, you can avoid using the Select method, since it's not really needed and very inefficient. Try...

Sub Macro5()

    Dim UserFile As String, MyPath As String, MyFile As String

    UserFile = Application.GetOpenFilename( _
         FileFilter:="Excel Files (*.xls;*.xlsx), *.xls;*.xls", Title:="Select a file")

    If UserFile = "False" Then
        MsgBox "User cancelled...", vbInformation
        Exit Sub
    End If

    MyPath = Left(UserFile, InStrRev(UserFile, "\"))
    MyFile = Mid(UserFile, InStrRev(UserFile, "\") + 1)

    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-5],'" & MyPath & "[" & MyFile & "]Availability'!R3C1:R321C24,9,0)"

    ActiveCell.Copy Destination:=ActiveCell.Range("A1:A37")

End Sub

You'll notice that I also added a few lines to allow the user to cancel the dialog box. And I added a FileFilter for the GetOpenFilename method to ensure that only the correct file type (ie. xls or xlsx) is selected by the user. Change as desired.

edited Jun 28 '17 at 16:47

answered Jun 28 '17 at 16:07

Domenic

7,844
2
9
17

1

That would refer to the file itself. I think you mean `Userfile.Name` or `Userfile.Fullname` :). – Brandon Barney Jun 28 '17 at 16:11
@BrandonBarney `UserFile` is just a[n undeclared] [variant/]string. `UserFile = Application.GetOpenFilename` – Mathieu Guindon Jun 28 '17 at 16:35
@BrandonBarney Thanks for pointing it out to me. I've edited my post accordingly. – Domenic Jun 28 '17 at 16:39
@Mat'sMug My bad. I completely skipped over reading the code and just assumed I knew what the variable meant. Please ignore my comment :p. I must not have been paying attention! – Brandon Barney Jun 28 '17 at 17:08

How to reference a user defined workbook and worksheet excel VBA

1 Answers1