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.)

 

[formarley]

Don't know enough to explain why Excel cuts (truncates) memo fields exported from Access. However, there is a way around it.

You can use Microsoft Query bundled with Excel to create a query that will return results in your Excel spreadsheet. It functions much the same as an Access query, but it's in Excel instead. Though it is not quite as dynamic as Access query building, it should provide the same function:

Excel Commands:
1)Open a new document
2)Click on "Data" on the menu bar
3)Alongside the "Import Extral Data" menu, choose "Import Data"
4)Find and select the Access Database file in question
5)Select the Table/Query you wish to view, click "OK"
6)Excel will then prompt you to select a location on the worksheet for the query results to show, click "OK"
7)You should see the result of the query on the screen; Memo fields not truncated...

If you end changing records in Access, you can also update the results on the Excel worksheet, by right-clicking anywhere on the worksheet, click "Refresh Data" and the results will update.