Update 3 out of 10 Excel tabs in a spreadsheet

Question

Hello! Not sure where to put this. I'm sure others must have tried this and figured it out before.

We have a spreadsheet with 10 tabs. 3 of those tabs we'll have automated via an FME workspace.

Question 1 - Is it better (or easier) to purely overwrite or append? We have an excel template set up as the formatting (font size/ auto height) need to be followed. The scenario here is we are keeping track of communications and creating a new row each time there's a new communication. Is anyone able to share a screenshot of what the parameters should look like? I am getting confused by truncate/insert/update, templates and named range and how they all work in this situation. Is it possible to only overwrite the three tabs or if I select overwrite does that imply the whole spreadsheet. I notice there are two sections to control parameters

I'm a little bit unsure of setting up a named range since the list of communications will continuously grow.

There are still those other 7 tabs in the spreadsheet that other members in the organisation will be manually updating. We need to make sure FME doesn't touch or overwrite these tabs! Therefore we are running the FME process at night to avoid any lock file issues. This is my biggest concern -

Question 2 - how do I update or overwrite the 3 FME tabs without overwriting the other 7 tabs that others are manually updating while also ensuring the 3 FME tabs are following the rules of the excel template (eg Calibri 9 font and cells autoheight formatted). We have resorted to an excel template because we were not able to get the parameters in the Excel Writer to use autoheight.

Normally I would attach the template and tested output, but it contains private information. I think you should get the gist of what we're trying to achieve though and can easily set up a dummy template.

Page 1 / 1

Hello @epro_admin

Based on the description, it sounds like you already have existing data in the destination Excel file you are writing to. So you want to keep any existing rows and append any new data to the file. In this case, inserting would the correct action to set.

The Sheet Name parameter controls which 'tab'/Excel worksheet the writer will insert new rows. You will need to specify the name of the sheet (plus named range if you are using one, ie. sheetname/rangename) you want to update here. As for formatting, another option to using an Excel template is to use an ExcelStyler to apply Excel styling on new data written to the destination file.

I did a quick test and it looks like when FME writes to a named range, the named range is automatically expanded to include new data appended to the end of the named range.

I hope this information helps.

Reply

debbiatsafe · Answer

Hello @epro_admin​Based on the description, it sounds like you already have existing data in the destination Excel file you are writing to. So you want to keep any existing rows and append any new data to the file. In this case, inserting would the correct action to set.The Sheet Name parameter controls which 'tab'/Excel worksheet the writer will insert new rows. You will need to specify the name of the sheet (plus named range if you are using one, ie. sheetname/rangename) you want to update here. As for formatting, another option to using an Excel template is to use an ExcelStyler to apply Excel styling on new data written to the destination file.I did a quick test and it looks like when FME writes to a named range, the named range is automatically expanded to include new data appended to the end of the named range.I hope this information helps.

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded