Vlookup Calculation from another Excel file

[tjh]

At one point in one of my macros, I open another Excel File (I will call this
newly opened file FileO to make this read easier).

The macro then changes a Vlookup formula in the current file to link to a
range in FileO.
The formula is then Re-Calculated.

The macro then closes FileO. And the macro stops, with the current file
still open.

When I close FileO the current file's Vlookup formula gives a reference
error, if I
recaluclate. But it works fine if FileO is open.

Is there an option I need to change? Or is this something that happens when
you use a Vlookup to another file? I have not used many Vlookups with other
files in the past, normally just the current file.

Thank You,

 

[Tom Ogilvy]

Are you using Indirect in your formula. Indirect doesn't work with closed
workbooks.

 

[tjh]

Yes, I am using Indirect to reference the range in the other workbook through
the vlookup. The Indirect($AZ$1) is the file and range name.

=VLOOKUP($A11,INDIRECT($AZ$1),O$6,FALSE)

Is there another easy workaround to the Indirect problem, or would I need to
change each of these formulas throught the VBA code.

Thank You,

 

[Tom Ogilvy]

If the workbook being referenced is close, then there is no easy workaround
to the indirect problem. You can search the groups for a post by Harlan
Grove where he posts his PULL function which is a User defined function
which will do this. However, this function opens the referenced workbook in
another instance of excel, retrieves the data and then closed it and the
instance of excel. I doubt that it is very fast if you need a number of
cells to have the formula.

The other way is to use event code to update the formula with a hard coded
link.

 

[tjh]

Thank You for your response.

1) I wanted to try out the Pull function that Harlan created so, I placed it
into a module, but I am still not sure how to make it work. Do I need to
alter the Vlookup or the value within the indirect cell that refers to the
other excel file and range?

2) How would you suggest using event code?

Thank You,