get values from closed workbook(s)

Hi,

I run a report on daily basis that generates a new excel workbook with the name report2013-01-28.xls (obviously the date-part of the file name is changed daily).

What I'm trying to do now is to create a "master file" to get a better overview in change of trends etc. The master file will be built in a way so that each column represents 1 day. The header of each column will be the current date (for example 2013-01-28).

I need a macro that, when run, extracts data from certain cells in the file reportXXXX-XX-XX.xls (where is XXXX-XX-XX is the name in the top row). Preferably the source-file should not have to be open.

I have attached two example-files to show you the idea.

For example, when I click the macro-button in "masterfile.xls" I would like the macro to understand that the last unpopulated colum is B, and in B1 the value is 2013-01-28, and therefore it would access the file report2013-01-28, and for example, extract the value from the cell B23 and put it in B8 in masterfile.xls.

Is this at all possible? Currently I'm doing this manually, and needless to say it's a bit time consuming.

Any help would be greatly appreciated!

Anton

I had a similar thing a few years back and found something on the internet that helped, unfortunately I don't have a link to it or know who deserves credit but it was workable for me.

The files have to be opened to extract data from them but this can be done behind the scene with a minimum of distraction.

Have adapted what I used for your situation in the renamed xls. You need to go into the macro and set up the path to your report files for this macro to work.

Hope this helps
NoS