Help me with a Macro to extract data from multiple files without opening them?

Hello guys,

Basically I have many files (one file per month) where I have three columns, thus, I need to extract from the files the value in columns 2 and 3 that correspond to IN7500 (this is an item code in colum 1 and the row position is different from month to month).

The file's name and folder is different depending of the month, for intance, the april 2010 file is :

V:\Finance\Month_end\Investment_Trx\2011\BACKOFFICE REPORTS\04-2010\201004 PASTE.xls

the may 2010 is in:

V:\Finance\Month_end\Investment_Trx\2011\BACKOFFICE REPORTS\05-2010\201005 PASTE.xls

and so on.....

I need to do it for a couple of years, thus, instead of doing it manually, I would like to use a macro, can anyone help me out?

Basically these are my questions as well:

*How do I manage to do a loop applying a variable to the month (which would increase by 1 each loop) and then using it to extract the data from the files changing each time the FOLDER and the FILE name based on the month (which I want to apply as a variable to be used in a loop). the forman is YYYYMM



thanks

Perhaps these two macros could be of help?

You have to enter start and stop date as YYYYMM.

Macro will extract data from the closed file. As your description where to find the data was a bit vague I’ve just let it copy the data from A2 to C2 on sheet1 in the closed file to sheet1 in the “target” file.

Depending on the volume of data you whish to retrieve it may be faster to open each file search for the data and close the file.

The macro “Private Function GetValue(path, file, sheet, ref)” is written by John Walkenbach and a description of it is found here:

http://spreadsheetpage.com/index.php...a_closed_file/

Please Login or Register  to view this content.

Alf