Pivot Refresh Error when calling Excel Macro from VB.NET program

A

amdefacto

I am getting error
"Method 'PivotTables' of object '_Worksheet' failed"
at line
" Sheet1.PivotTables("PivotTable1").PivotCache.Refresh"
Snapshot of Macro code is given below.
Using .NET program i paste data on the base data excel sheet.
Using the below code (PivotTableWizard source data) i add the new range
(StrRange) to the pivot and refresh.

Above process works when run manually...using excel but errors out when
called from the .NET program.
Pls help.

----------------------------------------------------
'Update Pivot Range
Sheet1.Activate
Sheet1.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=Sheet2.Range(StrRange)
ActiveWorkbook.ShowPivotTableFieldList = False
'Refresh Pivot Table
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
'PAUSE Process
wait 30 'seconds
'Update Pivot Table Cache
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
'Change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
'Refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error GoTo ErrLogger
pc.Refresh
Next pc
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top