Export to excel retaining formatting and removing existing data

[50ld13r]

hi,

I have created an app the user specifies a drivetime and the process populates a pre-defined template.  As I want to retain the formatting I specify the sheet and range and append the row count to form the range

\Template.xlsm|||Summary$A1:K" + tostring([Count]+1)

The issue arises because the data can vary in number of rows.  If the previous iteration had more rows that the current run, those excess rows are maintained.  I thought I could be smart and just specify a large range (Shown below) but the export will only output the size of the data array.

\Template.xlsm|||Summary$A1:K10000"

The only solution I can think of is to append thousands of blank dummy rows to the existing data.  I was wondering if there is a more elegant solution or I am just missing something obvious.  I want to avoid having one sheet with the data and a lookup sheet on the formatting one as the data is large and my excel report is already 30mb.

Thanks,

Ashley  

[T_Willins]

HI @50ld13r,

You are not missing something obvious.  Alteryx will only output actual data even if the range is larger than the data and won't override the existing data outside of the output data.  Overwrite sheet is not an option as you will lose your formatting and you have already indicated a lookup sheet is not a viable option either.  If the only sheet(s) in the workbook are from the Alteryx workflow, you can use Table tool to format the data and the Render tool to output the data to the file.  However, this is the equivalent to Overwrite File and likely is not a good solution for you either given your file size.  The only other suggestion I have is Python or VBA script can also clear contents in an Excel worksheet and can be run from your Alteryx workflow, but his would add another level of complexity to your workflow.   

[50ld13r · Accepted solution]

Thanks for the info.  To avoid the use of coding I have used generate rows and appended a series on nulls to the dataset.  This appears to work although not the most elegant.