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!

I used the code from this web site, it uses copyfromrecordset in Excel. Works
well

http://www.codeproject.com/cs/databa...oRecordset.asp

"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!

I'm not sure exactly what the code would be in C#, but all you need is
to get the excel workbook object and then set an array equal to the
range of cells you're wanting to pull. Assuming you already have the
workbook object (let's say MyWorkbook) since you're already pulling
cell by cell:

Dim arrRange
arrRange = MyWorkbook.Range("A1:C10")


On Oct 30, 8:33 am, Peter S. <Pet...@discussions.microsoft.com> 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!

Oh, I totally misunderstood the question. My apologies...


On Oct 30, 8:48 am, "crfergu...@gmail.com" <crfergu...@gmail.com>
wrote:
> I'm not sure exactly what the code would be in C#, but all you need is
> to get the excel workbook object and then set an array equal to the
> range of cells you're wanting to pull. Assuming you already have the
> workbook object (let's say MyWorkbook) since you're already pulling
> cell by cell:
>
> Dim arrRange
> arrRange = MyWorkbook.Range("A1:C10")
>
> On Oct 30, 8:33 am, Peter S. <Pet...@discussions.microsoft.com> 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!- Hide quoted text -
>
> - Show quoted text -

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

Range("A1").CopyFromRecordset(ConvertToRecordset(tableToCopy))


"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!

However, in spite of misreading your question, I don't think the
content of the link posted addresses it either. Perhaps if you can
get the contents of the datatable into an array (or it might work
straight from the datatable if the Excel object recognizes it as an
array), you can simply set the Excel Range equal to your array. Kind
of the reverse of what I posted in the first place. You just have to
specify the range specifically. For example if your datatable has 5
columns and however many rows:


MyWorkbook.Range("A1:E" & ubound(YourZeroBasedArray)+1) =
YourZeroBasedArray


On Oct 30, 8:48 am, "crfergu...@gmail.com" <crfergu...@gmail.com>
wrote:
> I'm not sure exactly what the code would be in C#, but all you need is
> to get the excel workbook object and then set an array equal to the
> range of cells you're wanting to pull. Assuming you already have the
> workbook object (let's say MyWorkbook) since you're already pulling
> cell by cell:
>
> Dim arrRange
> arrRange = MyWorkbook.Range("A1:C10")
>
> On Oct 30, 8:33 am, Peter S. <Pet...@discussions.microsoft.com> 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!- Hide quoted text -
>
> - Show quoted text -

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(tableToCopy))
>
>
> "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!

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(tableToCopy))

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(tableToCopy))
> >
> >
> > "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!

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(dataTbl),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(tableToCopy))
>
> 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(tableToCopy))
> > >
> > >
> > > "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!

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(dataTbl),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(tableToCopy))
> >
> > 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(tableToCopy))
> > > >
> > > >
> > > > "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!