Access to Excel Automation ADODB Problem

[Matt Slattery]

Am stuck on a problem with Automation string character
limitations.

I am using ADODB recordsets and Late Binding Object
Automation to export data from Access to Excel. One of the
fields in this recordset is a memo field which will often
have more than 255 characters. Due to the Automation
Object limitations - only the first 255 character will be
inserted into Excel.
To overcome this problem I am splitting the memo fields
into 255 character chunks which are inserted into seperate
cells with the view of recombining them using the
CONCATENATE function.
After entering the formula in the cell and opening the
resulting file - I find that the formula still only
displays the first 255 characters. If you then select the
cell, click in the formula bar and confirm by pressing
enter - the entire string is then successfully displayed.

To try to overcome this I have tried every possible
combination of range and application recalculates. I have
tried using a find and replace on the "=" in the formula.
I have even tried making the Excel Application visible
during the running of the sub and emulating the cell
selection and confirmation by using SendKeys "{F2}" then
SendKeys "{ENTER}" - but this still does not work.

Any advice that the experienced coders can offer would be
GREATLY appreciated.
I am more than happy to provide more information if required.

Regards,
Matt Slattery

 

[Guest]

You may have already tried this, but did you try
combining them in VBA, rather than using an Excel
Formula, with something like:

Activecell.Value = Activecell.Offset(0,-2).Value &
Activecell.Offset(0,-1).Value

 

[Jamie Collins]

I am using ADODB recordsets and Late Binding Object
Automation to export data from Access to Excel. One of the
fields in this recordset is a memo field which will often
have more than 255 characters. Due to the Automation
Object limitations - only the first 255 character will be
inserted into Excel.

Why are you using automation at all? You don't even need a recordset
(but may want to use one if you are operating on an open workbook).

e.g. to create a new Excel table (and worksheet/workbook if
necessary):

SELECT
MyMemoCol
INTO
[Excel 8.0;HDR=YES;Database=C:\MyJetDB.mdb.].NewExcelTable
FROM
[Database=C:\MyJetDB.mdb.].MyTable
;

e.g. to append to an existing Excel table:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\MyJetDB.mdb.].[MyExcelTable]
(MyExcelCol)
SELECT
MyMemoCol AS MyExcelCol
FROM
[Database=C:\MyJetDB.mdb.].MyTable
;

If using an existing table, you need to ensure the relevant column is
'seen' by Jet as ype 'Memo'. For details, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

If you are using a connection to either the .mdb or the .xls, you can
omit the connection string from the sql text.

Jamie.

--