Question Auto run an Excel macro for all files in a folder

[roberttran52002]

Hi all,

I have a a macro in a separate excel file and want it to automatically run on all excel files in a certain folder (c:\temp). As I have so many files that need to be excecuted with the same command, if I have to manually open each file and run the macro, it will be very time consuming and I may miss out some files unnoticeable.

Thank you very much for any advice!

Robert

 

[DCrake]

Alot will depend on what the macro is doing more info would be helpful

David

 

[roberttran52002]

Sorry for not making it clear!

Actually I want to have codes that help automatically open all files in a folder (c:\temp\...) & execute a macro (which had been created, named "Tranferring_data" & stored in "c:\Master data file.xls"). This is to import data from individual file to a "master data file".

All the individual files are identical in format, structure and can be run with the above mentioned macro.

Thank you very much!

Robert

 

[DCrake]

So to get this right you are attempting to merge the contents of many workbooks in to a single master workbook?

I know it seems obvious but why do you need to do this? For example do you want sheet 1 from each workbook to be copied into a singl worksheet in the master workbook?

David

 

[roberttran52002]

Hi David,

Since the data in each file are not consolidated in 1 worksheet but placed here & there ie. in sheet1, I need to get data from C12, B15, AE71...in sheet2 I just need A105, B32 to B45, AC17 to AF32...and from many other sheets.

Thanks!

Robert

 

[DCrake]

What I may be tempted to do, and this is only my opinion, is to create a temp table in Access and use Excel automation to iterate through all the known workbooks and grab the data and save it to the table. Then on completion open the master workbook and copy the data enbloc to the desired worksheet.

Doing it that way you can also perform validation as you progress.

David