I'm 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. I have a request to have them auto updated so users don't have to change shipdate and do a refresh. Can this be done with a Powershell script? I posted in PS forum and they suggested I try here..

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()
 This is the error it throws...

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

• Moved by Simon_HouMicrosoft contingent staff Thursday, September 10, 2015 8:18 AM appropriate

 THis was moved back from Analysis server section... can PS be used in this script.


 Thanks ...

You need to do this in VBA and resolve issues against your server and query.  The request clearly does not match the table component fields correctly.  Run the same by creating a macro with the macro recorder as you perform the same table rotation manually in Excel.  This will give you the syntax for the command which can then be translated too PowerShell.

Without your setup and file we cannot determine which  component is specified incorrectly.

ActiveSheet.PivotTables("PivotTable1").PivotFields("[DATE].[Ship Date]"). _
        CurrentPageName = "[DATE].[Ship Date].&[2015-09-10]"
    Range("B15").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

 This is what the recorded macro showed when I do the process manually.

 THanks.

Now convert it to PowerShell.

$xl=New-Object -ComObject Excel.Application
$xl.Workbooks.Open('mybook.xlsx')
$xl.ActiveSheet.PivotTables("PivotTable1").PivotFields("[DATE].[Ship Date]").CurrentPageName="[DATE].[Ship Date].&[2015-09-10]"
$xl.Range("B15").Select
$xl.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

 I thought that is what I tried with;

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

Not exactly the same thing.

You are using an index which won't work in PowerShell. Use the table name.

$sDate needs to look like this: "[DATE].[Ship Date].&[2015-09-10]"

"[DATE].[Ship Date].&[$sdate]"

This worked and updated spreadsheet. Is there away to get that put to variable since I know which field
and have the date already?

 Thanks for all your responses....

I don't understand the question.  What needs to be put into a variable?  "$sdate" is already a variable.

 Above question resolved.. sorry for the confusion..  One last thing is it possible on the ship date..

 to grab the most current value from cube before doing the refresh.

 SO I query cube for "[DATE].[Ship Date].&[2015-09-10]" and find for current month the newest ship date then plug that into

$sdate

In most cases get previous day works but I did test for a date not in cube and as expected it did throw. Is there any easy way to handle that by finding newest value first.

 Thanks for all your help...

• Marked as answer by hart60 13 hours 51 minutes ago

That is a new question.  How would you do it in VBA?  Start by posting int eh Excel developers forum then translate to PowerShell or use s4earch engine to find examples in any language.

   Thanks again for all your help !!!!!!I'll do some search's and see what is out there.