I have this powershell function that converts XLS to CSV using the excel com object. Ignore MS_logoutput thats just a custom output formatter function.
The convert function is called within a script by a automated scheduler.
The scheduler is running on Domain\HOST1 and it starts the powershell process as Domain\User1. It runs this non interactivtly.
Until recently this has been running without issue, however recently the automated non interactive run of this job has begun to fail if there is a logged in session to the VM Domain\HOST1 with user Domain\User1. (this session can be logged in and disconnected and the issue persists)
The security for the DCOM Config is all set to custom and the Domain\User1 user has full control on all permission.
The indentity is set to "the launching user"
The Function:
Function MS_Convert-XLStoCSV
{
Param(
[string] $ExcelInputFilepath = "$InputPathBOJ\$($ROW.InputFileBOJ).xls",
[string] $CsvlOutputFilepath = "$InputPathBOJ\$($ROW.InputFileBOJ).csv",
$SheetIndexToSave,
$DeleteFirstRowsCount
)
Begin
{
MS_logoutput "Function:", $MyInvocation.MyCommand, "Started"
}
Process
{
Try
{
& {
if ($null -eq $SheetIndexToSave)
{
$SheetIndexToSave = 1
}
if ($null -eq $DeleteFirstRowsCount)
{
$DeleteFirstRowsCount = 0
}
MS_logoutput "Adding Excel Type"
$excel = New-Object -comobject Excel.Application
MS_logoutput "Added Excel Type succesfully"
$excel.DisplayAlerts = $false
#$excel.Visible = $false
MS_logoutput "Opening $ExcelInputFilepath with Excel"
$OpenWorkbook = $Excel.Workbooks.Open("$ExcelInputFilepath")
MS_logoutput "Workbook opened correctly"
$OpenWorkbook.ConflictResolution = 2
MS_logoutput "Selecting sheet at index $SheetIndexToSave"
$sheetToSave = $OpenWorkbook.Sheets | Where-Object { $_.Index -eq $SheetIndexToSave }
MS_logoutput "Starting to save sheet"
while ($DeleteFirstRowsCount -gt 0)
{
$null = $sheettosave.cells.item(1, 1).EntireRow.Delete()
$DeleteFirstRowsCount--
}
$sheetToSave.SaveAs($CsvlOutputFilepath, 6)
MS_logoutput "File saved to $CsvlOutputFilepath"
MS_logoutput "Closing Excel"
$OpenWorkbook.Close($false)
$excel.Quit()
}
$null = [System.GC]::Collect()
MS_logoutput "Excel Closed"
}
Catch
{
MS_logoutput "Function:", $MyInvocation.MyCommand, "Failed with exception:" "Error"
MS_logoutput "Exception: $_" "Error"
MS_LogOutput "Exception: $($_.Exception.Message)" "Error"
MS_LogOutput "Inner Exception: $($_.Exception.InnerExceptionMessage)" "Error"
MS_LogOutput "StackTrace : $($_.Exception.StackTrace)" "Error"
$ErrorActionPreference = "SilentlyContinue"
$OpenWorkbook.Close($false)
$excel.Quit()
$null = [System.GC]::Collect()
$null = [System.GC]::WaitForPendingFinalizers()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($OpenWorkbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
$null = [System.GC]::Collect()
$null = [System.GC]::WaitForPendingFinalizers()
$null = Remove-Variable -Name excel
$ErrorActionPreference = "Continue"
throw "Exception: $_"
}
}
End
{
MS_logoutput "Function:", $MyInvocation.MyCommand, "Ended "
}
}
The Error output i get is
2020-07-20 19:25:19 | Info | Function: MS_Convert-XLStoCSV Started
2020-07-20 19:25:19 | Info | Adding Excel Type
2020-07-20 19:25:19 | Info | Added Excel Type succesfully
2020-07-20 19:25:19 | Info | Opening \\networklocation\some\subfolders\InputExcel.xls with Excel
2020-07-20 19:25:24 | Error | Function: MS_Convert-XLStoCSV Failed with exception:
2020-07-20 19:25:24 | Error | Exception: The remote procedure call failed. (Exception from HRESULT: 0x800706BE)
2020-07-20 19:25:24 | Error | Exception: The remote procedure call failed. (Exception from HRESULT: 0x800706BE)
2020-07-20 19:25:24 | Error | Inner Exception:
2020-07-20 19:25:24 | Error | StackTrace : at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.Automation.ComInterop.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, ComMethodDesc method, Object[] args, UInt32 argErr)
at CallSite.Target(Closure , CallSite , ComObject , String )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
