Knowledge Base 247412 need help to make work
 

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

Knowledge Base 247412 need help to make work
 

Ian

The following code was supplied to me by Tom Ogilvy last year and
worked when I tried it

Assuming myarray is a 2-D array:

rw = Ubound(myarray,1)-lbound(myarray,1)+1
col = Ubound(myarary,2)-lbound(myarray,2)+1
Range("A1").Resize(rw,col).Value = myArray


---
Message posted from http://www.ExcelForum.com/

Knowledge Base 247412 need help to make work
 

This slightly modified version of the code worked fine from within Excel
(run from Excel VBA). No reason your line should not work.

Sub tester1()
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
Set oBook = ActiveWorkbook
'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

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

The modifications only pertained to running from inside excel with an
already open workbook - the problem area is no different.

--
Regards,
Tom Ogilvy

"Ian Ornstein" wrote in message
om...
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