I learned yesterday that using ADO to update an Excel range will not
update a formula. See
http://groups.google.com/groups?q=Ia...gle.com&rnum=1

Today I tried to use the suggestion from kb 247412 Use Automation to
Transfer an Array of Data to a Range on a Worksheet.

My question is about the line I marked below with <-- This line does
not work
I would like to be able to transfer the entire array with one line of
code.
If I cannot get it to work, my alternative is to loop and move once
cell at a time.

Your advice please.
Thanks in advance


Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
Next

'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")

'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray <-- This line
does not work

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit