Macro for multiple sheets into excel in amp mode

[tmonroe]

We typically do a lot of audits writing them to different tabs for a process.  We have always used the block until done and not had any issues.  Now that we are using amp mode that does not work anymore.  I am trying to create a batch macro where I add a column "sheetname' to each output stream and union it into a macro.  The problem is each sheet is way different from the other.  My first attempt, it worked and created multiple sheets by sheetname, but all the fields from all the work flows show up (I assume the union tool created this).  Has anyone created a macro that will take multiple (how many could be 3-10), and use the batch macro to feed one at a time so amp mode is not trying to write to multiple sheets at once?  My other thought is multiple inputs, but not sure how to go about having 10 inputs, and only using 3.  That seems like it would avoid the union issue in my first attempt.

[Mathias_Nielsen]

Hi @tmonroe,

The "Control containers" will fix this issue for you. These work only with AMP engine enabled, so use these instead :)

I have used it often when writing to different sheets in the same file.

Hope this helps

[tmonroe]

I have not used them much, but will look into that. Thanks

[Mathias_Nielsen]

Great, let me know how you get on :)

[tmonroe]

I researched the container and it will work if i know which fields I need for each tab.  However, I am looking to dynamically load records into the macro with sheetname for each one and it dynamically change based on that sheets field names.  Not sure if this is possible or not.

[Mathias_Nielsen · Accepted solution]

Hi again @tmonroe,

Have you tried using the "File name from field" If you are only writing to the same file you can use this setup where you append the sheetname to the file. You might have to add "|||" in front of your sheet name for it to work. Alternatively you can change the entire file path if you are writing to multiple files. Then you would of course need the entire file name including sheet name.

I hope this helps you :)

[Mathias_Nielsen]

I guess you would actually have to use "Append to existing sheet" as Output option and not overwrite