VBA Copyrecordset to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray C - 28 Mar 2007 02:02 GMT

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

Reply to this Message

RoyVidar - 31 Mar 2007 11:35 GMT

"Ray C" <RayC@discussions.microsoft.com> wrote in message
<EC8CD64E-D5AF-4358-84FD-BA579D23C899@microsoft.com>:

> I have a query in my Access database that I need to export in an
> existing pre-formatted Excel worksheet. I programmatically place the
[quoted text clipped - 9 lines]
> 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.

Signature

Roy-Vidar

Reply to this Message