Help determining best approach

[falconace]

Help me figure out the best approach to the problem below. I simplified the example to make it easier to follow but still get the process/problem documented.

I am trying to create a macro that will eliminate a time consuming task of consolidating excel spreadsheets into one file for reporting purposes.

Right now, there is a directory that contains all of the source excel files. These files are all of the same format containing data in the same cells on the same worksheets, etc. The number and names of the source files change (so I can't hard code file names in and just set the cells equal to their source files), but their directory's path in relation to the the "report" file is always the same. For example, today the path may be C:\data\inputs\source\. Tomorrow it may be C:\data\newfolder\inputs\source. The "report" file is always contained in the directory that contains the inputs folder.

I want to be able to run a macro (or any other suggestion you have) to open the "report" file and pull data from all of the .xls files in the source directory. So for example if today I have 5 source files, I would have 5 rows of data imported. If tomorrow there are 8 source files, I would have 8 rows of data imported in the "report" file. All of the source files are the same structure, just different data values.

Please let me know of any approaches you think would be best to solve my problem. I don't want to start going down one path if there is a better solution out there. Feel free to post code examples if you want... I can follow them fine (spent several years as a developer).

[rylo]

Hi

Is it possible to have the macro in the Report file? That way, you will be able to access the data file based on the directory / path of the report file.

If not, then where do you want to have the controlling macro, and what, if any, relationship is there to the report / data files?


rylo

[falconace]

Yes, I want to have the macro in the report file. Just wondering if you guys had any suggestions for best practices, etc. in tackling something like this.

[rylo]

Hi

"Best Practices" - maybe not, but this should get you going.

Please Login or Register  to view this content.

This will change the drive and path to be the same as the calling workbook.

It then will work through the .xls files in that directory. If the file name isn't the same as the calling macro, then it will process.


HTH

rylo