Updating Pivot tables in Excel 1

[Mighty]

Hi All,

i have to admit that it is a long time since I have been on Tek-tips - used to be a regular user but just getting back into development now after an absence and I am having a problem. I have severl VBScript programs that I am using to update Excel files regularly. Some of these Excel templates have pivot tables. I am updating the "detail" tab in the Excel template and then using the code below (for example) to reset the data source for the pivot table that already exists in the Excel template file:

' Reset the Data Source for the Summary Pivot Table
woFile.WorkSheets("Summary").PivotTables("SummaryTable").PivotSelect "", 0, True
woFile.WorkSheets("Summary").PivotTableWizard 1, "'Work Order Detail'!R3C1:R" & (xlRow-1) & "C24"

This works fine when there is only one pivot table in the file. However, i am getting requests now to create multiple pivot tables in files and the code above to reset data source does not appear to work if there are multiple pivots. For example, I have added a second pivot table to an Excel file and updated the code above to the following:

' Reset the Data Source for the Summary Pivot Table
woFile.WorkSheets("Summary").PivotTables("SummaryTable").PivotSelect "", 0, True
woFile.WorkSheets("Summary").PivotTableWizard 1, "'Work Order Detail'!R3C1:R" & (xlRow-1) & "C24"

' Reset the Data Source for the Material Variance Pivot Table
woFile.WorkSheets("Material Variance Analysis").PivotTables("MaterialVariance").PivotSelect "", 0, True
woFile.WorkSheets("Material Variance Analysis").PivotTableWizard 1, "'Work Order Detail'!R3C1:R" & (xlRow-1) & "C24"

What happens now is that a new empty pivot table gets created in both worksheets and the data source in my two pivot tables are set to only the header row of my detail sheet. Does anyone know why this code would work fine for one pivot but not multiple?

Mighty

 

[SkipVought]

If you were to programatically NAME the source data range as "Database" each time you CHANGE a value on the source table sheet, AND use that name in each PivotTable Source, then you won't need to run this procedure to redefine the source range.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Mighty]

Skip,

Can you advise how to update a named range in VBScript. I had tried to do this but couldn't get it to work

Nick

Mighty

 

[SkipVought]

Go into Excel, select your table range (and I am assuming that your source table is now isolated and contiguous) and perform Formulas > Defined Names > Define Name -- enter [highlight]Database[/highlight] in the Name box.

Now Macro record that process, copy the code and use that copy as the starting point for your naming code.

Post back with your recorded code if you need help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Mighty]

Hi Skip,

A combination of names ranges and your first post in relation to refreshing the pivot caches has appeared to work.
Thanks for all the advice.

Nick

Mighty

 

[SkipVought]

Great, thx!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]