I have been trying to find a solution for this error for a few hours, all the solutions I found didn't fix the error I get when setting Range.Value2 = Value while trying to populate an array into an excel spreadsheet. Currently the array has a length of only 1 in size, I've tried it with bigger array and so far it just throws me this exception in the last line of code:
System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'
The code:
Imports ExcelDna.Integration
Imports Excel = Microsoft.Office.Interop.Excel
Public Function TestFunction()
'Dim data = New Object(1, 2) {{1, 2, 3}, {2, 3, 4}}
Dim app As Excel.Application = CType(ExcelDnaUtil.Application, Excel.Application)
Dim worksheet As Excel.Worksheet = CType(app.ActiveWorkbook.ActiveSheet, Excel.Worksheet)
Dim rng As Excel.Range = CType(worksheet.Cells(2, 1), Excel.Range)
Dim arr() As String = New String() {"hello world"}
'Tried this rng.Value2 = arr as well
rng.Resize(1, arr.Length).Value2 = arr
End Function
My goal is to be able to copy a matrix to the excel spreadsheet but so far I just need help in fixing the error that the code throws with an array. Thank you for future replies!
Range.Value2 is a two-dimensional array/matrix, you have to use such an array, too.
Dim arrLine1() As String = {"Hello World!", "red", "green"}
' convert to 2-dimensional array
Dim arr(,) As String
Redim arr(1, arrLine1.Length)
For i = 0 To arrline1.Length - 1
arr(0, i) = arrLine1(i)
Next
rng.Resize(1, arr.GetUpperBound(1)).Value2 = arr