Vlookup from close sheet via VBA

[Dan]

Hi,
I managed to do a vlookup from a closed file via:
=VLOOKUP(A1,'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18,3,FALSE)

but I do not manage to do it by code - I think the problem is that I do not
know the sytax to put 'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18 in VBA.
Can you help, many thanks,
Dan

 

[Joel]

FormualStr = "=VLOOKUP(A1,'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18,3,FALSE)"
Range("B1).formula = FormulaStr

 

[Dan]

Thanks, but that is not what I am after- you are just assigning a formula
via code - nothing to do with vlookup.

I want to use Application.VLookup...

Thanks

FormualStr = "=VLOOKUP(A1,'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18,3,FALSE)"
Range("B1).formula = FormulaStr

Hi,
I managed to do a vlookup from a closed file via:
=VLOOKUP(A1,'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18,3,FALSE)

but I do not manage to do it by code - I think the problem is that I do not
know the sytax to put 'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18 in VBA.
Can you help, many thanks,
Dan

 

[Mike H]

Hi,

You can't do a Vlookup in VBA on a closed workbook so you have options.

1. fleeting open the workbook do the lookup and capture the value
or
2. Write a formula to the worksheet capture the value and delete the formula

Mike

 

[Dan]

Thank you

Hi,

You can't do a Vlookup in VBA on a closed workbook so you have options.

1. fleeting open the workbook do the lookup and capture the value
or
2. Write a formula to the worksheet capture the value and delete the formula

Mike

Hi,
I managed to do a vlookup from a closed file via:
=VLOOKUP(A1,'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18,3,FALSE)

but I do not manage to do it by code - I think the problem is that I do not
know the sytax to put 'C:\temp\[test3.xls]Sheet1'!$A$1:$C$18 in VBA.
Can you help, many thanks,
Dan