LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: djnrempel

The situation:
- I publish data to an Excel file
- I open that file and add new sheets to it where I create pivot tables, reports, etc.
- Then I want to publish updated data.

If I use the Publish to Excel node, it basically replaces the entire file. I tried using Append to Excel on a file that had a pivot table, and the resulting file did contain a sheet with the data that had been on the pivot table sheet, but it is no longer a pivot table, it is just as if I had typed values into the cells.

Is there a way to have Lavastorm only modify the data sheet and leave other sheets completely alone?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Make a copy of the Excel sheet, with all the charts, pivot tables, etc, configured the way you want them.
Save that on the server, and use the "Template" option on the second tab of the regular Excel Output node.

The node will replace the data on the sheet(s) named on the input pins, and will leave the rest of the sheets alone.
The user will have to hit Refresh on the pivot tables, but it won't remove out those other sheets.

==================
Note: often for your charts and pivot tables, you will need to use full column ranges such as $A:$D instead of normal ranges like A1:D:999 - if your data ends up going past row 999, then your charts would be missing data.

Also, if you use the old XLS format instead of the new macro-disabled XLSX format, you could include an AutoExec macro in your workbook that would do the refreshes for you. Unfortunately, at this time, LAE does not support the newer macro-enabled XLSM format.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

I think you should also be able to get what you want by using the Append Excel node with "ExistingDataPreservation" set to "Preserve All" and "NoInputForSheetAction" set to "Leave Sheet" (which is the default).

Regards,
Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: djnrempel

Okay thanks guys. I will try Tim's method. I don't like the template option because it still means managing multiple files.

I just realized another option is to simply have my pivot tables in another excel file that links to the excel file outputted by Lavastorm. Hmm.