I'm pulling data from a Sql 2k5 table, looping through cols & rows and
populating the sheet.cells object, eventually I'm getting a famous Excel
error: Excel Exception HRESULT: 0x800A03EC. The code I'm getting the error
in is pasted into this post, I've read, and experienced, that this is VERY
slow. I read an article that some guy put the data into an ArrayList then
populated the cells that way but didn't show any code so it didn't help much.
I'd appreciate any help since I think I'll probably be fired soon if I don't
get this done ASAP; not to put that on anyone elses shoulders just some
advice would be great. At this point it's almost faster to do this by hand,
I'd really like to speed this up.

Code throwing the error:

foreach (Object obj in ColData)
{
string rowData = obj.ToString();
oSheet.Cells[iRow, colNum] = rowData;
colNum++;
}
iRow++;

Explaination: ColData at this point is an ArrayList, I'm putting the data
into a string thinking that maybe just passing in a string directly would be
better, NOPE. iRow is a variable that is dynamic, it won't be the same all
the time. It will never be less than zero and colNum is equal to the number
assigned to column the ColumnName is in. This is a complicated application
since it is a backend app to a WebApp, it appears that the data is being
written to the proper cells but this error is killing me, it is also
EXTREMELY SLOW.

Thanks all.

--
Kevin C. Brown
Developer

In VBA, you can dump the whole array in one go. Can't say about you chosen
language.
Certainly faster than writing a cell at a time.

Private Sub CommandButton1_Click()
Dim TempStr() As String

TextBox1.Text = "Line 1" & vbNewLine & "Line 2" & vbNewLine & "Line 3" &
vbNewLine & "Line 4" & vbNewLine & "Line 5"

TempStr = Split(TextBox1.Text, vbNewLine)

Range("A1").Resize(15).Value = Application.Transpose(TempStr)
'Or
Range("A1").Resize(UBound(TempStr) + 1).Value =
Application.Transpose(TempStr)

End Sub

NickhK

"KevinB" wrote in message
...
I'm pulling data from a Sql 2k5 table, looping through cols & rows and
populating the sheet.cells object, eventually I'm getting a famous Excel
error: Excel Exception HRESULT: 0x800A03EC. The code I'm getting the
error
in is pasted into this post, I've read, and experienced, that this is VERY
slow. I read an article that some guy put the data into an ArrayList then
populated the cells that way but didn't show any code so it didn't help
much.
I'd appreciate any help since I think I'll probably be fired soon if I
don't
get this done ASAP; not to put that on anyone elses shoulders just some
advice would be great. At this point it's almost faster to do this by
hand,
I'd really like to speed this up.

Code throwing the error:

foreach (Object obj in ColData)
{
string rowData = obj.ToString();
oSheet.Cells[iRow, colNum] = rowData;
colNum++;
}
iRow++;

Explaination: ColData at this point is an ArrayList, I'm putting the data
into a string thinking that maybe just passing in a string directly would
be
better, NOPE. iRow is a variable that is dynamic, it won't be the same
all
the time. It will never be less than zero and colNum is equal to the
number
assigned to column the ColumnName is in. This is a complicated
application
since it is a backend app to a WebApp, it appears that the data is being
written to the proper cells but this error is killing me, it is also
EXTREMELY SLOW.

Thanks all.

--
Kevin C. Brown
Developer

Sweet example NickHK.

Is there a delimiter you can use for cells as well?