0

I am trying to create a macro which includes a VLOOKUP but the VLOOKUP file would change each time. I would like the reference file in the VLOOKUP to be a variable. Ideally the macro would prompt the user to choose a file they wish to VLOOKUP from. So far I have this but it doesn't seem to be working...("test" is what the worksheet is named).

Sub VLOOKUP()

Application.ScreenUpdating = False

Dim myFilename As String

MsgBox "Please choose file with name to use in VLOOKUP formula.", vbOKOnly, "Choose file"
myFilename = CStr(Application.GetOpenFilename)

Range("M12").FormulaR1C1 = "=VLOOKUP(RC[-11],'[" & myFilename & "]test'!C9:C10,2,0)"

End Sub

However, the VLOOKUP in the cell is not showing up how it should. e.g.

=VLOOKUP(B12,'[G:\OPS\National Pricing Data And Risk\Vehicle Pricing\VP Work\Gareth\Even Newer Toyota Macro Test\[Z401 Toyota Test COMPLETE.xlsx]test]Z401 Toyota Test COMPLETE.xlsx]'!$I:$J,2,0)

I'm not sure where the extra ]Z401 Toyota Test COMPLETE.xlsx] is coming from. Is there something I'm missing/not doing correctly?

Edit:

Sub VLOOKUP()

Application.ScreenUpdating = False

Dim fullPath As String
Dim tmpName As String
Dim tmpPath As String
Dim myFilename As String

fullPath = "G:\OPS\National Pricing Data And Risk\Calculators, Docs, Templates & Guides\Toyota Macros.xlsm"
tmpName = fso.GetFileName(fullPath)
tmpPath = fso.GetParentFolderName(fullPath)
myFilename = tmpPath & "\[" & tmpName & "]"

Range("M12").FormulaR1C1 = "=VLOOKUP(RC[-11],'[" & myFilename & "]test'!C9:C10,2,0)"

End Sub
VBA Pete
  • 2,656
  • 2
  • 24
  • 39
Gareth
  • 223
  • 1
  • 3
  • 12

2 Answers2

1

Isn't VLOOKUP a "reserved word" in Excel? Your function may not be working because you're trying to use an Excel function that already exists. Maybe try calling your function "MYVLOOKUP" and see if that works.

Brian

B. Torreano
  • 13
  • 2
1

Excel doesn't like the way you've formatted your filename. You need to split it into directory and name.ext so you can format it like this: 'c:\path\to\file\[filename.ext]worksheetName'!F1

Edit

I forgot that this isn't as straightforward in VBA as it is in other languages. The easiest way is to use the FileSystemObject, but to use that you first have to reference it. You can see instructions on how to add the reference in this stackoverflow answer: https://stackoverflow.com/a/1755577/2295754.

And here's an example of how to use the FileSystemObject, in case you need a little more guidance than the other stackoverflow answer gave.

Dim fso As New FileSystemObject

fullPath = "D:\Ashby\Documents\test2.xlsx"
tmpName = fso.GetFileName(fullPath )
tmpPath = fso.GetParentFolderName(fullPath )
myFilename = tmpPath & "\[" & tmpName & "]"

Edit2

So, I intentionally left out the actual use of myFilename last time hoping you'd pick that up on your own. Oh well, here it is:

Range("M12").FormulaR1C1 = "=VLOOKUP(RC[-11],'" & myFilename & "test'!C9:C10,2,0)"
Community
  • 1
  • 1
ashbygeek
  • 759
  • 3
  • 20
  • Me being the rookie that I am...how do you do that? Do I need to create a new variable named "directory" and assign it something then add it in with the filename? – Gareth Jan 25 '17 at 00:56
  • Edited the post to answer your question – ashbygeek Jan 25 '17 at 01:25
  • Thanks mate, I've tried it with that but I'm not sure I've got it correct. I've added it in the opening post, can you see where I've messed up? – Gareth Jan 25 '17 at 05:02
  • Ah, you seem to have missed the punchline. I'll edit my post again. – ashbygeek Jan 25 '17 at 11:22
  • Sorry, I'm not sure what I'm doing wrong but it's not working. What is the purpose of the fullPath again? What should I be putting in there? Not the actual file yeah, otherwise it wouldn't be re-usable? – Gareth Jan 31 '17 at 05:38