auto pivot table update

 

  Im trying to automate a Pivot Table update. I put outputs in the code so you can see what I'm trying to update and with
what values.

To

 $date = 2015-09-07

From

 $dispshp = [DATE].[Ship Date].&[2015-09-01]

Update:

$workSheet.PivotTables(1).PivotFields("[DATE].[Ship Date]").CurrentPageName=$sdate

#Set the file path (can be a network location)
$filePath = "C:\shipment.xls"

#Create the Excel Object
$excelObj = New-Object -ComObject Excel.Application

#Make Excel visible. Set to $false if you want this done in the background
$excelObj.Visible = $true

#Open the workbook
$workBook = $excelObj.Workbooks.Open($filePath)

#Focus on the top row of the "Data" worksheet
#Note: This is only for visibility, it does not affect the data refresh
$workSheet = $workBook.Sheets.Item("Customer")

$sdate= "{0:yyyy-MM-dd}" -f (Get-Date) # '2012-08-30

$sdate

$workSheet.Select()

$dispshp = $worksheet.PivotTables(1).PivotFields("[DATE].[Ship Date]").CurrentPageName 

  #$workSheet.PivotTables(1).PivotFields("[DATE].[Ship Date]").CurrentPageName=$sdate

$dispshp

#Refresh all data in this workbook
$workBook.RefreshALL()

#Save any changes done by the refresh
$workBook.Save()

#Uncomment this line if you want Excel to close on its own
$excelObj.Quit()
 It gives an XML PARSER ERROR

 Thanks.
September 7th, 2015 9:08am

You have to post the exact error.
Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 9:14am

 Xcel pops up and display's message:

XML for Analysis Parser

   The restriction value provided by the consumer does not match other restrictions or refers to an unkown object.

 The PS error:
Exception setting "CurrentPageName": "Exception from HRESULT: 0x800A03EC"
At C:\powershell\pivot_table_refresh.ps1:25 char:3
+   $workSheet.PivotTables(1).PivotFields("[DATE].[Ship Date]").CurrentPageName =  ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterSetValueTI

September 8th, 2015 8:32am

Without your spreadsheet it will not be possible to find the cause.

Read  the following as it points to an addressing error: https://www.google.com/?gws_rd=ssl#newwindow=1&q=excel+0x800A03Ec

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 2:39pm

 The spreadsheet has data connection to the Analysis server so it wouldn't be able to update if you tried the refresh.

September 8th, 2015 6:14pm

Yes - if you had searched I think you would find that "auto-analysis server" delivered pivot tables have that error when you try to programmatically upate them.

Try using the analysis server SDK calls to create a custom pivot table.

Post in Excel or Analysis server forums for help with this.

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 6:53pm

 Thanks will try there...

September 8th, 2015 8:36pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics