File name in Vlookup

[sa02000]

I am trying to do vlookup in a macro. Is there any way I can specify a table from a sheet in a workbook that is not open for second argument of vlookup?

For this format
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC4,[current_file.xls]overall!R2C1:R1000C15,4,FALSE)"
I have to have current_file.xls open

It gave me errors when I tried this

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC4,[E:\folder 1\folder 2\current_file.xls]overall!R2C1:R1000C15,4,FALSE)"

What is the correct syntax to specify this file in vlookup so that I dont have to have that file open?

Thanks,
Jay

[Barb Reinhardt]

I've had to use INDIRECT.EXT to get data from closed workbooks. I don't
know how it would work for your application.

http://xcell05.free.fr/english/index.html



"sa02000" wrote:

>
> I am trying to do vlookup in a macro. Is there any way I can specify a
> table from a sheet in a workbook that is not open for second argument
> of vlookup?
>
> For this format
> ActiveCell.FormulaR1C1 = _
>
> "=VLOOKUP(RC4,[current_file.xls]overall!R2C1:R1000C15,4,FALSE)"
> I have to have current_file.xls open
>
> It gave me errors when I tried this
>
> ActiveCell.FormulaR1C1 = _
> "=VLOOKUP(RC4,[E:\folder 1\folder
> 2\current_file.xls]overall!R2C1:R1000C15,4,FALSE)"
>
> What is the correct syntax to specify this file in vlookup so that I
> dont have to have that file open?
>
> Thanks,
> Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=475574
>
>