Forum Discussion

null null's avatar
null null
Iron Contributor
Aug 21, 2018

powershell save not working for powerpivot slicers in excel

I am trying to refresh an excel file which has power pivot in it. I am trying to automate it with  a PowerShell script.

 

I find a distinct behaviour between opening file with and without the save explicitly and saving it in PowerShell. I find that the slices with values are not saved in case of PowerShell script but when i explicitly click save, the value of the slices are restored.

 

 

$i = "D:\baseRI\Full ASC Pivot - Apr14 . Apr18.xlsx"

$sleepTime = "1"

try
{
$xl = New-Object -ComObject Excel.Application;
$xl.DisplayAlerts = $false;
$xl.Visible = $true;
}
Catch
{
Write-EventLog -EventId "5001" -LogName "Application" -Message "Failed to start Excel" -Source "Application"
Exit
}
write-host "handling $i"
try
{
Write-Host $i
$wb = $xl.Workbooks.open($i);
$wb.RefreshAll();
$wb.Save();
Start-Sleep -s $sleepTime
$wb.Close($false);
$xl.quit();

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
}
catch
{
write-host $_.Exception.GetType().FullName
Write-EventLog -EventId "5001" -LogName "Application" -Message "Failed refreshing the workbook $i $_" -Source "Application"
}

 

 

No RepliesBe the first to reply

Resources