In VBA, my VLOOKUP needs to Update Values

Question

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

Can you debug.print fname to show whether it correctly wraps the reference in ' ? it would need to do so for the formula to be valid due to the space in the sheet name. — Zerk, Feb 17 '17 at 15:45

score 1 · Accepted Answer · answered Feb 17 '17 at 15:54

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)"

This worked! Thank you! I'd seen mention of this being the solution in some other threads but the code was different so it didn't make sense. But it does now! — DukeSilver, Feb 17 '17 at 16:58

score 0 · Answer 2 · answered Feb 17 '17 at 15:54

Part of what may be happening is you use the ActiveWorkbook to find the workbook you need versus finding the workbook by the correct name. I use the below subroutine for this purpose:

Sub Get_Workbook_Object(sPath As String, wbHolder As Workbook)
    Dim wb As Workbook

    If Len(sPath) > 0 Then
        ThisWorkbook.FollowHyperlink (sPath)
    Else
        Exit Sub
    End If

    For Each wb In Workbooks
        If wb.FullName = sPath Then
            Set wbHolder = wb
            Exit Sub
         End If
    Next
End Sub

To use this code you could add the subroutine to your module and then call it with something like:

Get_Workbook_Object fPath, openedBook

Also Dir() isn't going to return a fullpath, it is only going to return the appropriate filename. For example, it may return "Hippity Hop.xlsx" instead of "C:Users\Hippity Hop.xlsx" where the first part is the actual filepath. You may want to use something like this instead:

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Please select the CMS All Assets exported CSV"
    .Show
    If .SelectedItems.Count = 1 Then
        fpath = .SelectedItems(1)
    Else
        MsgBox "Please choose at least one file"
        Exit Sub
    End If
End With

This will return the full path of the file.

In VBA, my VLOOKUP needs to Update Values

2 Answers2