So I've got some data that I want to export to an excel file. Yesterday I found a site (http://www.kjmsolutions.com/datasetarray.htm) that had me pass it into an array and then populate the excel cells with the array. It worked perfectly fine yesterday, but I came in this morning, tried it, and got the following error:
"Exception from HRESULT: 0x800A03EC"
Now, I've found the line that it doesn't like, and I'll make it bold where I post my code. One of my co-workers told me to try and bypass the array and just send the dataset itself to excel, which I've been trying to get working. Until I figure that out I thought that I'd try and post my code here and see if anyone here can figure out why it refuses to work. The only thing we can come up with is that there might be something wrong with the data(since it worked yesterday, and works with data from other tables). Thank you in advance. Code is as follows(Most of the comments are from his site, just to help me remember what does what):
"Exception from HRESULT: 0x800A03EC"
Now, I've found the line that it doesn't like, and I'll make it bold where I post my code. One of my co-workers told me to try and bypass the array and just send the dataset itself to excel, which I've been trying to get working. Until I figure that out I thought that I'd try and post my code here and see if anyone here can figure out why it refuses to work. The only thing we can come up with is that there might be something wrong with the data(since it worked yesterday, and works with data from other tables). Thank you in advance. Code is as follows(Most of the comments are from his site, just to help me remember what does what):
Again, thank you.'rows of dataset variable
Dim rows As Integer = ds.Tables(TableForExcel).Rows.Count
'columns of dataset variable
Dim columns As Integer = ds.Tables(TableForExcel).Columns.Count
'Used to count rows in loop
Dim r As Integer
'used to count columns in loop
Dim c As Integer
'array to hold data - the size is set by the dataset 'rows' number and 'columns' data
Dim DataArray(rows, columns) As Object
'first we deal with the column in the loop - assigning the element in the dataarray of
'c. Dont worry about 'r' at this point. We are only 'filling the 'c' of the array
For c = 0 To columns - 1
DataArray(r, c) = ds.Tables(TableForExcel).Columns.Item(c).ColumnName
'next we deal with the row (r of the array) entries for the 'c' column - for that column we just created
'in the array - that is the part which threw me because I was expecting it to write the whole row.
'Notice we are still using the same value for c. It has not changed from the first loop.
For r = 0 To rows - 1
DataArray(r, c) = ds.Tables(TableForExcel).Rows(r).Item(c)
'will loop on rows here until all of that columns(c) values are collected
Next
'conclude the activity for that column - moving on until all columns are collected
Next
'now we write the data to the sheet - use A2 as we need to leave room for the header row
oSheet.Range("A2").Resize(rows, columns).Value = DataArray
'we write the header row - we do this cell by cell to allow for special formatting I could
'have combined it but it was easier this way
'count the columns involved
Dim columns2 As Integer
'the movement of the loop - necessary due to rows in excel not being zero based.
Dim columns3 As Integer = 1
'count the columns in the dataset(columns2
For columns2 = 0 To ds.Tables(TableForExcel).Columns.Count - 1
'assigning the values of the dataset column(columns3) in row 1 of spreadsheet
'(notice not zero based). We do not have to do anything with the row value as
'we know this is the only row we are interested in.
oSheet.Cells(1, columns3).Value = ds.Tables(TableForExcel).Columns(columns2).ColumnName
'next excel column we are going to write to - incrementing it upward
columns3 = columns3 + 1
'moving on to next dataset column
Next