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 certain environments (more on this below). To simplify this to 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
... or in a different form as captured today (pwsh 7.2.6 under Windows Terminal, 2022-08-13):
PS> .\SpecifiedCastIsNotValid-Test.ps1
OperationStopped: ***\SpecifiedCastIsNotValid-Test.ps1:11
Line |
11 | $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
What is interesting here is that I don't understand where a DateTime is even coming into play here!
When this error is further expanded as shown by $Error:
ErrorRecord : Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
WasThrownFromThrowStatement : False
TargetSite : System.Collections.ObjectModel.Collection`1[System.Management.Automation.PSObject] Invoke(System.Collections.IEnumerable)
Message : Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
Data : {System.Management.Automation.Interpreter.InterpretedFrameInfo}
InnerException : System.InvalidCastException: Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
at CallSite.Target(Closure , CallSite , ComObject , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
HelpLink :
Source : System.Management.Automation
HResult : -2146233087
StackTrace : at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input)
at Microsoft.PowerShell.Executor.ExecuteCommandHelper(Pipeline tempPipeline, Exception& exceptionThrown, ExecutionOptions options)
#
Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
At ***\SpecifiedCastIsNotValid-Test.ps1:11 char:1
+ $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], InvalidCastException
+ FullyQualifiedErrorId : System.InvalidCastException
... or by Get-Error:
Type : System.Management.Automation.RuntimeException
ErrorRecord :
Exception :
Type : System.InvalidCastException
TargetSite : System.Object CallSite.Target(System.Runtime.CompilerServices.Closure, System.Runtime.CompilerServices.CallSite, System.Management.Automation.ComInterop.ComObject, System.Object)
Message : Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
Data : System.Collections.ListDictionaryInternal
Source : Anonymously Hosted DynamicMethods Assembly
HResult : -2147467262
StackTrace :
at CallSite.Target(Closure , CallSite , ComObject , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
CategoryInfo : OperationStopped: (:) [], InvalidCastException
FullyQualifiedErrorId : System.InvalidCastException
InvocationInfo :
ScriptLineNumber : 11
OffsetInLine : 1
HistoryId : -1
ScriptName : ***\SpecifiedCastIsNotValid-Test.ps1
Line : $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1
PositionMessage : At ***\SpecifiedCastIsNotValid-Test.ps1:11 char:1
+ $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PSScriptRoot : ***
PSCommandPath : ***\SpecifiedCastIsNotValid-Test.ps1
CommandOrigin : Internal
ScriptStackTrace : at <ScriptBlock>, ***\SpecifiedCastIsNotValid-Test.ps1: line 11
at <ScriptBlock>, <No file>: line 1
TargetSite :
Name : Invoke
DeclaringType : System.Management.Automation.Runspaces.PipelineBase, System.Management.Automation, Version=7.2.6.500, Culture=neutral, PublicKeyToken=31bf3856ad364e35
MemberType : Method
Module : System.Management.Automation.dll
Message : Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
Data : System.Collections.ListDictionaryInternal
InnerException :
Type : System.InvalidCastException
TargetSite : System.Object CallSite.Target(System.Runtime.CompilerServices.Closure, System.Runtime.CompilerServices.CallSite, System.Management.Automation.ComInterop.ComObject, System.Object)
Message : Unable to cast object of type 'System.Object[,]' to type 'System.DateTime'.
Data : System.Collections.ListDictionaryInternal
Source : Anonymously Hosted DynamicMethods Assembly
HResult : -2147467262
StackTrace :
at CallSite.Target(Closure , CallSite , ComObject , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Source : System.Management.Automation
HResult : -2146233087
StackTrace :
at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input)
at Microsoft.PowerShell.Executor.ExecuteCommandHelper(Pipeline tempPipeline, Exception& exceptionThrown, ExecutionOptions options)
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.
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.
Following are some details of where I'm currently seeing this tonight:
- Windows 11 Professional, Version 22H2, OS Build 22598.100 (Insider)
- Also reproducible under Windows 10.
- Excel from Office 365, Version 2203 Build 15028.20204 / 16.0.15028.20178 64-bit.
$PSVersionTable
Name Value
---- -----
PSVersion 5.1.22598.1
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.22598.1
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
- Visual Studio Code:
Version: 1.66.2 (user setup)
Commit: dfd34e8260c270da74b5c2d86d61aee4b6d56977
Date: 2022-04-11T07:46:01.075Z
Electron: 17.2.0
Chromium: 98.0.4758.109
Node.js: 16.13.0
V8: 9.8.177.11-electron.0
OS: Windows_NT x64 10.0.22598
Edit: As of 2022-08-12, am even seeing the same with the latest versions:
- Windows 11 Professional, Version 22H2, OS Build 22622.575 (Insider)
- Excel from Office 365, Version 2207 Build 15427.20210 / 16.0.15427.20182 64-Bit.
$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: 1.70.1 (user setup)
Commit: 6d9b74a70ca9c7733b29f0456fd8195364076dda
Date: 2022-08-10T06:08:33.642Z
Electron: 18.3.5
Chromium: 100.0.4896.160
Node.js: 16.13.2
V8: 10.0.139.17-electron.0
OS: Windows_NT x64 10.0.22622
Tests:
I previously had a grid I was tracking here with results across environment (Visual Studio Code, Windows PowerShell ISE, Windows PowerShell / Windows Terminal, PowerShell / Windows Terminal, and PowerShell / cmd, etc.) - but realized I'm still seeing inconsistent results across even some of these combinations.
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.
As of 2022-08-13, I am seeing the following - which at the moment, I can reproduce repeatedly:
- I had opened "PowerShell 7 (x64)" from the Start Menu, which opened in Windows Terminal. Running the above
.\SpecifiedCastIsNotValid-Test.ps1fails. - Running the same in a new window of the same works.
- Re-running in the original window (#1) still fails.
- Running
pwshin the original window to open a child PowerShell process, then re-running the test succeeds. - Exiting the child process to revert back to the original PowerShell process, then re-running the test fails.
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?
Updates:
- 2022-08-12: Also now reported to https://github.com/PowerShell/vscode-powershell/issues/4130 - though I've now been able to reproduce this outside of VSC, and this will probably need to be closed.
- 2022-08-13: Opened a Microsoft Support Ticket - #32439207 - referencing this post.