vlookup from another file location vba

[GreenyMcDuff]

Hi there,

hopefully quite a simple question:

I am trying to vlookup some values into a sheet from a workbook located in another folder

Here is what I have so far:

    Range("H9").Select
        Selection.Resize(LastRow - 8).Formula = "=vlookup(Cells(9,2).Address(False,True),'N:\Sheet1 & Format(DateSerial(Year(Now), Month(Now()) - 1, 0), "mmyy").xls Summary (Net of Fees)'!$A:$M,3,0)"

As you can see the lookup needs to refer to a different file each month. I am fairly sure this is just a syntaxing issue so I would be grateful to anyone who can show me the light

Thanks

Chris

 

[Jonmo1]

Can you post the formula as you expect it to appear in the cell after running the macro.

 

[Jonmo1]

Here's a best guess...

Range("H9").Resize(LastRows - 8).Formula = _
"=vlookup(" & Cells(9, 2).Address(False, True) & ",'N:\Sheet1\[" & Format(DateSerial(Year(Now), Month(Now()) - 1, 0), "mmyy") & ".xls]Summary (Net of Fees)'!$A:$M,3,0)"

 

[GreenyMcDuff]

Hi here's how I expect it to look:

=vlookup($B9,'N:\Sheet1 0711.xls'Summary!$A:$M,3,0)

I'm not entirely sure on the synatx to reference another workbook

Thanks for the replies

Chris

 

[Jonmo1]

Is Sheet1 part of the Path or the filename?

you need [] around the filename.

 

[Jonmo1]

The correct syntax would be like

=VLOOKUP($B9,'N:\Path\[filename.xls]SheetName'!$A:$M,3,0)