vbaexcelvlookupgetopenfilename

In VBA, my VLOOKUP needs to Update Values

I'm writing a script that requires opening a second workbook and running a VLOOKUP in the second workbook. It works perfectly when the filename of the second workbook is "testlookup.xlsx" but when I changed the filename to "hippity hop 1251225253.xlsx", it opens a window that says "Update Values: 1251225253" and then the VLOOKUP fails. How can I get the code to work regardless of the filename?

fpath = Application.GetOpenFilename(, , "Select the CMS All Assets exported CSV")
fname = Dir(fpath)

Workbooks.Open (fpath)
Set openedBook = Application.ActiveWorkbook
Set assetBook = openedBook.Worksheets(1)
ActiveWindow.WindowState = xlMinimized

checkWkbk.Activate
With dupeSheet
    'determine last row
    lr = .Cells(Rows.count, 1).End(xlUp).Row
    'vlookup from C2:CEnd
    .Range(.Cells(2, 3), .Cells(lr, 3)).FormulaR1C1 = _
        "=VLOOKUP(RC[-2], " & CStr(fname) & "!C1:C2, 2, FALSE)"
End With
Solution

  • If your description of the filenames is correct, the problem is that you're using a file name with space characters in it, which is throwing the VLookup off. You need to put single-quote characters around the file name in the formula, thus:

    "=VLOOKUP(RC[-2], '" & CStr(fname) & "'!C1:C2, 2, FALSE)"

    I may be off base with this bit, since you said it works when you don't have spaces in the file names, but you should also include the worksheet name in the formula string, so your formula would look more like this:

    "=VLOOKUP(RC[-2], '[" & CStr(fname) & "]" & assetBook.name & "'!C1:C2, 2, FALSE)"