PowerShell Excel Interop - InvalidCastException, but only from VS Code

[ziesemer]

Prerequisites

• I have written a descriptive issue title.
• I have searched all open and closed issues to ensure it has not already been reported.
• I have read the troubleshooting guide.
• I am sure this issue is with the extension itself and does not reproduce in a standalone PowerShell instance.
• I have verified that I am using the latest version of Visual Studio Code and the PowerShell extension.
• If this is a security issue, I have read the security issue reporting guidance.

Summary

(Bringing this forward from https://stackoverflow.com/questions/71907697/powershell-excel-interop-invalidcastexception-but-only-from-vs-code , which has not resulted in even any suggestions over the past 3 months. Another user had posted what appears to be an identical issue over 3 years ago, at https://stackoverflow.com/questions/56533576/assignment-of-powershell-multi-dimensional-array-into-excel-workbook-range-fails - however, I'm no longer able to reproduce the same with only PowerShell outside of VS Code.)

I have a large PowerShell script that automates the creation of an Excel workbook from scratch. For months now, I have been unable to resolve one issue - but only when run from Visual Studio Code.

A minimal test case and the typical failure presented is included in the "Steps to Reproduce", below.

I can work-around this issue by assigning the values one cell at a time, but this is terribly less efficient - especially for assigning hundreds or even thousands of cells at once. The code certainly works in some environments, just not others - and it is representative of code samples over 10 years old (though typically without the PowerShell or interop components), so it isn't really anything "new" or bleeding-edge.

This issue sometimes seems to come and go. However, it is seen by multiple users across multiple devices in multiple environments, separately maintained. All that is effectively required here is PowerShell + Excel on a currently-supported Windows OS.

Tests:

Environment	PowerShell Version	Result
Visual Studio Code	5.1	Fails
Visual Studio Code	7.2	Fails
Windows PowerShell ISE	5.1	Success
Windows PowerShell / Windows Terminal	5.1	Success
PowerShell / Windows Terminal	7.2	Success

I repeated the same 3+ times each with consistent results.

I recalled in the past killing the terminal within VS Code (clicking the trash can icon to kill the "PowerShell Integrated Console") and then re-launching it when prompted. Sure enough, that has again - but temporarily - resolved the issue, now allowing for a successful run of both the minimal test case and the entire script from VS Code under PS 5.1, even though I started from a clean session for the above tests. Restarting VS Code now, I'm no longer able to reproduce the issue - but am sure the problem will reappear after restarting Windows or another set of circumstances I've yet to identify.

In short, what is causing this array assignment to sporadically fail? What else can I check that may be altering how PowerShell runs from within the VS Code terminal than elsewhere?

PowerShell Version

PS> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.2.6
PSEdition                      Core
GitCommitId                    7.2.6
OS                             Microsoft Windows 10.0.22622
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Visual Studio Code Version

PS> code --version
1.70.1
6d9b74a70ca9c7733b29f0456fd8195364076dda
x64

Extension Version

PS> code --list-extensions --show-versions | Select-String powershell

ms-vscode.powershell@2022.7.2
ms-vscode.powershell-preview@2022.8.1

# The release version, 2022.7.2, is disabled.  The issue presents regardless of which version is being used - including by several co-workers, who only have the release version installed.

Steps to Reproduce

A minimal test case:

$ErrorActionPreference = 'Stop'

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$wb = $excel.Workbooks.Add()

$wb.Worksheets('Sheet1').Range('C2:C5').Value2 = 'abc'
$arr1 = $wb.Worksheets('Sheet1').Range('C2:C5').Value2

# Try a reflexive test first.  This seems to always fail whenever the below does.
$wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1

# Assuming we made it that far, try to overwrite with "x0, x1, x2, x3"...

$arr2 = [object[,]]::new(4, 1)
for($i = 0; $i -lt 4; $i++){
    $arr2[$i, 0] = ('x' + $i)
}

$wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr2

The typical failure presents as:

Specified cast is not valid.
At line:11 char:1
+ $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], InvalidCastException
    + FullyQualifiedErrorId : System.InvalidCastException

I'm more than familiar with a class cast exception, and this is not one - at least not from the code written and shown. I could see that this could be an exception from some interop or system code itself, but am then struggling to see the details of what or where that is.

Visuals

No response

Logs

No response

[andyleejordan]

Did you figure out what was going on?

[ziesemer]

I did, and mean to update both this and the SO post - but spent too many hours last evening working through this that I'm still working to catch-up on. :-)

Turns out, the line needed to cause this (but not reproduce this) is not contained in what was previously provided here. I still need to figure out the exact circumstances for reproducing then resolving (will reply with a further follow-up once I have) - but effectively a line of prior PS code can effectively "poison" the COM API session for future use.

Ultimately, adding a prior call to this line will cause this issue later:

$wb.Worksheets('Sheet1').Cells(1,1).Value2 = Get-Date

Interestingly, the cell updates with the current date as expected, and no error is thrown from that line. However, once executed - then at any point later executing the minimal test case as provided above, the test case will fail.

The proper fix for the above line is:

$wb.Worksheets('Sheet1').Cells(1,1).Value2 = [double]((Get-Date).ToOADate())

I'm guessing there is some sort of type confusion in the underlying interop code. It is just interesting how it still succeeds, but doesn't manifest until later - and is still making this quite difficult to demonstrate and reproduce.

This certainly is no longer related to the VSC PS extension - but if anyone else on the PS team wanted to pursue this further, please feel free to re-engage me...

[SeeminglyScience]

@ziesemer I think that's actually the same problem as PowerShell/PowerShell#16926. Or at least the same resolution. Feel free to add to that thread or throw it a 👍