Exporting a query using VBA

[BarbaraAnne]

Hello
I have almost successfully written VBA code to export the results of a query to an Excel file, however, one of the fields is very long, and gets truncated to 255 characters. If I manually export the query, it is fine. My code is:

Sub ExportProposal()
   Dim strQuery As String
   Dim strExportFile As String
       strQuery = "qryProposals"
       strExportFile = “C:\...\file.xls”
DoCmd.OutputTo acQuery, strQuery, acFormatXLS, strExportFile, False, "", 0
End Sub
 

where qryProposals is a selct query and C:\...\file.xls is the filename and path.

Could anyone help please?

Thank you

[MMcCarthy]

I'm afraid this is not really resolvable. Exporting a memo field to Excel is problematic at best.

[barry07]

I hate to disagree with an expert of Mary's eminence, but I have successfully exported memo fields of up to 798 characters in length - I don't know what the prcatical limit is.

I use a slightly diffrerent syntax:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sQuery, sOutFile

[MMcCarthy]

LOL! That's fine Barry.

It's not that it never works but it is riddled with problems.