why it doesn't work for me :(

[ma]

Hello,

I want to export some data to excel and I found the following document
on the web:



http://support.microsoft.com/default.aspx?scid=kb;en-us;306022



I cut and paste the code to my VBA program and trying to test it.



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

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

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

'Add headers to the worksheet on row 1.
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Order ID"
oSheet.Range("B1").Value = "Amount"
oSheet.Range("C1").Value = "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(sSampleFolder & "Book2.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()







I got an error on line that uses DataArray complaining that object wasn't
set. So I changed the type of DataArray to string but now I have another
problem in the following line,

oSheet.Range("A2").Resize(100, 3).Value = DataArray
it is complaning that object requred.

What should I do? What is wrong with the code?



Best regards

 

[Ken Snell [MVP]]

The code in that article appears to be erroneous. I'll report it to MS.

Try this modified code:

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(99, 2) As Variant
Dim r As Integer
For r = 0 To 99
DataArray(r, 0) = "ORD" & Format(r + 1, "0000")
DataArray(r, 1) = Rnd() * 1000
DataArray(r, 2) = DataArray(r, 1) * 0.07
Next

'Add headers to the worksheet on row 1.
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Order ID"
oSheet.Range("B1").Value = "Amount"
oSheet.Range("C1").Value = "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("PathToFolder\" & "Book2.xls")
Set oSheet = Nothing
Set oBook = Nothing
oExcel.Quit()
Set oExcel = Nothing

 

[Guest]

Sorry, just noticed your other message about exporting data from an unbound
listbox to Excel which I guess relates to this, so what I suggested wouldn't
be much use to you!

Good luck anyway,

Stuart

 

[Guest]

Hi,

I'm assuming you're exporting data from Access, but have you considered
creating a query and using the DoCmd.TransferSpreadsheet method? This has a
HasFieldNames argument so it will also export the column names for you.

If it's not Access data you're trying to export you might be better off in
an Excel discussion group.

Cheers,


Stuart