Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Processing multiple files with same macro

Uni_User
5 - Atom

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.

4 REPLIES 4
TomWelgemoed
12 - Quasar

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.

Uni_User
5 - Atom

Thanks for the suggestion to do it without a macro. I think that will be the best way to go if possible.

 

My idea is to combine all the individual Actual Sales input files into one, process them into a format that I can match to the Target Sales file data records, and then find/replace to append the Actual Sales data to the Target Sales records.

 

I'll post again with the results or further questions.

TomWelgemoed
12 - Quasar

@Uni_User ,

 

Think that is right, yes. Good luck and shout if you're stuck!

Uni_User
5 - Atom

Hi Tom,

 

I did a workflow as you suggested instead of a macro and it worked fine. Thanks for the idea.

Labels