Exception from HRESULT: 0x800A03EC

[scorpion53061]

Additional information: Exception from HRESULT: 0x800A03EC.

This code produced this error. I am pretty sure I got this to run a while
back and I was wondering if you all see anything I am doing wrong.....or do
you have a suggestion for a better way.......I do know this method is quite
slow.


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim WSheet As New Excel.Worksheet
Dim colindex As Integer
Dim Excel As New Excel.Application
Dim rowindex As Integer
Try
OleDbDataAdapter1.Fill(DataSet11.ITEMS)
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try

With WSheet
Dim col As DataColumn
colindex = 1
Dim rowon As Integer = 1
For Each col In DataSet11.Tables(0).Columns
'colindex += 1
Excel.Cells(rowon, colindex) = col.ColumnName.ToString
colindex = colindex + 1
Next col
Dim row As DataRow
colindex = 1
rowindex = 2
For Each row In DataSet11.Tables(0).Rows

For Each col In DataSet11.Tables(0).Columns
'colindex += 1
.Cells(rowindex, colindex).Value =
row(col.ColumnName).ToString()
rowindex = rowindex + 1
colindex = colindex + 1
Next col
Next row
Try
'Saving .xls file with Test.xls name
WSheet.SaveAs("C:\TEST.XLS")
Catch
End Try
'setting up caption that "File Created"
Me.Text = "File Created"
'closing down workbook
EXL.Workbooks.Close()
End With

End Sub

 

[scorpion53061]

I figured it out on the cell by cell approach.

If anyone could show me an example using this code how I could have used an
array to do this I would love to see it.....Thank You...

 

[Cor]

Hi Scorpion,
I have put that what I promished yesterday in that mailstring.
Cor

 

[scorpion53061]

This was the corrected code......if anyone could show me how to do this with
an Array in this code I would be greatful.....

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim WSheet As New Excel.Worksheet
Dim colindex As Integer
Dim Excel As New Excel.Application
Dim rowindex As Integer

Try
OleDbDataAdapter1.Fill(DataSet11.ITEMS)
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Excel.Visible = True
WSheet = Excel.Workbooks.Add.Worksheets.Add
With WSheet
Dim col As DataColumn
Dim i, r, c As Integer
For i = 0 To DataSet11.Tables.Count - 1
colindex = 1
Dim rowon As Integer = 1
For c = 0 To DataSet11.Tables(i).Columns.Count - 1
Excel.Cells(rowon, colindex).Value =
DataSet11.Tables(i).Columns(c).ColumnName
colindex = colindex + 1
Next
colindex = 1
rowon = 2
For r = 0 To DataSet11.Tables(i).Rows.Count - 1
For c = 0 To DataSet11.Tables(i).Columns.Count - 1
Excel.Cells(rowon, colindex).Value =
DataSet11.Tables(i).Rows(r).Item(c)
colindex = colindex + 1
Next
rowon = rowon + 1
colindex = 1
Next

Next
Try
'Saving .xls file with Test.xls name
WSheet.SaveAs("C:\TEST.XLS")
Catch
End Try
'setting up caption that "File Created"
Me.Text = "File Created"
'closing down workbook
EXL.Workbooks.Close()
End With

End Sub

 

[scorpion53061]

A 32,000 row spreadsheet using this method (14 columns) took almost 45
minutes.

If anyone has ideas on how to improve this situation I would be greatful.

 

[scorpion53061]

Problem Solved......

A 32,000 row spreadsheet using this method (14 columns) took almost 45
minutes.

If anyone has ideas on how to improve this situation I would be greatful.

 

[Herfried K. Wagner [MVP]]

Problem Solved......

How did you solve it?

 

[scorpion53061]

Sorry,

I am more familiar with ArrayLists than plain old arrays (I have only been
exposed to .NET stuff - kind of a newbie at this stuff and in situaitons
like this you work with what you know - at least kind of)

I wrote each column to an ArrayList and then wrote each arraylist to the
spreadsheet with a loop.

Only problem is I am having problems doing formatting of the data but the
boss can live without this.

 

[scorpion53061]

cut the 45 minutes down to under 10 seconds this way......

Sorry,

I am more familiar with ArrayLists than plain old arrays (I have only been
exposed to .NET stuff - kind of a newbie at this stuff and in situaitons
like this you work with what you know - at least kind of)

I wrote each column to an ArrayList and then wrote each arraylist to the
spreadsheet with a loop.

Only problem is I am having problems doing formatting of the data but the
boss can live without this.

 

[kcbtke252]

Perhaps some code?

I just tried the ArrayList concept and got the same error I've been getting; could you maybe give me a code snippet of how you're adding your data to the Lists? I'm sure you're doing it when you're pulling the data out of the DataSet or however you're getting youre data.

I'd appreciate it, thank you.

Kevin

cut the 45 minutes down to under 10 seconds this way......

"scorpion53061" wrote in message
news:%23JJCpz%[email protected]...
> Sorry,
>
> I am more familiar with ArrayLists than plain old arrays (I have only been
> exposed to .NET stuff - kind of a newbie at this stuff and in situaitons
> like this you work with what you know - at least kind of)
>
> I wrote each column to an ArrayList and then wrote each arraylist to the
> spreadsheet with a loop.
>
> Only problem is I am having problems doing formatting of the data but the
> boss can live without this.
>
> "Herfried K. Wagner [MVP]" wrote in message
> news:uLuI4s%[email protected]...
> > * "scorpion53061" scripsit:
> > > Problem Solved......
> >
> > How did you solve it?
> >
> > --
> > Herfried K. Wagner
> > MVP · VB Classic, VB.NET
> >
>
>

 

[jalpaeol]

pls can a

any one can help me i have same prob with excel file but i not understand what is solution from this all post



it is urgent pls reply me quick if you can

just hightlight the solution pls




thanks

 

[jalpaeol]

can any one reply me i am not geting what is solution in this post

pls highlight the solution for me pls

thanks

it is urgent pls