Export to excel retaining formatting and removing existing data

Question

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

50ld13r · Accepted Answer

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.