Hey all, I've scoured the forum and can't locate a solution.

I've created an Excel template that contains a pivot table. When creating the template I ran the query and dropped the data into an Excel file (145k rows). I then created the pivot table and saved it as a template. Before loading the template to the server I highlighted the data rows and deleted the data leaving only the headers on one worksheet and empty pivot table on the other. I then uploaded the template.

The template works fine if I have 145k rows; however, if I deviate from that number then I have blanks present when the number falls below 145k OR I don't capture the data in the pivot table for the rows that exceed the 145k.

Is there a way for my pivot table template to adjust to the number of rows created when I run my fex?

Thanks for any info you can offer.

This message has been edited. Last edited by: <Kathryn Henning>,

Pondod, you ask "Is there a way"

Sure there is, you need a macro enabled template. You'll use the macro to build your pivot table. In the macro, you can get the number of rows and column in your data.

jgelona,

I know we saved it as an .xltm file before loading it, however, since I didn't create the pivot table I'm not sure if it's macro induced or not. I know we set the option to Refresh when opened. I'll check it out. Thanks for the direction.

Tony

Resolved:

1. Make sure your worksheet is exactly how you want it in the template BEFORE saving it as a template
2. In your pivot table options remember to set the PivotTable Options/Data/"Refresh data when opening the file" checkbox.
3. If your range is dynamic remember to use the command:

  =OFFSET(Sheet!$A$1,0,0(COUNTA(Sheet!$A:$A)),column#)  

4. In your fex include the following lines at the beginning:

SET EXCELSERVURL=''
-RUN