Excel automation with .NET 1.1

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

I tried following the code below from the microsoft article Q306022 to
copy an array of data to excel in a single call. I only have a single
dimension in my array.

The problem I have is that even though the call:
oSheet.Range("A2").Resize(100, 3).Value = DataArray

works it only copies the first item from my DataArray. It copies this
value to each item in the defined range in Excel. It's as if excel
does not recognize that I'm sending an array and instead sets each
element to a single value.

My actual call is:
oXLWS.Range("A1").Resize(nValues.GetUpperBound(0) + 1, 1).Value =
nValues

I've also tried:
oXLWS.Range("A1:A10").Value = nValues

but I get the same result.

Any ideas????

Greg

*********************************
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()
*********************************
 
Microsoft support helped me resolve this issue. The key point is that
you must provide a two dimensional array even if you only want to fill
a single column of values in Excel.

Dim nValues(9, 0) as Integer
instead of
Dim nValues(9) as Integer
 
Back
Top