VBA to export query to Excel truncates field at 255 char

[ajaeger]

I have a query that pulls from a SQL table. It pulls a field, COMPANY_DESCRIPTION that is defined as a text field. When I look at the query results in Access, I see all the characters in that field, for example, I see 1245 characters for Company A's description. When I export it from within Access (right-click the query and export to Excel), I also see all 1245 characters for that field. However, when I export it via code, I only see the first 255 characters of that field. I'm using:


strExportAs = strPath + "\" + strCompany + ".xls"

DoCmd.SetWarnings False
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, 8, "qryExportOne", strExportAs
DoCmd.SetWarnings True


I'm using Excel 2007.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[CautionMP]

ajaeger,
I think [tt]DoCmd.OutputTo[/tt] is the easy solution since it more closely matches what your doing through the UI.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[ajaeger]

Using the following, I'm still truncating at 255:

DoCmd.OutputTo acOutputQuery, "qryExportOne", acFormatXLS, strExportAs, False, , False

Am I missing something? Is there a way to reference the saved export setup that I have in Access that exports correctly?

Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com