vlookup on multiple closed workbooks

Hi, I guess this problem might have come up before but I haven't been able to find a solution yet.

My problem is that I have a master workbook that "need" to use vlookup on multiple external workbooks that exists in folder.

For each vlookup in the master workbook I need it to go through all the workbooks in a specific folder to find a match. I know that vlookup doesn't handle external closed workbooks and I have been trying to get Harlan Grove's pull function to work on a single external workbook but have been unsuccessful. I've also tried to look at ReadDataFromAllWorkbooksInFolder() that I found but I have very limited vba skills and this is killing me.

This should be a simple problem to solve right? Could anyone help me in the right direction?

I think that vba code could solve this pretty easily or am I totally wrong?

Hi,

Unless the workbooks are already open, you can't do this with worksheet
functions. If they are, you can do it using the Indirect function.
Otherwise, you would need a macro that changes the definition of the links
just as you would manually using Edit=>Links and changing the source (used
by all formulas). The equivalent to getting the list of links is the
Linksources method of the workbook object.

That should get you started.

Grap the name of the link using linksources and then change the source with
the changelink method of the workbook object.

If you base the names of your files on the current year, you can probably
have this automatically done when the workbook is opened - if the year is
different, the code changes the link.

Regards,
Tom Ogilvy
MVP Excel

Hi,

Thanks to you I have tested Harlan's pull function, which works fine ...
but which is, at least on my PC, very very slow ...

HTH
Carim

Thanks for the reply. Glad the pull function worked for you somewhat.

I gonna try to do a macro with linksources, thanks!

Hi,

I know the kind of effort and time which goes into these kinds of challenges ...
For the benefit of all, would you mind sharing your final code with the Forum ...

All the Best
Carim

Hi, i need the source code for this. i have same problem but donno how to use pull function

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP sit Look for pull.zip

And this site will help you install it

arthurbr,

The website you have mentioned is not opening.