Powershell 5.1 ISE suddenly fails to recognize EXCEL COM object creation

Welcome Forums General PowerShell Q&A Powershell 5.1 ISE suddenly fails to recognize EXCEL COM object creation

Viewing 2 reply threads
  • Author
    Posts
    • #225822
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      I have the ff. portion of a bigger Powershell 5.1 script that has been running smoothly for over 2 years:

      $Excel = New-Object -Comobject Excel.Application
      $Excel.Visible = $True
      $Excel.DisplayAlerts = $False
      $book = $Excel.Workbooks.Open($OutWDed2H)
      $sheet = $book.Worksheets.Item('PPWKS')
      $sheet.Unprotect('abc')
      

      When I ran it today (the script has not been changed one bit), it suddenly generated these error messages:

      Exception setting "Visible": "Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type
      'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the
      interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Element not found. (Exception from HRESULT:
      0x8002802B (TYPE_E_ELEMENTNOTFOUND))."
      At line:2 char:4
      + $Excel.Visible = $True
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
      + FullyQualifiedErrorId : ExceptionWhenSetting
      
      Exception setting "DisplayAlerts": "Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type
      'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the
      interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Element not found. (Exception from HRESULT:
      0x8002802B (TYPE_E_ELEMENTNOTFOUND))."
      At line:3 char:4
      + $Excel.DisplayAlerts = $False
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
      + FullyQualifiedErrorId : ExceptionWhenSetting
      
      You cannot call a method on a null-valued expression.
      At line:4 char:4
      + $book = $Excel.Workbooks.Open($OutWDed2H)
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull
      
      You cannot call a method on a null-valued expression.
      At line:5 char:4
      + $sheet = $book.Worksheets.Item('PPWKS')
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull
      
      You cannot call a method on a null-valued expression.
      At line:6 char:4
      + $sheet.Unprotect('abc')
      + ~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull
      
      

      Would be grateful for muched needed help, tips and/or guidance.  I suspect this has to do with recent Microsoft updates to my .Net or some related Windows OS that impactx EXCEL but have no idea where to begin.

      Thanking you in advance.

    • #225855
      Participant
      Topics: 0
      Replies: 14
      Points: 177
      Helping Hand
      Rank: Participant

      Hello Ramon,

      What Office version you have installed?

      Do you have multiple Office version installed side by side?

      I would start with simple Office repair.

      Also please review this StackOverflow thread:

      https://stackoverflow.com/questions/28066719/unable-to-cast-com-object-of-type-microsoft-office-interop-excel-applicationcla

       

      Hope that helps.

       

      • This reply was modified 4 weeks ago by AndySvints.
    • #225870
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      Hello AndySvints,

      Many thanks for your response.  I have Office 2019 Home & Student ed. on my laptop.  No I do not have any other Office versions installed.  However, I have VISIO 2007 installed and with it Office 2007 SP3.  This could not be the reason my PS 5.1 script is failing because the VISIO and the Office 2007 SP3 were installed more than a 2 months ago, and the script was still working after that installation.  For what it’s worth, I  checked the EXCEL assembly (which I was told by another blog / source) may be the reason:

      
      GAC Version Location
      ---     -------    --------
      False v2.0.50727  C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Micr…
      
      

      Thanks for the Stack OV reference.  I hope it leads me to the root cause.
       

       

Viewing 2 reply threads
  • You must be logged in to reply to this topic.