Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to Excel Automation ADODB Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to Excel Automation ADODB Problem
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to Excel Automation ADODB Problem
"Matt Slattery" wrote ...
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...al_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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
access database (adodb)from excel vba | Excel Worksheet Functions | |||
XL & Access 2000, ADODB and the Find Method | Excel Programming | |||
Automation Code Problem from Access to Excel | Excel Programming | |||
ADODB Connection to Access | Excel Programming | |||
Export from Excel to Access ADODB | Excel Programming |