>I've got some data I am placing into an excel sheet using automation. There are three tables totaling close to 500 fields and 50,000 records. It appears that it's going to take about 24 hours to write the data to excel. Is that right or am I doing something wrong?
>
>Thanks
Yes, that is right. Automation - cell by cell - is slow.
You have some options to speed this process up:
1) Copy to DBF, and open this in Excel (through Automation, of course). Then, give the file some "finishing touches" (column widths, titles, ...).
2) You can copy a bunch of data to the Clipboard (separated with Tabs and CR-LF). Then, use Automation to paste in Excel.
3) Some operations of cells can be done on groups (ranges) of cells. This will very likely speed things up. For instance, to make a bunch of cells bold, use something like this:
with oSheet
.Range(.Cells(1,1), .Cells(10,10)).Font.Bold = xlTrue
endwith
HTH, Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
