locked
pivot table refresh RRS feed

  • Question

  •  

     I have a process which loops through a folder and finds all (.xls) files and does a RefreshAll to all of the tabs in the
    workbook. It works more than not, but is there a better way in Powershell to do this task? I call it from a cmd
    file. The SSAS is on a different server than the files(Pivot Tables) I'm trying to refresh. If it runs into an error "RPC server unavailable" than the process seems to lock up and I have to go kill the Xcel application within Task manager.

     any help or suggestions much appreciated.


    $ExcelApp = new-object -ComObject Excel.Application
    $ExcelApp.Visible = $false
    $ExcelApp.DisplayAlerts = $false
    
    get-childitem -path F:\NASalesReps *.xls -Recurse | % {
    	$date = get-date -uformat "_%d_%m_%Y_%H%M%S.bak"
    	Copy-Item $_.fullname ($_.fullname -replace ".xls",$date)
    	
    	$Workbook = $ExcelApp.Workbooks.Open($_.fullname, 3, $false, 5, $null, $null, $true)
    	$Workbook.RefreshAll()
    	$Workbook.Save()
    	 
    	#logging stuff
    	Write-Host $Workbook.Name
    	write-host $Workbook.Path
    	$Workbook.Close()
    	remove-variable Workbook
    }
    $ExcelApp.Quit()


    Tuesday, June 13, 2017 3:26 PM

Answers

  • Hi,

    Try this:

    $ExcelApp = new-object -ComObject Excel.Application
    $ExcelApp.Visible = $false
    $ExcelApp.DisplayAlerts = $false
    $xlCaclAuto=-4105
    get-childitem -path F:\NASalesReps *.xls -Recurse | % {
    	$date = get-date -uformat "_%d_%m_%Y_%H%M%S.bak"
    	Copy-Item $_.fullname ($_.fullname -replace ".xls",$date)	
    	$Workbook = $ExcelApp.Workbooks.Open($_.fullname, 3, $false, 5, $null, $null, $true)
    	$calculation=$ExcelApp.Calculation
    	$ExcelApp.Calculation=$xlCaclAuto
    	$Workbook.RefreshAll()
    	$ExcelApp.Calculation=$calculation
    	$Workbook.Save()	 
    	#logging stuff
    	Write-Output $Workbook.Name
    	write-Output $Workbook.Path
    	$Workbook.Close()
    	remove-variable Workbook
    }
    $ExcelApp.Quit()

    Sergei

    • Marked as answer by hart60 Wednesday, June 14, 2017 4:20 PM
    Tuesday, June 13, 2017 3:39 PM
  • I'm will propose a solution that can help.

    Create a job for each spreadsheet.  The script will not freeze and we can poll the jobs in a loop.  If a job exceed a particular time limit it can be killed by the script and reposted for a second try.

    Here is how to do this. You will have to test it and debug on your system.

    $jobscript = {
    	Param($FileName)

      $date = get-date -uformat "_%d_%m_%Y_%H%M%S.bak"
    Copy-Item $FileName($FileName-replace ".xls", $date)
    $ExcelApp = new-object -ComObject Excel.Application $ExcelApp.Visible = $false $ExcelApp.DisplayAlerts = $false $xlCaclAuto = -4105 $Workbook = $ExcelApp.Workbooks.Open($FileName, 3, $false, 5, $null, $null, $true) $calculation = $ExcelApp.Calculation $ExcelApp.Calculation = $xlCaclAuto $Workbook.RefreshAll() $ExcelApp.Calculation = $calculation $Workbook.Save() $Workbook.Close() $ExcelApp.Quit() } $jobs = get-childitem -path F:\NASalesReps *.xls -Recurse | ForEach-Object { Start-Job -Name $_.name -ScriptBlock $jobscript -ArgumentList $_.FullName Write-Host $_.fullname
    } # check for incomplete jobs after waiting 10 minutes $stuckjobs = $jobs | Wait-Job -Timeout 600 | Where {$_.State -ne 'Completed' } $stuckjobs | Stop-Job
    $jobs | Receive-Job | Remove-Job


    \_(ツ)_/






    • Edited by jrv Wednesday, June 14, 2017 7:27 AM
    • Marked as answer by hart60 Wednesday, June 14, 2017 4:20 PM
    Wednesday, June 14, 2017 7:03 AM

All replies

  • Hi,

    Try this:

    $ExcelApp = new-object -ComObject Excel.Application
    $ExcelApp.Visible = $false
    $ExcelApp.DisplayAlerts = $false
    $xlCaclAuto=-4105
    get-childitem -path F:\NASalesReps *.xls -Recurse | % {
    	$date = get-date -uformat "_%d_%m_%Y_%H%M%S.bak"
    	Copy-Item $_.fullname ($_.fullname -replace ".xls",$date)	
    	$Workbook = $ExcelApp.Workbooks.Open($_.fullname, 3, $false, 5, $null, $null, $true)
    	$calculation=$ExcelApp.Calculation
    	$ExcelApp.Calculation=$xlCaclAuto
    	$Workbook.RefreshAll()
    	$ExcelApp.Calculation=$calculation
    	$Workbook.Save()	 
    	#logging stuff
    	Write-Output $Workbook.Name
    	write-Output $Workbook.Path
    	$Workbook.Close()
    	remove-variable Workbook
    }
    $ExcelApp.Quit()

    Sergei

    • Marked as answer by hart60 Wednesday, June 14, 2017 4:20 PM
    Tuesday, June 13, 2017 3:39 PM
  • Hi,

    >>If it runs into an error "RPC server unavailable" than the process seems to lock up and I have to go kill the Xcel application within Task manager.

    Apart from Sergei's suggestion, we could consider this from another side.

    If the RPC server unavailable, we could bypass this server, keep working on another server, by using -ea silentcontinue in powershell, for instance:

    if(test-connection $server)
    {
      run your script
    }else{
      "the remote server is unavailable!"
    }

    Best regards,

    Andy


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, June 14, 2017 6:33 AM
  • Test-Connection does NOT test RPC service.  It only test network connectivity.  Look up RPC service to learn what it is.  It is fundamental to every Windows system and is used in all modern computer systems and has been for decades. 

    The issue here is that some connection via RPC fails but the server has not failed.  Since the code uses the file system we have to assume these are mapped drives and that the remote server is experiencing a dynamic failure.  This has to be a call from an Excel worksheet that refreshes a connected table.  If Excel is being run as a task then this will happen and there is no remedy.  Microsoft is very explicit about Office apps not working reliably as scheduled tasks.

    https://technet.microsoft.com/en-us/library/cc787851(v=ws.10).aspx

    https://en.wikipedia.org/wiki/Remote_procedure_call

    An RPC failure in a database query will always time out.  If the query is set to never timeout then it will freeze  the script.  The only remedy is to set a timeout on the database query which may not fix the issue if Excel is running as a scheduled task.

    .So guessing that a ping will help is not useful and does not address the original question.

    Read up on RPC and on Pivot Tables and how they are refreshed in Excel.


    \_(ツ)_/


    • Edited by jrv Wednesday, June 14, 2017 6:48 AM
    Wednesday, June 14, 2017 6:46 AM
  • I'm will propose a solution that can help.

    Create a job for each spreadsheet.  The script will not freeze and we can poll the jobs in a loop.  If a job exceed a particular time limit it can be killed by the script and reposted for a second try.

    Here is how to do this. You will have to test it and debug on your system.

    $jobscript = {
    	Param($FileName)

      $date = get-date -uformat "_%d_%m_%Y_%H%M%S.bak"
    Copy-Item $FileName($FileName-replace ".xls", $date)
    $ExcelApp = new-object -ComObject Excel.Application $ExcelApp.Visible = $false $ExcelApp.DisplayAlerts = $false $xlCaclAuto = -4105 $Workbook = $ExcelApp.Workbooks.Open($FileName, 3, $false, 5, $null, $null, $true) $calculation = $ExcelApp.Calculation $ExcelApp.Calculation = $xlCaclAuto $Workbook.RefreshAll() $ExcelApp.Calculation = $calculation $Workbook.Save() $Workbook.Close() $ExcelApp.Quit() } $jobs = get-childitem -path F:\NASalesReps *.xls -Recurse | ForEach-Object { Start-Job -Name $_.name -ScriptBlock $jobscript -ArgumentList $_.FullName Write-Host $_.fullname
    } # check for incomplete jobs after waiting 10 minutes $stuckjobs = $jobs | Wait-Job -Timeout 600 | Where {$_.State -ne 'Completed' } $stuckjobs | Stop-Job
    $jobs | Receive-Job | Remove-Job


    \_(ツ)_/






    • Edited by jrv Wednesday, June 14, 2017 7:27 AM
    • Marked as answer by hart60 Wednesday, June 14, 2017 4:20 PM
    Wednesday, June 14, 2017 7:03 AM
  • If you have a large number of file to refresh then you will need to batch the jobs as the database connections are limited and you may exceed  your quota.  If that is the case create an array of files to process and process 10 or so at a time to reduce connections.  Be sure to remove all completed jobs.

    One of the most common causes of error is continuing to try to process Excel after an exception has been thrown.  The following can prevent this:

    $jobscript = {
    	Param(
    		[Prameter(Mandatory)]
    		$FileName
    	)
    	Try{
    		$ExcelApp = new-object -ComObject Excel.Application
    		$ExcelApp.Visible = $false
    		$ExcelApp.DisplayAlerts = $false
    		$Workbook = $ExcelApp.Workbooks.Open($FileName, 3, $false, 5, $null, $null, $true)
    		$ExcelApp.Calculation = [Microsoft.Office.Interop.Excel.XlCalculation]::xlCalculationAutomatic
    		$Workbook.RefreshAll()
    		$ExcelApp.Calculation = [Microsoft.Office.Interop.Excel.XlCalculation]::xlCalculationManual
    		$Workbook.Save()
    		$Workbook.Close()
    		$ExcelApp.Quit()
            }
    	Catch{
    		$_
           }
           [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)
           [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp)
    }


    \_(ツ)_/




    • Edited by jrv Wednesday, June 14, 2017 7:25 AM
    Wednesday, June 14, 2017 7:21 AM
  • Thank you both for the replies as both Posts worked. I now will do some more testing and figure
    which solution to use going forward.

     Many Thanks!!!
    Wednesday, June 14, 2017 4:20 PM