Transferring an array of data to Excel, this code doesn't work!
I'm acquiring data in from the comm port and storing it in an array. I followed MS's example on "Using automation to transfer an array of data to a range on a worksheet" There example works but when I try to put it in my code is doesn't. This is what I have:
Code:
Set objBook = objExcel.Workbooks.Add
objExcel.Cells(1, 1) = "Force"
objExcel.Cells(2, 1) = "(" & lblUnits.Caption & ")"
objExcel.Cells(1, 3) = Date
objExcel.Range("C:C").ColumnWidth = 9.5
For i = 2 To Index Step 3
Format(j) = Mid$(DataArray(i), 4)
j = j + 1
Next i
objExcel.Cells(1, 6) = j
objExcel.Range("C4", "C10").Value = "Hello"
objExcel.Range("B4").Resize(100).Value = Format
objExcel.Cells(1, 5) = Format(2)
Everything works except:
Code:
objExcel.Range("B4").Resize(100).Value = Format
If I change that line to say, Format(6) for example, then it prints that array value in 100 cells. It doesn't seem to want to print the entire array. What's wrong? I've tried putting parenths at the end and some other things with no luck. It will only "print" a single value multiple times, never multiple values one time, or something like that!
Solution - What MS doesn't tell you...
OK, I got it to work. What MS fails to mention in their example is the Resize method must have both a row and a column definition. When you're typing it, the pop up tip says it's optional but it's not!
Here's MS's example on how to do this: (Their example has a little more detail but this is the basic idea)
Code:
Dim oExcel As New Excel.Application
Dim oBook As New Excel.Workbook
Dim DataArray() As Variant
Dim r As Integer
Private Sub Form_Load()
ReDim DataArray(25, 0)
Set oBook = oExcel.Workbooks.Add
For r = 0 To 20
DataArray(r, 0) = r 'puts data in columns
Next r
oExcel.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
oExcel.Range("A3").Resize(25, 1).Value = DataArray()
oBook.SaveAs "C:\Temp\aaa.xls"
oExcel.Quit
Set oExcel = Nothing
End Sub
If I change DataArray to be one dimensional, it doesn't work. Or if it is 1D, and the Resize method only has one number, it doesn't work, try it! :p
Anywho, it's working and I'm happy now. I just wish MS would have made this more clear so I wouldn't have to drive myself crazy! :rolleyes:
