Processing multiple files with same macro

Uni_User

Hi,

I’m trying to process five files using a macro, and then find/replace the clean data to match actual sales to a spreadsheet with targets. I have a macro that does one file but I can’t work out how to process all files with the same macro. Any help appreciated.

Details:

In one file I have the target for each division based on full-year predictions:

Div.        Item                      Area                Route                    Target

Green   Widget A             Southeast           Direct sale           2,500

Green   Widget A             Southeast           Distributor          10,000

Blue       Product X             Northwest          Distributor          5

I also have a set of files for each division. Each file has actual units sold in it by product, region and other criteria.

E.g.

Green.xlsx

Widget A-SE, Dir. sale, 1,089 units

Widget A-SE, Dist., 2,515 units

Blue.xlsx

Product X-NW, Dist., 10 items

I need to update the files to list each division’s target and actual sales together producing something like:

Performance.xls

Green tab:

Div.        Item                      Area                      Route                    Target                   Actuals to date

Green   Widget A             Southeast           Direct sale           2,500                     1,089

Green   Widget A             Southeast           Distributor          10,000                   2,515

 

Blue tab:

Div.        Item                      Area                      Route                    Target                   Actuals to date

Blue       Product X             Northwest          Distributor          5                              10

I have built a workflow that cleans up one Actuals file (Green) and does a find-replace on the Green target data.

What I would like to do is design a macro that will take each Actuals file in turn, run it through the workflow, find-replace it on the Target spreadsheet data (and then split it into tabs).

I’m having trouble getting started. Does anyone have a suggestion?

Thanks.

TomWelgemoed

This is a quick reply as I might be missing some facts: try to see if you can avoid going down the macro route first. Could you for example do any of the following?

• Use a wildcard (e.g \mydirectory\*.xlsx) to select all files that are in the same format? (assuming they are the same format)
• Use the directory tool to query the directory to extract files (again, if in same format)?
• Download the crew macros from http://www.chaosreignswithin.com/ and install it - there is a great wildcard excel tool that helps you select Excels of different formats in the same directory

What I would try to do is to get the content onto the canvas using 1 of the above approaches and clean it based on the filename (make sure to output the filename as part of the input tool used). Then you can append the targets to it thereafter.

Let me know if I misunderstood you.