Exporting Access data to Excel seems to truncate text to 255 characters
Hi Experts,
I wonder if anyone can help with an exporting problem from MS Access 2003 to Excel 2003.
Basically the attached code creates an Excel spreadsheet and launches a template file (Word Template) that opens the exported spreadsheet, and runs a mail merge etc. The export item 'Qry_MM_Export' is a query.
The process works, however there is one column that exceeds 255 characters, and when it exports it to the spreadsheet, it seems to truncate the data. But when you look at the query itself, the data is all there.
Please could someone help me export all the data to the spreadsheet without it truncating.
(By the way, the truncated column is not a field from a table, it is text that comes in from a function)
Thanks VERY much in advance
Jpirozzolo.
I wonder if anyone can help with an exporting problem from MS Access 2003 to Excel 2003.
Basically the attached code creates an Excel spreadsheet and launches a template file (Word Template) that opens the exported spreadsheet, and runs a mail merge etc. The export item 'Qry_MM_Export' is a query.
The process works, however there is one column that exceeds 255 characters, and when it exports it to the spreadsheet, it seems to truncate the data. But when you look at the query itself, the data is all there.
Please could someone help me export all the data to the spreadsheet without it truncating.
(By the way, the truncated column is not a field from a table, it is text that comes in from a function)
Thanks VERY much in advance
Jpirozzolo.
'//Now export the data and shell the file.
ExportFile = "C:\MailMergeExport.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry_MM_Export", ExportFile, True
a = ShellApp(TemplateFile, vbMaximizedFocus)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi folks, Thanks for your replies.
The query is based on 1 table, but it uses expressions to collect information and work out data from other records in the same table. It is the expression that is truncated to 255, and according to the link from capricorn1 (thank capricorn1), Access treats such expressions as 255 text fields. (I have the same issue with transfertext)
Apparently I need to do something with the query to append the results to a table or something like that. Not done that before, so will need to play around with it.
Any pointers will be really appreciated... Thanks!
The query is based on 1 table, but it uses expressions to collect information and work out data from other records in the same table. It is the expression that is truncated to 255, and according to the link from capricorn1 (thank capricorn1), Access treats such expressions as 255 text fields. (I have the same issue with transfertext)
Apparently I need to do something with the query to append the results to a table or something like that. Not done that before, so will need to play around with it.
Any pointers will be really appreciated... Thanks!
ASKER
Hi.
Thanks for your help in pointing me in the right direction. I have managed to resolve this issue now :)
Basically I decided to do away with the expression and create a memo field in the record that the query uses. Then before running the query, I created a private sub that collected the data that I wanted (the stuff that is over 255 chars and was originally an expression) and then using SQL, send it to the new memo field. The query then just opens up the record data with the new memo field. Then after exporting the data to excel, all info (after 255 chars) seems to be there. :)
Regards,
Joe.
Thanks for your help in pointing me in the right direction. I have managed to resolve this issue now :)
Basically I decided to do away with the expression and create a memo field in the record that the query uses. Then before running the query, I created a private sub that collected the data that I wanted (the stuff that is over 255 chars and was originally an expression) and then using SQL, send it to the new memo field. The query then just opens up the record data with the new memo field. Then after exporting the data to excel, all info (after 255 chars) seems to be there. :)
Regards,
Joe.
ASKER
Thanks for your help folks!!
a quick search in Access help will reveal how to use TransferText
Hope this helps