I want to insert data in Excel. As an datasource I have a multidimensional array: Say, I have this
dim myArray(5, 3)
for row = 0 to 4
for col = 0 to 2
myArray(row, col) = row * col
next
next
How can I insert these array in excel at cell index [0, 0]
A B C
1 0 0 0
2 0 1 2
3 0 2 4
4 0 3 6
5 0 4 8
Or for lazies:
Option Explicit
Dim a(4, 2) ' Ubounds
Dim r, c
For r = 0 To UBound(a, 1)
For c = 0 To UBound(a, 2)
a(r, c) = r * c
Next
Next
Dim oXls : Set oXls = CreateObject("Excel.Application")
Dim oWb : Set oWb = oXls.Workbooks.Add
oXls.Visible = True
oWb.Sheets(1).Range("A1:C5") = a
oXls.Quit
Evidence:
In case of problems:
According to this (you'll have to search for more up-to-date info yourself) -
Range("a1:a10").Value = Application.WorksheetFunction.Transpose(myarray)
adding more noise (.Value, .Transpose) may be necessary for all other versions of Excel except mine).
Excel arrays (including cell indexes) are 1-based, not 0-based like VBScript arrays, i.e. there is no cell index [0, 0]. However, since the offset is constant, it can be handled by simply adding 1 to the index of the VBScript array:
for row = 0 to UBound(myArray, 1)
for col = 0 to UBound(myArray, 2)
xl.Workbooks(1).Sheets(1).Cells(row+1, col+1) = myArray(row, col)
next
next
Sub ertdfgcvb()
Dim myArray(5, 3)
For Row = 0 To 4
For col = 0 To 2
myArray(Row, col) = Row * col
Next
Next
Dim rng As Range
Set rng = Range("A1")
For i = LBound(myArray, 1) To UBound(myArray, 1)
For j = LBound(myArray, 2) To UBound(myArray, 2)
rng.Offset(i, j) = myArray(i, j)
Next
Next
End Sub
