Changing excel file with multiple tabs

[JeeshaV]

I have an Excel workbook with multiple tabs, and I need to add columns, join, and do some calculations on one of the tabs.

As the output file, the tab with changes should be added to the existing file as an extra tab. How can I do so?

[AndrewDMerrill]

Do you have any sample data? Input/Expected Output? Workflow you've started working on and can share? More information will be invaluable to provide you with better help. As it stands, I can tell you that you'll need to start with an Input Tool, and will finish with an Output Tool, and may need to use Control Containers/Block Until Done Tool (depending on your version).

Here is a post that describes how to do the input/output to the same file: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Write-Output-into-Input-file/t...

[alexnajm · Accepted solution]

From the information described, yes it’s possible. In your Output Data tool you would configure it to write to the new sheet name, but ensuring you change Option 3 from Create New Sheet to Overwrite Sheet. That way, the other tabs remain intact!

[JeeshaV]

Hi,
My apologies for not providing the complete information.
So, I have 10 Excel workbooks with ten tabs each. I have to make some changes in the data of one of the 10 tabs in all the workbooks. These are similar changes, i.e., adding some columns and basic joins.
I merged the tabs with data requiring changes from all files through a Python script, imported it into the input tool and performed the required steps. When I try to export this output to the current workbooks, the output tool deletes the other tabs.

Can I save this new tab into the existing respective workbooks?

[rzdodson]

@JeeshaV you absolutely can do that. What I would recommend in your use case is retrieving the files you are looking to overwrite through a Directory tool. It'll allow you to get the entire file name, make the required changes you are needing to do, then ovewriting the workbooks so as to include the new tab. Doing this process will likely require you to transfer your workflow to a batch macro so you can handle one overwrite at a time until we have successfully completed the changes across all of the files.

Will update this response soon when I have built a mock-up solution for you.

[JeeshaV]

Hi,

I tried doing what you suggested below. Here are the screenshots. 

Upon running the workflow, an error comes up which is 'The field ____ is missing. Compare the tool configuration with the input stream'. However, I didn't encounter this error while running the macro separately. 

[JeeshaV]

Thank you!! 🙂