Export to Excel Problem - Data in MEMO field was cutted down to 255 characters

[M Ho]

Dear all,

I'm trying export a search result from the Form View by using the
following command.

Public Sub cmdExport_Click()
Dim fileName As String
fileName = InputBox("Export Excel file name:", "Enter file name")
DoCmd.OutputTo acForm, "SearchRecords", "MicrosoftExcel(*.xls)",
"c:\" & fileName & ".xls", True, ""
End Sub


However, as a result, for all the fields which the field type are MEMO
and containing data exceed 255 characters were cutted down to only 255
characters in the exported excel file. Hence, some data is missing
within those MEMO fields.

Looking forward for your precious advice.

Thanks a lot.

M Ho

 

[Douglas J. Steele]

What version of Excel? I know that in Excel 97, a cell cannot be longer than
255 characters, and I believe that's the default format when you export from
Access. You could try using the TransferSpreadsheet method instead of the
OutputTo approach, and specify acSpreadsheetTypeExcel9 as the spreadsheet
type.

 

[Joe Fallon]

Office 97 had this problem.
Fixed in 2000 and newer.

 

[Douglas J. Steele]

Yes, but isn't Excel 97 still the default export format?

 

[M Ho]

Thank you all so much !

I'm using Office 2000, but the OutputTo method still have this
problem.

However, if I use the TransferSpreadsheet method, I need to create a
temp table to hold the filtered record first and the problem comes
again after coping the record to the temp table, data within MEMO
fields is also cutted down to 255 characters only. I wonder did I did
something wrong ?

I tried the following to create a temp table and transfer to Excel


DoCmd.RunSQL "CREATE TABLE temp (Code TEXT, No TEXT, SentDate DATE,
New TEXT, Property MEMO, Particulars MEMO, Reference TEXT)"

DoCmd.RunSQL "SELECT * INTO TEMP FROM ((cmcm INNER JOIN cmin ON
cmcm.Reference = cmin.Reference) INNER JOIN cmpe ON cmcm.Reference =
cmpe.Reference) INNER JOIN DistMatching ON cmcm.Code =
DistMatching.Division WHERE " & Forms!searchForm!SQLquery
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TEMP",
"C:\CICS\export.xls"



Your advice would be much appreciated.

M HO

 

[Joe Fallon]

1. Try it directly first! Just use TransferSpreadsheet against the table
with the memo field.
(Prove that it works.)

2. Build a real table with a memo field as the data type.
The purge it and insert new records to it each time you want to use it.
(Guaranteed data types - make tables don't give you that.)