Batch Macro need to output to multiple Excel files

[Brad1]

This has probably been answered but I'm not seeing my exact scenario.  I have a batch macro.  It's running the same process over and over but each time with a different Excel file as the input.  I want to output each run into a different Excel file located at the same path.  The file name I want to use for the Output Excel file is contained in a column within each of the Input Excel files.  I got it to create a new tab for every run but I want separate Excel files for every run.  In the Macro Output Data tool when I select "Create New Sheet", check "Take File/Table Name From Field", "Change Entire File Path" and "Field Containing File Name or Part of File Name" is set to "Client Name" (the field in each input Excel file that I want the Output Excel file to be named).  These selections give an error of:  "You must specify a sheet name."

Any idea?  Thanks.

[Brad1 · Accepted solution]

Resolved by adding a formula for mypath, myfile, and Full_Path 

[ChrisCrombie]

Hi Brad,

I'm glad you got this working.  I'm trying to create almost the exact same scenario, but I can't seem to get mine to write the output files.  It works when I do a single pass on the macro, but not when I try to pass in multiple filenames.  I get no indication as to why it's not working but I suspect it's trying to Union the results when all I really want is a separate file for each iteration. 

Do you mind sharing your solution so I can see where I'm going wrong?

Edit:  I'm dropping my examples in if anyone has any ideas.

[Brad1]

I'm no expert.   I've got these questions in my Macro;

The answers are:

MyClient

MyClientNumber

MyState

These correspond to Field Names in a Text Input Tool.  These strings get replaced in SQL statements.  These get looped through and spit out the individual Client files.

I think you might need a MyPath and a MyClient to save the files separately

I have this in a formula right before   ""MyPath" + "MyClient" +  DateTimeToday() + "xlsx|Sheet1"" right before the output tool.  Coming into the formula tool is the Control Parameter and Action Tool.

I would try and scrub my workflow and upload it but it's just too massive for me to have time to do that.

Looking at your workflow - I would try the following and see if this helps. 

You probably manually typed in "Eway".  Trying clicking on the expression above so that it populates the express in the Replace String window.  Then delete out everything in front of and behind "Eway".  You wouldn't think that would make a difference but it does for the Apps I have going.  If I type it in - it never replaces the string.  I probably should have led with this. 

[ChrisCrombie]

Thanks for the reply Brad.

Yes, the problem you pointed out was simply me replacing string to try a different table on a single pass.  I forgot I did that and probably shouldn't have included it in the sample.  My main problem is that I'm a knucklehead and had the "Disable All tools that Write Output" checked on my main workflow.  I'm fairly novice when it comes to macros and I find them hard to troubleshoot since you can't see each step of the macro like you can with a standard workflow.  

Everything works fine after unchecking that box and I'm pretty thrilled with the results now.

Cheers!

Chris

[Brad1]

Glad to hear it!

Brad Shannon
Financial Analyst - Geo Analytics
Express Scripts 

[ckirgiz]

Hello,

Is it possible to share your batch macro? I have the almost exact same task. 

Thank you,

John

[ckirgiz]

I just realized that you said "I would try and scrub my workflow and upload it but it's just too massive for me to have time to do that." So I got my answer :)

Thanks,