Powershell: Cannot set the Value property for PSMemberInfo object of type "System.Management.Automation.PSParameterizedProperty"

[evg656e]

Steps to reproduce

1. Create empty excel file.
2. Open file via excel com api (https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model).
3. Try to get or set cell's value.

$app = New-Object -com Excel.Application
$book = $app.Workbooks.Open('path/to/excel/file.xlsx')
$sheet = $book.Worksheets.Item(1)
$sheet.Cells.Item(1, 1).Value = 123

Expected behavior

Cell value to be updated.

Actual behavior

Error:
Line |
  4 |  $sheet.Cells.Item(1, 1).Value = 123

     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Cannot set the Value property for PSMemberInfo object of type "System.Management.Automation.PSParameterizedProperty".

Environment data

Name                           Value
----                           -----
PSVersion                      7.0.0-rc.2
PSEdition                      Core
GitCommitId                    7.0.0-rc.2
OS                             Microsoft Windows 10.0.18363
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Notes

Value getter does not work too.

But calling InvokeSet or InvokeGet explicitly on item's value works fine, e.g.:

$sheet.Cells.Item(1, 1).Value.InvokeSet(123)

Powershell 6.2.4 affected by this bug also. Powershell 5.1.18362.145 works fine.

[mklement0]

Possibly related (though there it is about _not_ using .Item() explicitly): #4554

/cc @daxian-dbw

[daxian-dbw]

Yes, similar to the referenced issue, this one is also because the ComBinder is not supported in .NET Core (the call ComInterop.ComBinder.TryBindSetMember in PowerShell core is a stub method).
The Value property itself is a parameterized property with the definition like Variant Value (Variant RangeValueDataType). The ComBinder knows how to bind the value assignment call site, but without it, PowerShell ETS cannot directly assign value to a parameterized property.

[evg656e]

The problem applies only (?) to Value property, other properties i tried (Value2 and Formula) work as intended.

@mklement0I I think it's not possible to use properties like Cells or Rows from Powershell without Item method explicitly. All examples i have seen use it.

[mklement0]

I think it's not possible to use properties like Cells or Rows from Powershell without Item method explicitly

Indeed; fixing this limitation is what the previously linked #4554 is about.

[SeeminglyScience]

The problem applies only (?) to Value property, other properties i tried (Value2 and Formula) work as intended.

Worth noting (since it isn't obvious) that that's due to Value being a parameterized property and Value2 being a normal property.