Formatting Excel worksheet in VBA 2

[CautionMP]

Long story short, I create a report in Microsoft Excel by using OLE automation to move data from Microsoft Access to a blank worksheet using a Row/Column loop.

Now here's the question: From a speed & file size perspective is it quicker (with automation) to apply Cell formatting:[ol]
[li]One Cell at a time?[/li]
[li]Or to a Range of Cells?[/li][/ol]

I would build test routines and monitor time/size but as usual the difference between 'done' and 'done right' is a paycheck.

Thanks in advance for any input,
CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

Hi,

2!

Skip,

_[red][/red]

 

[PHV]

using a Row/Column loop
Have you tried the CopyFromRecordset method of the Range object ?
apply Cell formatting
to a Range

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

I was just about to make PHV's first point. One swell foop with the CopyFromRecordset method.

Skip,

_[red][/red]

 

[CautionMP]

Well I'll be dipped in pooh.

I've seen both of you mention [tt]CopyFromRecordset[/tt] on numerous occasions. Being from the 'if it ain't broke don't fix it' crowd I never really tried it.

Based on the following, I'm a convert.

Table with 238 fields and 2,824 records. Two identical routines (well you know what I mean) in Access that create a new workbook and dump all the records to the first sheet without any formatting:
[ul][li]Row/Column loop, Time 0:10:27[/li]
[li][tt]CopyFromRecordset[/tt], Time 0:00:17[/li][/ul]

With [tt]CopyFromRecordset[/tt] I will have to apply the formatting to a range so I guess my original question just became moot.

Thank You Gentlemen,
CMP