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.