Automatically open and close external workbook for indirect function

Hi everyone,

I found the following bit of genius code from vane3261 he posted back in 2006 which is supposed to open an external workbook with a wildcard filename in order for an INDIRECT function formula to obtain results:

Please Login or Register  to view this content.

The INDIRECT code I'm using is as follows:

Please Login or Register  to view this content.

When I try to run the code I get error message: Object doesn't support this action and With Application.FileSearch is highlighted in yellow. Can anyone give me a hand in getting this corrected?

Thanks!

Hi, Groovicles,

if you searched to find a code from 2006 you should have found all the threads as well stating that Application.FileSearch was abandonned with Office/Excel2007. Maybe use Dir() instead.

Ciao,
Holger

Hi, Groovicles,

maybe like this:

Please Login or Register  to view this content.

Ciao,
Holger

Hi Holger,

Thanks very much for the code. I tried it out this morning and unfortunatelly nothing happened. The external workbook did not open and I did not get any error message. Its funny. I put the code into the worksheet by right clicking on the worksheet tab and pressing View Code, but I don't see this code as a macro in my macro list. Am I missing something perhaps?

Hi, Groovicles,

unfortunatelly nothing happened

Code goes behind the sheet where you change any entry. Go into the VBE and press F9 in then code line

Please Login or Register  to view this content.

and close the VBE. Any change in the worksheet should lead you into the VBE to have the possibility to step through the code by using F8 for finding out why the code won´t work. The first line of code will check and allow to work with the following code if Range C1 is changed and that´s the only cell to be changed on that event.

The mark you set will be deleted once you close the file or by pressing F9 on that line again.

Ciao,
Holger

Instead if trying to use VBA to open a file for INDIRECT() to work, take a look at installing the MoreFunc, which will allow INDIRECT to to reference closed workbooks?

Hi, FDibbins,

would MoreFunc help when

Please Login or Register  to view this content.

is set? Sorry, have not installed that AddIn.

Ciao,
Holger

Holger, I have no idea, sorry

Hi, Groovicles,

please check the name of the file which is laid down in C1 and the path to it as if that workbook doesn´t exist (this returns a length of 0) no workbook will be opened. As yoiu didn´t pass any informatio about what is in cell C1 you are the only one to check that out.

Ciao,
Holger