set a range variable for the columns to format then use numberformat:

tablerange.NumberFormat= "$#,##0.00"




"Peter S." wrote:

One small note, I set a breakpoint on the TranslateType function and noticed
when it receives a type of Decimal it returns adCurrency. So that looked
great however I noticed that all my data shows up with a cell format of
General. Is this something that is out of my control?

"Peter S." wrote:

Your right! I tried it and as soon as I pulled the records from the database
the Excel spreadsheet was built instantaneously! This is definitely much
faster! Whereas before it would take at least 15 seconds minimum. For
everyone's information I also needed to include the COM reference: Microsoft
ActiveX Data Objects 2.8 Library in addition to the two functions you posted
in the first reply. Thanks again!!!!

Here is what I used for code (I started at A2 because I write column headers
in the spreadsheet)

Excel.Range tableRange = excelApp.get_Range("A2","A2");

tableRange.CopyFromRecordset(ConvertToRecordset(da taTbl),dataTbl.Rows.Count,dataTbl.Columns.Count);

================================================== ===
"Ralph" wrote:

It did not seem liike a good alternative to me either. But after testing it
on a datatable that was copying over 2,000 records I noticed a significant
improvement. I copied both of the functions from the web page to a new class
module in my project then used the

Range("A1").CopyFromRecordset(ConvertToRecordset(t ableToCopy))

in place of looping through each row in the datatable.

"Peter S." wrote:

This is an interesting solution, thanks for the response. Do I have to go
through some hoops to convert the DataTable to a recordset? It seems like it
is not a straighforward process. I am wondering if the conversion (to a
recordset) might only be somewhat more efficient than populating each cell
individually?? It would be optimal if I could use the DataTable without
conversion/modifications....

"Ralph" wrote:

Sorry I should have said you can use copyfrom recordset with the function
something like:

Range("A1").CopyFromRecordset(ConvertToRecordset(t ableToCopy))


"Peter S." wrote:

I am currently copying data from a DataTable to an Excel spreadsheet on a
cell by cell basis. I would like to speed things up by doing this on a row by
row basis or one big block copy. I can't seem to be able to figure out on how
to do this on a row by row basis or by one single command. Can anyone supply
a snippet of how this can be performed? Thanks!