Trapping Excel error messages in Powershell Scheduled scripts

PowerShell Help
1

Hi. I am new to powershell and have had to convert old VBA code on an old server to prosershell 3 on a Windows 2012 server.
An OpenVMS system generates Excel XML spreadsheets complete with data which are deposited in a folder on the 2012 Server. A schedule service runs a powershell script periodically (every 5 minutes) which converts the Excel XML file to an Excel XLSX spreadsheet which can then be accessed by users.
If there are any errors in the XML code, (e.g. insufficient columns declared) then Excel pops up a message box with the error and the operator has to log in and accept the error or else Excel will just hang around awaiting.
If I set $objExcel.DisplayAlerts = $False then Excel exits with an error and no operator action is required.
My problem is that the error returned by Excel to Powershell in $error bears no relation to that error that would have been displayed in the Excel Message box had DisplayAlerts been set to $True.
How do I get the actual Excel error causing the problem.

I have attached the whole Powershell script which also emails the resultant Excel spreadsheet to a user. That part works fine.

2

You may not be able to; it depends on how the Excel Automation classes are written. What are you seeing in $error[0] when a problem occurs?

3

Hi Dave
Thanks for your reply. I was hoping there might be some variable that Excel uses to store the error code or message.

This is the contents of $Error[0]

Remove-Variable : Cannot find a variable with name ‘UserWorkBook’.
At C:\inetpub\ftproot\XML_Convert.ps1:71 char:13

  •         Remove-Variable UserWorkBook

  •         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (UserWorkBook:String) [Remove-Variable], ItemNotFoundException

The first error generated was:

Exception calling “Open” with “1” argument(s): “Open method of Workbooks class failed”
At C:\inetpub\ftproot\XML_Convert.ps1:59 char:13

  •         $UserWorkBook = $objExcel.Workbooks.Open($strInputFile)

  •         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:slight_smile: , MethodInvocationException
    • FullyQualifiedErrorId : ComMethodTargetInvocation

If I set DisplayAlerts to $True, I get a message box titled “Problems during load” and the word “Worksheets” in the box.

Regards

David

4

OK, part of the confusion is that you’re looking at an error that has been wrapped in at least one extra layer of exception (MethodInvocationException, which is a PowerShell thing.) You can get at the original error like this; see if it gives you better information:

try
{
    $objExcel = New-Object -ComObject "Excel.Application" 
    $objExcel.Visible = $False
    $objExcel.DisplayAlerts = $False
    $UserWorkBook = $objExcel.Workbooks.Open($strInputFile)
}
catch
{
    $exception = $_.Exception

    while ($null -ne $exception.InnerException)
    {
        $exception = $exception.InnerException
    }

    # Display the properties of the original exception
    $exception | Format-List * -Force
}
5

Hi Dave

It works fantastically.

Many thanks.

David