VBA adding variable (filename) to a vlookup

toneloc

New Member
Joined
Sep 22, 2009
Messages
7
Hi,

I am trying to pass a string variable to a vlookup formula in VBA but the output is not working correctly.

I have the following code :-

Dim cFileName As String

MsgBox "Please browse for the latest CAT file on your hard drive, and select"
cFileName = Application.GetOpenFilename
'They have cancelled.
If cFileName = "False" Then Exit Sub
Workbooks.Open Filename:=cFileName

Windows("Voucher Backupxxxxxxx.xls").Activate

Sheets("LOH").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[" & cFileName & "]HC'!E:G,3,FALSE)"

The output from this is as follows :-

=VLOOKUP(B7,'[C:\Documents and Settings\.........\Test\CAT.xls]HC'!E:G,3,FALSE)

Which results in #NAME as the "[" bracket does not bracket the CAT.xls] but appears at the beginning of the filename but I can't figure out how to fix it.

Can anyone assist please?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Give this a try:

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3]," & "[" & cFileName & "]" & "HC!E:G,3,FALSE)"

Hope this helped,
Rolf
 
Upvote 0
Thanks very much for the quick response - I have tried this but am getting an application-defined or object-defined error.

I think it is now correctly bracketing the filename [CAT.xls] but think it is also putting [ ] around the entire pathname
 
Upvote 0
Have sorted this - had to remove the full path from the filename


Dim posn As Integer, i As Integer
Dim fName As String

'Create short file name for CAT file
posn = 0
'find the position of the last "\" character in filename
For i = 1 To Len(cFileName)
If (Mid(cFileName, i, 1) = "\") Then posn = i
Next i
'get filename without path
fName = Right(cFileName, Len(cFileName) - posn)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top