Alteryx Designer Desktop Discussions

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

Output to Excel using batch macro

bkoons
7 - Meteor

I am trying to create a batch macro that will output results to multiple files. I have cash flows (A,B,C) that get output to excel workbooks (X,Y,Z) in this way:

 

X needs A & B

Y needs B

Z needs B & C

 

I have the first input into the batch macro that lists the file path and tab names for X,Y,Z and I have a second input table that lists the cash flows concurrently. There is a cash flow (CF) for each mapping, meaning cash flow B is listed in the table three times, each with a field listing the path and tab of the destination (dp):

 

CF-A (dp-X, tab A)

CF-B (dp-X, tab B)

CF-B (dp-Y, tab B)

CF-B (dp-Z, tab B)

CF-C (dp-Z, tab C)

 

 

The batch macro is simply an output data tool set to change the entire path based on the path and tab being passed by the first input.

 

What is happening is each filepath and tab listed in the first input is being populated with the entire cash flow table, not just the ones specific to that file. So workbook X, tab A has cash flows:

CF-A (dp-X, tab A)

CF-B (dp-X, tab B)

CF-B (dp-Y, tab B)

CF-B (dp-Z, tab B)

CF-C (dp-Z, tab C)

 

On top of that, the macro is writing to the same file&tab several times.

 

I'm hoping this description is enough for someone to help, without me creating a dummy example. Thanks in advance for any help you can offer!

7 REPLIES 7
gc
9 - Comet

It might be helpful if you can upload the batch macro so folks could inspect it, too.

bkoons
7 - Meteor

I've uploaded the workflow (Example2) and batch macro (Example). Sorry, I know they are poorly named. I was not able to upload the .xlsm files, but they are simple and each contain 3 tabs (A,B, and C). 

NicoleJohnson
ACE Emeritus
ACE Emeritus

Hi!

I'm not sure you actually need a macro in this case... I believe you can achieve this result with the regular Output Data tool. See attached workflow:

 

1. Append the PathAndTab name to the original data

2. Filter for those whose FileNames match from both sources

3. Select Tool to remove unwanted columns

4. Output Tool - select one of the files to start with, make sure your Output Options are set to Overwrite Sheet, and then check the box at the bottom to "Take File/Table Name from Field". Use the PathAndTab designation for your field, and select "Change Entire File Path" from the dropdown... voila!

 

Capture.JPG

 

Let us know if that does the trick... or if someone finds a way to do it with a macro (I'm sure there's a way... in fact, your original workflow might have worked if you add in the Append/Filter steps above...)

 

NJ

bkoons
7 - Meteor

I should have mentioned that I'm in version 10.6. I'm not able to open your solution.

 

gc
9 - Comet

Open Notepad. Then File Open the workflow file. Change version to "10.5" on line 2.

 

<AlteryxDocument yxmdVer="10.5">

 

and save.

 

bkoons
7 - Meteor

SOLUTION ...NJ, your reply led me to the answer!! I needed to join the fields NOT append them. And then I removed the batch macro and just used the output tool with the settings you described. Now I'm golden, thanks!!!

NicoleJohnson
ACE Emeritus
ACE Emeritus
You're absolutely right, join is definitely the better choice over append... sometimes I trick myself into more complicated solutions than they need to be :) Glad it worked!!

NJ
Labels
Top Solution Authors