Solved: CopyFromRecordset without formatting

[lifeson]

Hi folks
I use this line to insert records onto a worksheet from an access database

'Write the records
ws.Range("A1").Offset(1, 0).CopyFromRecordset rst

This always removes any formatting on the worksheet. Can I add the records without amending the formatting a sort of:


.copyfromrecordset.PasteSpecial Paste:=xlPasteValues :doh: :doh:

[Bob Phillips]

Can't recall noticing that, but I don't do it much and when I do it tends to be in a raw data table, non-formatted, so it is not a problem.

As it seems to destroy existing format and there is no formatting options to the CopyFrom Recordset method, I guess tat you are stuck with formatting the data after the event.

[lifeson]

Thanks for that XLD - not the answer I was hoping for :( :(

[Bob Phillips]

Sorry, best I could do.

A thought that may be good for you, dump it to a staging area using CopyFromRecordset, then do a copy-pastevalues to the real area.

[Kenneth Hobs]

You can do as xld suggested or just iterate the recordset. It is not that hard.
http://vbaexpress.com/kb/getarticle.php?kb_id=889 (http://vbaexpress.com/forum/../kb/getarticle.php?kb_id=889)