Vlookup from close sheet via VBA
 

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

Vlookup from close sheet via VBA
 

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


"Dan" wrote:

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

Vlookup from close sheet via VBA
 

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

"Joel" wrote:


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


"Dan" wrote:

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

Vlookup from close sheet via VBA
 

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" wrote:

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

Vlookup from close sheet via VBA
 

Thank you

"Mike H" wrote:

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" wrote:

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