M
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
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