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.
Does it do the same when ran both with and without admin?
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.
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.
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.
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
Thank you Mr Maurer. much appreciated.
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.
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.
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.
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()
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()
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.
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?
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
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.
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.
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.