Output to Excel using batch macro

[bkoons]

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!

[gc]

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

[bkoons]

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]

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!

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]

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

[gc]

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

<AlteryxDocument yxmdVer="10.5">

and save.

[bkoons · Accepted solution]

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]

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