Powershell 7 does not recognize Cells() method of Excel module

Welcome Forums General PowerShell Q&A Powershell 7 does not recognize Cells() method of Excel module

Viewing 16 reply threads
  • Author
    Posts
    • #220377
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      I have a PS script which has been functioning rather well under PS 5.1.  When I started testing it under PS 7, it also worked.  Suddenly and only today April 19, 2020, did I encounter a surprising message (repeated hundreds of times because it is inside a loop) when running in PS 7:

      Invalid operation: C:\Users\myuserid\xxxx (details omitted)
      $sheet.cells($row,2).Value = $_.FullName
      Method invocation failed because [System._ComObject] does not contain a method named 'Cells'.
      

      I immediately re-ran the exact same script under PS 5.1 and everything worked without any error messages.  What’s surprising to me is that a few (2 maybe 3) weeks earlier this script which was never changed was also running well under PS7.  The database from which the data are being run are also identical.

      Would be grateful for any advice, tips or guidance.

      Thanking you all in advance.

       

    • #220380
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      Does it do the same when ran both with and without admin?

    • #220386
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      Yes, both were run as Administrator. (I have gotten into the habit of always running as Administrator, but before I sent this reply, I re-ran both again, as Administrator, just to be sure).

      Much thanks for your response.

    • #220392
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      I was asking if it error in 7 on both admin and non admin. You answered my question, what I was thinking is if excel is running not as admin then powershell as admin can’t read it, in my experience. If you ran normal powershell as admin and it work, then testing 7 in admin is equivalent.

      • #220395
        Participant
        Topics: 86
        Replies: 138
        Points: 677
        Rank: Major Contributor

        Yes, on PS 7, the same error message comes up whether running as Admin or as non-Admin.

        In PS 5.1, the error never occurs whether running as Administrator or not.

        Thanks again for your attention and kind consideration.

    • #220398
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      I suggested this to another person. If you’re going to be running it on windows and you just want to invoke it from 7, you could consider this.

      PowerShell 7.0 marks a move a to .NET Core 3.1, enabling significantly more backwards compatibility with existing Windows PowerShell modules. This includes many modules on Windows that require GUI functionality like Out-GridView and Show-Command, as well as many role management modules that ship as part of Windows.

      For Windows, a new switch parameter UseWindowsPowerShell is added to Import-Module. This switch creates a proxy module in PowerShell 7 that uses a local Windows PowerShell process to implicitly run any cmdlets contained in that module. For more information on Import-Module.

      https://docs.microsoft.com/en-us/powershell/scripting/whats-new/what-s-new-in-powershell-70?view=powershell-7

    • #220404
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      Thank you Mr Maurer. much appreciated.

    • #220512
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      Hello Mr Maurer,

      I executed the following command:

      PS C:\Users\MYacct\Documents > import-module importexcel -UseWindowsPowershell | install-module
      

      and got a warning, which did not lead to Install-module execution:

      WARNING: Module importexcel is loaded in Windows PowerShell using WinPSCompatSession remoting session;
      please note that all input and output of commands from this module will be deserialized objects.
      If you want to load this module into PowerShell Core please use 'Import-Module -SkipEditionCheck' syntax.
      

      Rather than do it right away as suggested in the warning:  i.e., run with the ‘-SkipEditionCheck’ parameter, I thought it safer to check with you before proceeding.  Would appreciate your comments and guidance.

      Many thanks.

    • #220533
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      All that is saying is that the objects returned are going to be deserialized. The object is not a live object so I’m not sure you’ll be able to set properties, you’ll have to see. I noticed that importexcel imports into powershell 7 fine. Could you possibly share some of your script so I could play with it? As far as -skipeditioncheck I’m not sure it matters since it imports fine in 7.

    • #220536
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      This is the difference between deserialized and not.

      With -UseWindowsPowershell

      $excel = Open-ExcelPackage C:\temp\excel1.xlsx
      $excel | gm
      
      TypeName: Deserialized.OfficeOpenXml.ExcelPackage
      
      Name               MemberType   Definition
      ----               ----------   ----------
      GetType            Method       type GetType()
      ToString           Method       string ToString(), string ToString(string format, System.IFormatProvider formatProvide…
      PSComputerName     NoteProperty string PSComputerName=localhost
      PSShowComputerName NoteProperty bool PSShowComputerName=False
      RunspaceId         NoteProperty guid RunspaceId=bb5b43b9-249c-4b35-977c-9c8bf5bce5a6
      Sheet1             NoteProperty Deserialized.OfficeOpenXml.ExcelWorksheet Sheet1=Sheet1
      Compatibility      Property     System.String {get;set;}
      Compression        Property     System.String {get;set;}
      DoAdjustDrawings   Property     System.Boolean {get;set;}
      Encryption         Property     System.String {get;set;}
      File               Property     System.String {get;set;}
      Package            Property     System.String {get;set;}
      Stream             Property     System.String {get;set;}
      Workbook           Property     System.String {get;set;}
      
      

      And without it.

      $excel = Open-ExcelPackage C:\temp\excel.xlsx
      $excel | gm
      
      TypeName: OfficeOpenXml.ExcelPackage
      
      Name             MemberType     Definition
      ----             ----------     ----------
      Dispose          Method         void Dispose(), void IDisposable.Dispose()
      Equals           Method         bool Equals(System.Object obj)
      GetAsByteArray   Method         byte[] GetAsByteArray(), byte[] GetAsByteArray(string password)
      GetHashCode      Method         int GetHashCode()
      GetType          Method         type GetType()
      Load             Method         void Load(System.IO.Stream input), void Load(System.IO.Stream input, string Password)
      Save             Method         void Save(), void Save(string password)
      SaveAs           Method         void SaveAs(System.IO.FileInfo file), void SaveAs(System.IO.FileInfo file, string pass…
      ToString         Method         string ToString()
      Compatibility    Property       OfficeOpenXml.Compatibility.CompatibilitySettings Compatibility {get;}
      Compression      Property       OfficeOpenXml.CompressionLevel Compression {get;set;}
      DoAdjustDrawings Property       bool DoAdjustDrawings {get;set;}
      Encryption       Property       OfficeOpenXml.ExcelEncryption Encryption {get;}
      File             Property       System.IO.FileInfo File {get;set;}
      Package          Property       OfficeOpenXml.Packaging.ZipPackage Package {get;}
      Stream           Property       System.IO.Stream Stream {get;}
      Workbook         Property       OfficeOpenXml.ExcelWorkbook Workbook {get;}
      Sheet1           ScriptProperty System.Object Sheet1 {get=$this.workbook.Worksheets["Sheet1"];}
      
      

      So if you need to use one of those missing methods then this will not work. I’m not sure if there is an easy way to “rehydrate” the object in this case. Hopefully someone else has more info on that. In the meantime if I get a chance I’ll see if I can reproduce your error.

      • This reply was modified 3 months, 3 weeks ago by Doug Maurer.
    • #220611
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      I have simplified the original script to its “bare essentials” so your efforts will not be wasted. Insofar as the error message I am getting is concerned, the following stripped down version works in PS 5.1 but fails on PS 7.  I fully understand your most recent explanation, there is no way this script can work or be made to work in PS 7 because the Object Type returned in PS5.1 is not the same as that in PS7.  I suppose this is due to .Net Core in PS7.

      There is an Excel template file that is part of the script that the script reads in.  I cannot see a mechanism in this forum to attach a file.  Thanking you for your assistance and kind consideration.  The code (about 55 lines) :

      <#
      Sample script involving EXCEL ComObject in PS 7.
      #>
      # ------------------------------------------------------------
      # Location of Excel template file
      #
      $InpExcel = 'C:\Users\xxt35\Documents\Excel_Template.xlsx'
      
      # ------------------------------------------------------------
      # Preparations for Excel output file
      # (Worksheet is password protected, pw='abc')
      #
      $Excel = New-Object -Comobject Excel.Application
      $Excel.Visible = $True
      $Excel.DisplayAlerts = $False
      $book = $Excel.Workbooks.Open($InpExcel)
      $sheet = $book.Worksheets.Item('PPWKS')
      $sheet.Unprotect('abc')
      
      # -------------------------------------------------
      # Blank out target data rows & columns
      #
      $sheet.Range("B3:B3").ClearContents | Out-Null
      $sheet.Range("B4:B4").ClearContents | Out-Null
      $sheet.Range("B6:B10").ClearContents | Out-Null
      $sheet.Range("C6:C10").ClearContents | Out-Null
      $sheet.Range("D6:D10").ClearContents | Out-Null
      $sheet.Range("E6:E10").ClearContents | Out-Null
      
      # -------------------------------------------------
      # Data to be inserted into Excel file
      #
      $List_of_Employees = @{"John"=10; "Bob"=20; "Tom"=30; "Roy"=40}
      
      # -------------------------------------------------
      # Insert the data into the Excel template file
      # One employee per row, starting in Row 6.
      #
      $row = 6
      foreach ($employee in $List_of_Employees.Keys) {
          $sheet.Cells($row,2).Value = $employee
          $sheet.Cells($row,4).Value = $List_of_Employees.Item($employee)
          $row++
      }
      
      # -------------------------------------------------
      # Save result to a new output file
      
      $SaveTo = 'C:\Users\xxt35\Documents\Excel_WithData.xlsx'
      If (Test-path -path $SaveTo) {Remove-item $SaveTo -force}
      $sheet.Protect('abc')
      $sheet.SaveAs($SaveTo,51)
      $Excel.Workbooks.Close()
      $Excel.Quit()
      
      

       

      • This reply was modified 3 months, 2 weeks ago by Ramon TAN.
    • #220767
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      Hello Mr Maurer,

      I have a stripped down version of the script that you asked for, in order that your efforts are not wasted.

      I fully understand your most recent reply, and know fully well that in PS7 which uses .Net Core, my stated problem cannot be solved, if only because PS 5.1 returns a COM Object Type different from the one returned under PS 7.

      As a layman/novice I cannot understand why Microsoft would want to issue a new Powershell that is “less than” the functionality of an older version.  I am sure there will be profound explanations in the various blogs out there, if I’m lucky enough to find them. I am clearly missing something fundamental.

      The script that reproduces that same error is shown below. The script reads an EXCEL template file (.xlsx). I cannot find a way to attach this file so my reply/post. It seems the PS Forum does not have such a feature.

      #
      # Sample script involving EXCEL ComObject in PS 7.
      #
      # ------------------------------------------------------------
      # Location of Excel template file
      #
      $InpExcel = 'C:\Users\xxt35\Documents\Excel_Template.xlsx'
      
      # ------------------------------------------------------------
      # Preparations for Excel output file
      # (Worksheet is password protected, pw='abc')
      #
      $Excel = New-Object -Comobject Excel.Application
      $Excel.Visible = $True
      $Excel.DisplayAlerts = $False
      $book = $Excel.Workbooks.Open($InpExcel)
      $sheet = $book.Worksheets.Item('PPWKS')
      $sheet.Unprotect('abc')
      
      # -------------------------------------------------
      #    Blank out target data rows & columns
      #
      $sheet.Range("B3:B3").ClearContents | Out-Null
      $sheet.Range("B4:B4").ClearContents | Out-Null
      $sheet.Range("B6:B10").ClearContents | Out-Null
      $sheet.Range("C6:C10").ClearContents | Out-Null
      $sheet.Range("D6:D10").ClearContents | Out-Null
      $sheet.Range("E6:E10").ClearContents | Out-Null
      
      # -------------------------------------------------
      #   Data to be inserted into Excel file
      #
      $List_of_Employees = @{"John"=10; "Bob"=20; "Tom"=30; "Roy"=40}
      
      # -------------------------------------------------
      #   Insert the data into the Excel template file
      #   One employee per row, starting in Row 6.
      #
      $row = 6
      foreach ($employee in $List_of_Employees.Keys) {
          $sheet.Cells($row,2).Value = $employee
          $sheet.Cells($row,4).Value = $List_of_Employees.Item($employee)
          $row++
      }
      
      # -------------------------------------------------
      # Save result to a new output file
      
      $SaveTo = 'C:\Users\xxt35\Documents\Excel_WithData.xlsx'
      If (Test-path -path $SaveTo) {Remove-item $SaveTo -force}
      $sheet.Protect('abc')
      $sheet.SaveAs($SaveTo,51)
      $Excel.Workbooks.Close()
      $Excel.Quit()
      
      

       

      • This reply was modified 3 months, 2 weeks ago by Ramon TAN.
    • #220902
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      Could you try adding the excel assembly before the rest of the script?

      [Reflection.Assembly]::LoadFile('C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll')
      
      

      Please note you may need to locate where your Microsoft.Interop.Excel. This code should find it for you.

      $exceldll = Get-ChildItem -path c:\windows\assembly -Include microsoft.office.interop.excel.dll -Recurse -ErrorAction SilentlyContinue | select -First 1
      
      [Reflection.Assembly]::LoadFile($exceldll)
      
      

      I hope this helps.

    • #220905
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      Yeah that didn’t work. For some reason PS7 is considering Cells() a method instead of a property. Even though it shows it as a property with get-member

      Cells Property Range Cells () {get}
      
      

      In regular powershell you can run these

      $sheet.Cells($row,4).Value | gm
      
      TypeName: System.Management.Automation.PSParameterizedProperty
      
      $sheet.Cells($row,4).Value.issettable
      True
      
      

      In PS7

      $Sheet.Cells($row,4) | gm
      InvalidOperation: Method invocation failed because [System.__ComObject] does not contain a method named 'Cells'.
      
      $Sheet.Cells | gm
      
      

      In fact the second command caused PS7 to hang, requiring ctrl + c. Could you possibly use range?

    • #220908
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      I’m not sure if you could use range or not, but at least it’s working in PS7 as your clearcontents command and the following output shows.

      $Sheet.Range('a1','a4').value
      
      IsSettable : True
      IsGettable : True
      OverloadDefinitions : {Variant Value (Variant RangeValueDataType)}
      TypeNameOfValue : System.Object
      MemberType : ParameterizedProperty
      Value : Variant Value (Variant RangeValueDataType) {get} {set}
      Name : Value
      IsInstance : True
      
      
    • #221157
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      Dear Mr Maurer,

      Many thanks for your responses.  I am right now overseas (and will be for a while due to Covid19) and the Internet and telephone infrastructure is very poor.  I saw your messages briefly by email and couldn’t even scroll down to read the rest.  I’ve attempted half a dozen Logins in the last 48 hours and always ended up failing in various degrees.  Now (12 AM, Manila time, April22) I finally got in safely and haven’t been cut off yet.  SO I am quickly printed all your messages before I get cut off.  I will revert with feedback after I try all your suggestions.  My sincerest gratitude for your inputs and kind consideration.

       

    • #221163
      Participant
      Topics: 4
      Replies: 481
      Points: 1,760
      Helping Hand
      Rank: Community Hero

      Well when you get this message, I’m not sure you’re going to be able to run this script in PS7 yet. Hopefully someone else has better information. Stay safe and good luck.

    • #221778
      Participant
      Topics: 86
      Replies: 138
      Points: 677
      Rank: Major Contributor

      Thanks Mr Maurer.

      (1) Range() fails when I put in the ($row,$column).Value line of code — the error message suggests I am performing an invalid operation on a ParametrizedProperty.

      (2) The following link suggests using the Add-Type -AssemblyName approach:

      https://stackoverflow.com/questions/59045043/powershell-6-doesnt-find-excel-interop-powershell-5-1-does

      I tried it various times, first with -AssemblyName, then with -Path and -LiteralPath, but it didn;t work.

      In any case, my “solution” (if it can be called one!) is to just stay with PS 5.1 since it works there.

      Thanks again for your tips and guidance.

       

Viewing 16 reply threads
  • The topic ‘Powershell 7 does not recognize Cells() method of Excel module’ is closed to new replies.