Trapping Excel error messages in Powershell Scheduled scripts

Tagged: 

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of David Gelman David Gelman 3 years, 3 months ago.

  • Author
    Posts
  • #12553
    Profile photo of David Gelman
    David Gelman
    Participant

    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.

  • #12558
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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?

  • #12559
    Profile photo of David Gelman
    David Gelman
    Participant

    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: (:) [], 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

  • #12561
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    }
    
  • #12563
    Profile photo of David Gelman
    David Gelman
    Participant

    Hi Dave

    It works fantastically.

    Many thanks.

    David

You must be logged in to reply to this topic.