I am a newbie in excel macro vba. I have a problem on my vlookup code which refers to another workbook selected by a user.
Here's my code:
Private Sub vlookups()
Dim data_file_new As String
Dim i As Integer
Dim a As String, b As String, path As String
data_file_new = CStr(Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Select new data file for VLOOKUP"))
path = data_file_new
a = "=VLOOKUP(A:A,'[" & path & "]Source'!$A:$AB,28,0)"
b = "=VLOOKUP(A:A,'[" & path & "]Source'!$A:$AJ,36,0)"
i = 7
Do Until Sheets("Macro Template").Cells(i, 1) = ""
Sheets("Macro Template").Cells(i, 37) = a
Sheets("Macro Template").Cells(i, 38) = b
i = i + 1
Loop
End Sub
My problem is that my code doesn't give the correct formula for the vlookup. instead, it gives this formula:
=VLOOKUP(A:A,'[E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source]No Approval Monitoring Log_June'!$A:$AB,28,0)
the correct formula is this:
=VLOOKUP(A:A,'E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source'!$A:$AB,28,0)
Any help would be appreciated.
Thanks!
Try this (Untested)
Explanation:
Application.GetOpenFilename()
returns aVariant
. Handle it as shown in the code above.The formula that you are looking for is
=VLOOKUP(A:A,'E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source'!$A:$AB,28,0)
andRet
will give you a straight File path and nameE:\AP NO APPROVAL\No Approval Monitoring Log_June 2015 xlsx.xlsx
.Vlookup
puts a[]
around the file name. You have to first extract the filename from the file path and reconstruct the entire string. We use theFunction GetFilenameFromPath
in the above code to retrieve that.You don't need to loop cells to enter the formula. You can enter the formula in ONE GO in the entire range.