Unable to understand RPC_E_SERVERFAULT Error

Question

I have a PowerShell Script that cycles through multiple Excel Workbooks, runs a few VBA routines, and then closes.

Unfortunately, I get the following error :

Exception calling "Run" with "1" argument(s): "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

THe strange thing is if I run the VBA Routines manually, I DO NOT get the above error. There is a lot of content online about this but nothing that solves my problem. ONe of the biggest culprits of this error is Foxit Addin, but I don't have that.

Any suggestions? Below is my ps1 script :

Function Excel-Quit {
 $Excel.Quit()
 # release the WorkSheet Com object
 [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
 # release the Excel.Application Com object
 [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null 
 # Force garbage collection
 [System.GC]::Collect()
 # Suspend the current thread until the thread that is processing the queue of finalizers has emptied that queue.
 [System.GC]::WaitForPendingFinalizers()
}
$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$Excel.DisplayAlerts = $False
$Excel.AskToUpdateLinks = $False
$ErrorActionPreference = "Stop"
$DTFormat = "yyyyMMddhhmm"
$TimeNow = Get-Date -Format "$DTFormat"
$Master_Bin = "C:\Users\administrator.hi\GCP\CGP - Reporting\Weekly_Hours_Reports"
$OutFile = "$env:LOGFILE" 
$Legacy = "$Env:INTRAPATH\_Legacy_Template_List.txt"
If (Test-Path "$Legacy") { Remove-Item "$Legacy"}
#New-Item "$Legacy"
$Invalid = 0
$LegacyTemplates = 0
$Array_Exclusion = @("_Master_Hours_Template_T&E.xlsm","Master_Hours_Template.xlsm")
$ExcelFiles = Get-ChildItem -Path "$Master_Bin\*" -Exclude *.zip,*.txt,*.csv -Include *.xlsm | `
 Where { !$_.PSisContainer }
Foreach($EFile in $ExcelFiles)
{
 
 Try { 
 
 # Ensure file is not already open
 [IO.File]::OpenWrite($EFile.fullname).close()
 
 # Get Excel file name without extension
 $FNWE = [io.path]::GetFileNameWithoutExtension("$($EFile.Name)")
 
 # Retrieve Date
 Try{
 $LWT = Import-CliXml "$Env:FILEPATH\CliXml\$FNWE.xml"
 }
 Catch{}
 #Process file only if $LWT does not equal LastWriteTime, indicating the file has been updated after formal refresh cadence (i.e. adding Notes, Adjustments etc)
 If($TimeNow -ne $EFile.LastWriteTime.ToString("$DTFormat")) {
 
 Try {
 $ReleaseObjectFLag = "True"
 Write-Output "Begin Processing : $($EFile.Name)"| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 
 $Workbook = $Excel.Workbooks.open("$Master_Bin\$($EFile.Name)", 0, $False)
 $Excel.Calculation = -4135
 $ExcelID = ((get-process excel | select MainWindowTitle, ID, StartTime | Sort StartTime)[-1]).Id
 Write-Output " Execute 'ProcessReport' Sub Routine"| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 
 # Refreshes ERP Report into Master Template 'Results' Tab
 $Excel.Run("ProcessReport")
 
 If("$($EFile.Name)" -ne "Master_Hours_Template.xlsm" -And "$($EFile.Name)" -ne "_Master_Hours_Template_T&E.xlsm"){
 
 Write-Output " Execute 'CalculateWorkbook' Sub Routine"| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 $Excel.Run("CalculateWorkbook") 
 
 $Workbook.Sheets.Item('Cover Page').Activate()
 # Check to ensure valid Client Name
 $Value1 = $Workbook.Worksheets.Item('Cover Page').Cells.Item(5, 2)
 
 If($Value1.Text -ne 'Effective Start Date')
 {
 Write-Output "Old Template : $($EFile.Name)"| Out-File -FilePath "$Legacy" -Append -Encoding ASCII
 $LegacyTemplates++
 }
 
 Write-Output " Execute 'ClientPrep' Sub Routine"| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 $Excel.Run("ClientPrep")
 Write-Output " "| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 
 $TimeNow = Get-Date -Format "$DTFormat" 
 "$TimeNow" | Export-CliXml "$Env:FILEPATH\CliXml\$FNWE.xml" -Force
 }
 
 $Workbook.save()
 $Workbook.close()
 
 }
 Catch {
 $ErrorMessage = $_.Exception.Message
 Write-Output "Failed : Refresh $($EFile.Name)"| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 Write-Output " $ErrorMessage"| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 $Workbook.save()
 $Workbook.close()
 
 Excel-Quit
 Exit 1
 }
 }
 }
 Catch {
 Write-Output "$_.Exception.Message"| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 Write-Output " "| Out-File -FilePath "$Outfile" -Append -Encoding ASCII
 }
 
 "$Invalid" | Export-CliXml "$Env:FILEPATH\CliXml\Invalid.xml" -Force
 "$LegacyTemplates" | Export-CliXml "$Env:FILEPATH\CliXml\LegacyTemplates.xml" -Force
}
# Call Excel-Quit Funtion
If($ReleaseObjectFLag) {Excel-Quit}
Exit 0

Thank you!

• jdweng: Usually errors like your are due to a null object. What line is causing the error?
• chrtak: So what's interesting, if I comment out the following line I don't get the error : $Excel.Run("ClientPrep") Additionally, if DON"T comment that out but I put a $Workbook.save() ahead of it, I also don't get the error.
• jdweng: Maybe Auto Calculation is not turned on so save is doing the auto calculation. Try adding Calculate before the Run. Calculation = -4135 is manual. Automatic is -4105. You can try adding $Excel.Calculate to manually calculate entire workbook.
• chrtak: Upon further testing, that wasn't the solution (me adding $Workbook.save()). Interestingly enough, if I commented out the other calls to the Excel Subroutines but left $Excel.Run("ClientPrep"), it works. It almost seems like it's too much processing and getting bogged down?
• jdweng: It is probably the calculations are taking a lot of time. Check Task Manager while code is running and see memory and Utilization