Free Support Forum -

Refresh PivotTable in existing Excel file


I have a large number of Excel files being "reloaded" with data
programmatically using SQL Server 2000 DTS Packages which uses a server-side
installation of Excel to open the files with VBA and reload the data to
update pivot tables in the files.

Set appXL = CreateObject("Excel.Application")
appXL.Visible = False

appXL.Workbooks.Open(LocalFilePath & ReportName(0))
appXL.ActiveSheet.Range("C4") = "Last updated: " &

appXL.DisplayAlerts = false
appXL.ActiveWindow.Close (SaveChanges)
appXL.DisplayAlerts = true

I want to use Aspose.Cells on the server instead of Excel, but can I in some
way do this without having to create new Excel files from scratch, I just
want to use
Aspose.Cells instead of the Excel application.

Is this possible?


Jonas Hilmersson


Thanks for considering Aspose.

Well, we will support to manipulate PivotTables in the template files at runtime soon.

Related VBA/Macros, we don't support it.

Thank you.

Hi ,

Now we do not support to manipulate PivotTables in the template files. We only keep them and perserve them to the file.

You can load data to the worksheet,then create a pivot table with the data source range.

Please check how to create a simple pivot table

So I can't update the PivotCache on an existing Excel file without recreating the Excel file to use data from a worksheet region?



Hi Jonas,

I think this feature is not supported.

Hi Jonas,

You can force Excel refresh the PivotCache when Opening the file with MS Excel. Please check "Refresh on open" option of the "PivotTable Options".Please check the wiki

Hi, and thanks for your answer.

“Refresh on open” is unfortunately not an option for me as the file has to be loaded with data from the beginning, it will take to long to refresh the data every time a user opens the file.


We are reviewing Aspose.Cells and would like to know if this function has been added. The templates will be opened via Aspose server side and will need to refresh the pivottable data from and MSAS OLAP cube. They will not be opened in Excel by the end user, rather posted to a sharepoint site as a PDF.

Thanks for your help,


Hi Paul,

Well, I am afraid there is no such feature available at the
moment. The component does not support to manipulate the Pivot Tables
in the template files. Aspose.Cells creates/formulates pivot data only
and it forces MS Excel to create a pivot table when opening the file
into MS Excel. It actually implements the refresh on open
option/setting for MS Excel, so, when you open the generated file(by
Aspose.Cells) into MS Excel, the pivot table is then created.

to update you further, we are currently working on reading pivot tables
in template files, hopefully the feature would be completed in Q1 2010.

Thanks for your understanding!