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]

hi,

How about this....

dim pc as excel.pivotcache

for each pc in woFile.pivotcaches
  pc.refresh
next

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

 

[Mighty]

Hi Skip

Thanks for the suggestion. Unfortunately I can't just do a straight refresh as the number of rows in the source data changes so I need to specify the new data source

Mighty

 

[SkipVought]

but what METHOD does the data source change?

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

 

[SkipVought]

this might work...

'
    Dim woFile As Excel.Workbook, ws As Excel.Worksheet, pt As Excel.PivotTable
    
    For Each ws In woFile.Worksheets
        For Each pt In ws.PivotTables
            pt.ChangePivotCache woFile.PivotCaches.Create( _
                SourceType:=xlDatabase, _
                SourceData:=woFile.Worksheets("Work Order Detail").Cells(3, 1).CurrentRegion, _
                Version:=xlPivotTableVersion12) '[highlight]or whatever version YOU are using[/highlight]
        Next
    Next

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

 

[Mighty]

Hi Ship,

That is obvously not VBScript syntax so I tried changing it and could not get it to work

Nick

Mighty

 

[SkipVought]

Your variables are declared as Object.

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

 

[combo]

Probably the problem is that you don't specify destination argument. It is optional, if you don't specify it, excel assumes active cell. Again, if it is outside pivot table, excel creates new table. See excel vba help file for details.

combo

 

[Mighty]

Hi Combo,

So what would be the destination - is it the first cell of the pivot table?

Mighty

 

[SkipVought]

No dsitination.

This is the PivotCache.

PivotCaches.Create Method
Creates a new PivotCache.
Version Information
Version Added: Excel 2007

Syntax

expression.Create(SourceType, SourceData, Version)

I'd rather guesws that the Excel CONSTANTS need VALUES instead...
[pre]
xlDatabase 1

xlPivotTableVersion2000 0 Excel Version 2000
xlPivotTableVersion10 1 Excel Version 10
xlPivotTableVersion11 2 Excel Version 11
xlPivotTableVersion12 3 Excel Version 12
xlPivotTableVersionCurrent -1 Provided only for backward compatibility


[/pre]


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

 

[Mighty]

Hi Skip,

Updated my code to the following:

For Each ws In orderFile.Worksheets
    For Each pt In ws.PivotTables
        pt.ChangePivotCache orderFile.PivotCaches.Create(0, orderFile.Worksheets("Work Order Detail").Cells(3, 1).CurrentRegion, 2)
    Next
Next

Get the following error message: Invalid procedure call or argument

Mighty

 

[SkipVought]

0 is not a valid value for the [highlight]SourceType[/highlight]...

'
    For Each ws In orderFile.Worksheets
        For Each pt In ws.PivotTables
            pt.ChangePivotCache orderFile.PivotCaches.Create( _
                [highlight]1[/highlight], _
                orderFile.Worksheets("Work Order Detail").Cells(3, 1).CurrentRegion, _
                2)
        Next
    Next

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

 

[Mighty]

Hi skip,

Spotted that and changed it and now have a different error:

The PivotTable field name is not valid. To create a PivotTable, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

My data starts on row 3 (headers are on row 3) and there are no empty cells in row 3. There are however some fields filled in on row 2

Mighty

 

[Mighty]

Hi Skip,

I changed the code to the following and it appears to be working:

For Each ws In orderFile.Worksheets
    For Each pt In ws.PivotTables
        pt.ChangePivotCache orderFile.PivotCaches.Create(1, "'Work Order Detail'!R3C1:R[b][COLOR=#EF2929]500[/color][/b]C24", 2)
    Next
Next

The red section above would then be set in my code as it will vary

Mighty

 

[SkipVought]

My data starts on row 3 (headers are on row 3) and there are no empty cells in row 3. There are however some fields filled in on row 2

This is what happens when you share scant information regarding your application, and your source table in particular.

A PROPER table in my world, is ISOLATED from all other data! faq68-5184 Your table would NOT be a proper table in my world, because your have data contiguous with your table in row 2. My solution uses the CurrentRegion method that ASSUMES an ISOLATED CONTIGUOUS table, containing unique headings in the first row of the table. IMNSHO,it is a poor way to structure data in an Excel workbook!

This is extremely important information to share with the people attempting to assist you in arriving to a satisfactory solution.

Please describe how this source data table is updated. Manually, query?????



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

 

[Mighty]

Apologies Skip. This is an Excel template file that I was given and asked to populate automatically.
I run a query which adds data to this table and then attempts to reset the bounds of the pivot table data based on the new rows that have been added

Mighty

 

[SkipVought]

I run a [highlight]query[/highlight] which adds data to this table

Then there is an OBJECT on the sheet, depending on your Excel version, and a corresponding Named Range. That Named Range is the reference that defines the range of your source table!

What Excel version?

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

 

[Mighty]

The query is not in the excel file.
I run the query in the VBScript program and loop through the query in the VBScript program and write it to the excel file.

In relation to the Excel file, I have hit another glitch. The code above works fine when I run it on my machine (Excel 2007) but not when I run on the server where it is scheduled to run (Excel 2003)

Mighty

 

[SkipVought]

If you have control over this workbook/sheet, ISOLATE YOUR TABLE! Then you have no problem using the code I provided, using the CurrentRegion method to define the source table range.

Rregarding the last issue, you must determine the Excel VERSION and assign the ChangePivotCache version accordingly.

Application.Version returns the Excel version of the Application object.

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

 

[Mighty]

When I run on server running Excel 2003, it tells me that it doesn't support the Pivotcaches.Create method

Mighty