Output one data into multiple sheets/ Update multiples files

[Hakimipous]

Hello,

What would you say is the best way to update a particular sheet from different output (all having the same structure)

For example, I would like to output the word "RUN" into the first cell of every sheets called "execute" from all my excel files

Thanks for your insight !

[jrgo]

@Hakimipous,

If you're targeting sheets in only one workbook, you can create a batch macro to iterate through each sheet that you feed into the control parameter.

This is also assuming that you've already created a workflow that is able to do this to one sheet that you've pointed to.

Your main workflow would have a normal Input tool. In the Table/Sheet selection windows, you'll select the option to list sheet names. You'll also want to set the option to include the full file path of your file.

Use a filter to find any sheets where you find "execute" and then construct a new field that would concatenate the full path and sheet name. This field will be what you feed into the macro's control parameter.

Convert your workflow that does this operation to a batch macro and have your control parameter update the file path of your input tool.

Hope this helps spark some ideas!

Best,

Jimmy

[Hakimipous]

Wow thanks again for replying so quickly @jrgo !

just one question :

When you say " construct a new field that would concatenate the full path and sheet name"

Should it looks like this :  C:\Users\XX\Output_One_Sheet\doc_1.xlsx|||<List of Sheet Names>execute  ?

And should the name of this field be the Word "RUN" that I would like to output to all my sheets? 

[jrgo]

@Hakimipous,

correction on on my part... instead of just concatenating, use the replace function to swap “<List of Sheet Names>” with your sheet name field.

Yes, set it as your header in a blank table... Text Input tool would probably be easiest.

[Hakimipous]

@jrgo

What would it look like with a text input tool? 

Also forgot to mention something important. Would that be possible with the Append option? Overwriting the sheet woudn't be an option as it contains some macro formula

[jrgo · Accepted solution]

@Hakimipous,

Like this

The string "RUN" as the header with no records under it. If you see a 1 on the left had side (represents the row number), that means that you've activated the row.

I was experimenting with this a bit and this may not work 100% depending on what else is in the sheet it's suppose to add "RUN" to in cell A1. Reason for this is because when you append it with your other table that contains the full file path, it will create one record and you need it to dynamically update the file path of the Output tool. As such, Alteryx will throw an error if something already exists in A2.

In order for this to work universally, the process that outputs needs to be put in it's own batch macro where you can feed the full path that contains the sheet and range reference that will update the file path of the output tool. I've attached an example that should work for you.

The package includes the workflow, a batch macro and a dummy excel file for testing. The workflow reads in the sheets names as, filters to sheets that contain the work "export" then formulate a new output field name that contains the sheet and also the range to output to. Specifying the range is important so that it does not replace anything else on that sheet, should there be anything.

In the macro, you'll notice that it's pretty simple. the Text input box with the header only going straight to the output tool. The Control Parameter also connected to the output tool will will iterate through each row fed in from the main workflow.

[jrgo]

Append option in your output tool wouldn't be the option to use because it would simply add your value after your last active cell.

In the macro i shared, you'll notice that the output option i specified is to overwrite the sheet. However, since the ACTUAL output path will contain a cell range reference of A1:A1, it will only replace that single cell if anything exists in it.

[Hakimipous]

Awesome ! it works perfectly

I added a Directory input tool + a formula tool containing [FullPath] + "|||execute"  to make it work and all my files and it does x) 

Thanks again you made my day x)