VBA Copyrecordset to Excel

[Guest]

I have a query in my Access database that I need to export in an existing
pre-formatted Excel worksheet. I programmatically place the results of this
query into a recordset, then I use CopyRecordSet to copy the data into a
specific location in the Excel sheet.

Problem:
One field is a Memo field and it looks like the first 255 characters get
transferred correctly, the rest either gets cut off or appears as gibberish
in Excel. Is there a way to send all the data in the Memo field using
CopyRecordSet ?

Thanks in advance
Ray

 

[RoyVidar]

I have a query in my Access database that I need to export in an
existing pre-formatted Excel worksheet. I programmatically place the
results of this query into a recordset, then I use CopyRecordSet to
copy the data into a specific location in the Excel sheet.

Problem:
One field is a Memo field and it looks like the first 255 characters
get transferred correctly, the rest either gets cut off or appears
as gibberish in Excel. Is there a way to send all the data in the
Memo field using CopyRecordSet ?

Thanks in advance
Ray

On some tests I did a while back, I found that with the
copyfromrecordset method, I could transfer up to 1823 with Access 2000,
but it didn't fail until the memo contained more than 65 509
characters.
In the 2003 version, it failed with more than 911 characters.

I don't know whether these are version specific limitations, or
limitations based on other particulars of my setups - so you'd
probably need to perform other tests yourself.

I think I would try to use CopyFromRecordset with the rest of the
recordset, then open a new recordset with only the memo field, loop it
and the cells through automation to place the content of the memo field
correct.

Another alternative, could be to use/try something like

SELECT <column list>, Left(MyMemo, 911)
FROM

but I wouldn't know how that would be performance wise.