Importing and Replacing Specific Sheet in an Excel Workbook

[jyeung753]

Hello, 

I am trying to create a workflow that imports an entire excel workbook, and updates a certain tab (sheet) with a new file sent by a client. The workbook holds various sheets with different schema. So formatting is not consistent regarding the entire workbook. Think of an accounting workbook with different sheets. However, the specific tab that needs to be essentially replaced with the new client data, is the same schema.

The following results need to be achieved:

1. Importing a full excel workbook (All sheets have different schema)

2. Replace one sheet with another from a client (They are the same schema)

3. Output the full excel workbook with a specific sheet updated, now holding new monthly client data

I have attached a mock workflow of the inputs and outputs incase my description did not make sense. 

Any assistance is extremely appreciated. Thank you!

[DavidSkaife]

Hi @jyeung753 

It will be easier to import the Client Data, and overwrite the specific tab on the Company Workbook, with the Output tool option 3 set to Overwrite Sheet or Range:

Unless you're needing to do anything else with the other data in the Company Workbook?

[jyeung753]

Hi David, 

Thank you for that suggestion.

Would you be able to explain the functions/tools used in between the input and output to overwrite this sheet of data into the workbook? As I understand it - the company workbook needs to be imported with the Table or Query being selected as <List of Sheet Names>. From there I am clueless as to how I can take a sheet from that list and replace this with the client data that needs to be overwritten. Not sure if any methods are preferred for this typical operation (Attached is just an idea via join tool).

No other work in necessary for the data in the company workbook.

Appreciate the help with this

[DavidSkaife · Accepted solution]

Hi @jyeung753 

The below should work, making note of the highlighted sections (Sheet1 needs to be renamed as whatever the tab is called in the Company Workbook). No other tools or changes should be required :) :

This will overwrite the existing tab with the data from the Client.

Note: I'm assuming the client data has the headers on it as well? If not and you want just the data to overwrite that's something slightly different - i'll mock something up now

EDIT: if you want it to preserve the headings, and just overwrite the data itself then the Output tool can be set up like so, taking note of the defined range and skipping field names option:

I've specified row range of 2 to 5000 as an example, this can be larger or smaller as required to fit your needs, but starting at row 2 means it ignores the headers in the existing file, and skipping field names means the names contained in the Client Data file are also ignored