Trouble with VLOOKUP and GetOpenFileName

[mwhassan]

Hi all, I am hoping that I am just having a syntax problem here.

I am using GetOpenFileName to get the name of a database that is downloaded from a webserver and use it as a comparison database for a file that I am working with. Problem is that the name of the file changes constantly and I need to preserve the name so I can't just call it the same thing all the time.

I know I could re-save as a constant name but I think that's a bit sloppy and I may need to distribute this Macro throughout my unit and want to keep it as simple as possible for the enduser.

Anyway here is my issue, if I am using 'x' as my variable for GetOpenFileName, how do I use that variable in a VLOOKUP formula i.e.

(ActiveCell.FormulaR1C1 = "=UPPER(VLOOKUP(RC[-13], ['x']Sheet1!C1:C8, 8, FALSE))

 

[MrKowz]

Try:

(ActiveCell.FormulaR1C1 = "=UPPER(VLOOKUP(RC[-13], ['" & x & "']Sheet1!C1:C8, 8, FALSE))

 

[mwhassan]

Nope that did not work you have any other thoughts?

 

[MrKowz]

What value is stored in x when it gets to this line?

 

[mwhassan]

Should be the File name here is the code I am using

x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Choose File to Copy", MultiSelect:=False)

MsgBox "You selected " & x

I even executed the " MsgBox "You selected " & x " just before where the program stops and the correct file name comes up.

 

[MrKowz]

Try this:

Dim x As Variant
Dim y As Variant

x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Choose File to Copy", MultiSelect:=False)
y = Split(x, "\")
x = Left(x, InStr(x, y(UBound(y))) - 1) & "[" & y(UBound(y)) & "]"
ActiveCell.FormulaR1C1 = "=UPPER(VLOOKUP(RC[-13], '" & x & "Sheet1'!C1:[B]K8[/B], 8, FALSE))"
End Sub

The problem likely is that the filename wasn't encased in [ ]. The path stays outside of the []. Also, your VLOOKUP's range was one-dimensional (it had no 8th column to look at), and there was a mismatched parethases.

 

[mwhassan]

Worked but much slower, also asks me for file location two additional times and sheet1 locations as well.

The C1:C8 in the VLOOKUP stand for column 1 through column 8. Missing parentheses was simply a cut and paste error.

I thought this would just be a syntax error, I have had these issues with VLOOKUPs before. They work great in a cell but I have not had too much luck with them in VBA unless I hardcode. If you have another solution that would be great if not I'll have to figure something else out

Thanks for all your help