VBA Function to copy data

[Red Spot]

Hi,

I'm having a bit of a problem with a function.
I want to get data from several files copied into 1 file, but I want the actual locations/files/tabs in the file that copies the data.

If I do something simple as:

In cell:

Please Login or Register  to view this content.

In VBA:

Please Login or Register  to view this content.

Its actually works
But what I want is:

In cell:

Please Login or Register  to view this content.

In VBA:

Please Login or Register  to view this content.

^Fails

Please Login or Register  to view this content.

^Results in having the filelocations in the cells.

I just cant seem to get it to work, unless I use 'indirect' in the formula, but that means I have to open the other files, which I dont want to do.

Interesting thing is, I can easilly do this in a regular macro, but I seem to fail at getting it done from a function.

Some help in getting to the point I can get the data from the sourcefile while using the filelocations in the destination file would really be apreciated.

[AlphaFrog]

This link may help.
Excel "Pull" Function: Creating dynamic links to closed workbooks

[Red Spot]

Havent tried it yet, I will tomorrow.
Thank you for your help

Edit: I've tried the code, it worked, I adapted it to work a bit more specific .. only to find out it doesnt recalculate itself unless you actually activate the cells en force a recalculation.
That means I cant use it cause I want to set up the destination file once and never bother with it again.

Is there any way I could overcome this not recalculating cells bit? I surelly dont want it to do so often as it takes a long time to actually compare files and copy data over, but having it recalculate when opening the file would be nice.

[Red Spot]

I dont like to double post or bump my own topics, but I need this to be done on monday and I'd hate finding myself being forced to start working with alternative methods. Any help would really be apreciated.

I first use one function to search the row the specific data is on in the source-file:

Please Login or Register  to view this content.

The above row then gets parsed together with a file location and column to the modified function linked 2 post back:

Please Login or Register  to view this content.

[AlphaFrog]

To force all formulas to recalculate when you open the workbook, put something like this in the ThisWorkbook code module.

Please Login or Register  to view this content.

You can manually force your UDF GH_GetData to re-calc by using Ctrl+Alt+F9

Here's a good primer on how Excel’s Smart Recalculation Engine works

[Red Spot]

Thanks for the help guys. I updated my code a bit to try and speed up the gathering of data, but its just too darn slow using the Excel4macro.

Please Login or Register  to view this content.

So I just made it a startup macro:

Please Login or Register  to view this content.

Edit: Below the final code for the file.

Please Login or Register  to view this content.