0

I am trying to import a csv file into Excel using Powershell. The $Range.Value2 assignment fails with a cast error.

I've boiled the issue down to the simplest example.

$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$range = $workbook.ActiveSheet.Range("a1","b2")
$array = New-Object 'object[,]' 2,2
$array[0,0] = 'Cell1'
$array[0,1] = 'Cell2'
$array[1,1] = 'Cell3'
$array[1,0] = 'Cell4'
$range.Value2 = $array

Instead of inserting 'CellX' into a1, b1, a2, b2, I get --

PS C:\cygwin64\home\zkgf4jl\mfParser> C:\cygwin64\home\zkgf4jl\mfParser\Untitled2.ps1 Unable to cast object of type 'System.Object[,]' to type 'System.Management.Automation.PSReference`1[System.Management.Automation.LanguagePrimitives+Null]'. At C:\cygwin64\home\zkgf4jl\mfParser\Untitled2.ps1:10 char:1 + $range.Value2 = $array + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], InvalidCastException + FullyQualifiedErrorId : System.InvalidCastException

AndyS
  • 1
  • i don't know much about cygwin64 other than it's a shim for unix apps on windows but your code works fine here with PowerShell 5.1 on native Windows 10 and Office Professional Plus 2016. You might want to add more info about your environment as it's possibly related to that... – mclayton Jun 10 '19 at 21:17
  • What about explicit type casting as `$range.Value2 = [ref]$array`? – JosefZ Jun 10 '19 at 22:23
  • Cygwin plays no role in this error. This is just my home directory to allow a centralized point to launch into remote Linux hosts. As far as Powershell is concerned, this is just a normal windows directory. – AndyS Jun 11 '19 at 12:56
  • PS version 5.1 Build 14409 Windows 7 Enterprise Service Pack 1 – AndyS Jun 11 '19 at 12:57
  • JosefZ -- adding the cast changed the error: PS C:\cygwin64\home\zkgf4jl\mfParser> ./Untitled2.ps1 Exception from HRESULT: 0x800A03EC At C:\cygwin64\home\zkgf4jl\mfParser\Untitled2.ps1:10 char:1 + $range.Value2 = [ref]$array + ~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException – AndyS Jun 11 '19 at 13:01
  • Over 2 years later, still dealing with the same! See also: https://stackoverflow.com/questions/71907697/powershell-excel-interop-invalidcastexception-but-only-from-vs-code – ziesemer Apr 18 '22 at 17:34

1 Answers1

0

I solved this by changing the assignment to $range.Value instead of Value2. This generic error, HRESULT: 0x800A03EC, pops up in working powershell excel applications time and time again. The recurring theme -- it was working yesterday, then a windows update was applied and my working code starts horking up exceptions.

AndyS
  • 1