CODEWITHSUNDEEP
vbaexcelpowershell
Souhail Ouabi
Souhail Ouabi

Reputation: 125

Refreshing a Pivot table with VBA

I am trying to refresh automatically a pivot table with Powershell script that calls a vba code in an excel. my powershell code is th following.

$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("$para_temp\RapportPalettes.xlsm")
$worksheet = $workbook.worksheets.item("source")
$excel.Run('Import')
$worksheet = $workbook.worksheets.item("TCD")
$excel.Run('MAJ')

the first macro "Import" works just fine. But the second "MAJ" that refreshes the Pivot table with the new data in source doesn't

I tried first this macro in maj : 

Sub maj()

Dim pt As PivotTable



    Set pt = ActiveSheet.PivotTables("TCD")

    pt.RefreshTable

End Sub

I don't get any error but my pivote table isn't refreshed and i have to do it manually.

Then I tried this one to change the pivot table's source of data :

Sub MAJ()

Dim sht As Worksheet
Dim SrcData As String
Dim pvtCache As PivotCache

'Determine the data range you want to pivot
  Set sht = ThisWorkbook.Worksheets("Source")
  SrcData = sht.Name & "!" & Range("A1:Z10000").Address(ReferenceStyle:=xlR1C1)

'Create New Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

'Change which Pivot Cache the Pivot Table is referring to
  Worksheets("TCD").Activate
  ActiveSheet.PivotTables("TCD").ChangePivotCach (pvtCache)

End Sub

but i have an 438 error in the VBA : object doesn't support this property or method for this line 

ActiveSheet.PivotTables("TCD").ChangePivotCach (pvtCache)

could you please Help ?

EDIT :

as you can see, I do have a worksheet and a pivot table named TCD. Worsheet

Pivot table

EDIT : In fact the macro is working, i tested it by creating a button that calls it. It doesn't work when i call it with powershell

Upvotes: 0

Views: 5783

Answers (2)

Souhail Ouabi
Souhail Ouabi

Reputation: 125

I finally found the problem which was a bit stupid. In fact I was refreshing the Pivot Table but I wasn't saving the changes.

This is the macro I finally used and I used the same powershell script:

Sub Test()
Application.DisplayAlerts = False
Dim TCD As PivotTable
     For Each TCD In Worksheets("TCD").PivotTables
          TCD.RefreshTable
     Next
     ThisWorkbook.Save

     Application.DisplayAlerts = False
End Sub

Thanks for all

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

Try the code below to refresh the PivotTable named "TCD" in worksheet "TCD" with the updated PivotCache.

Code

Option Explicit

Sub MAJ()

Dim sht As Worksheet
Dim SrcData As String
Dim pvtCache As PivotCache
Dim pvtTbl As PivotTable

'Determine the data range you want to pivot
Set sht = ThisWorkbook.Worksheets("Source")
SrcData = sht.Range("A1:Z10000").Address(False, False, xlA1, xlExternal)

'Create New Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)

'Set the pivot table object
Set pvtTbl = ThisWorkbook.Worksheets("TCD").PivotTables("TCD")
With pvtTbl
    .ChangePivotCache pvtCache 'Change which Pivot Cache the Pivot Table is referring to
    .RefreshTable ' refresh the Pivot Table
End With

End Sub

Upvotes: 2

Related Questions