Refresh PivotTable in existing Excel file

Aspose.Cells Product Family
1

Hi,

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.Sheets(2).Select
appXL.ActiveSheet.Pivottables("Pivottable1").RefreshTable
appXL.ActiveSheet.Range("C4") = "Last updated: " &
appXL.Sheets(2).Pivottables(1).PivotCache.RefreshDate
appXL.ActiveSheet.Range("A1").Select

appXL.Sheets(1).Select
appXL.ActiveWorkbook.Save
appXL.DisplayAlerts = false
appXL.ActiveWindow.Close (SaveChanges)
appXL.DisplayAlerts = true
appXL.Quit

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?

Brgds

Jonas Hilmersson

2

Hi,

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.

3

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 Creating a Simple PivotTable.

4

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

Brgds

Jonas

5

Hi Jonas,

I think this feature is not supported.

6

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
Creating a Simple PivotTable.

7

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.

/Jonas

8

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,

Paul

9

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.

Just
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!