MS Access 2013 truncates longtext fields to 255 characters when exporting to excel?

Using Access 2013 with this code still truncated the long text fields to 255 Chars.

In my case I'm exporting from a Query.

I solve the problem with creating "Save Export" Definition (i.e. Export to Excel the Query and saving the export). 

Then used VBA with the following code:

     Dim strSavedExportFile As String

     strSavedExportfile = "Export-Notes_Excel"

    DoCmd.RunSavedImportExport (strSavedExportfile)

The Filename and path, format type are defined when manually exporting the query.

Option: "export data with formatting and layout" is checked. 

Do the "Save Export Steps" option, and save with a name that will be used above. 

This will export the file preserving the full text, and not requiring user intervention. 

Not my preferred solution, a bit kludgy, but it works.

Interesting.  I'm surprised that the long text fields were truncated using that code, but I'll bear your workaround in mind if I run into the problem.  Thanks for posting it.

What do you mean by "VBA automate an instance of Excel and push the data directly into the spreadsheet cells?" Isn't that what the DoCmd.TransferSpreadsheet command does? 

No.  TransferSpreadsheet doesn't actually run Excel, but rather writes to the workbook file directly.  I'm talking about something like this:

'------ start of code ------

Sub CopyToExcel(TableOrQuery As String, WorkbookPath As String, Optional WorksheetName As String)

    Dim rs As DAO.Recordset

    ' Note: the following declarations use early binding, so they require

    ' a reference to be set to the Microsoft Excel? <version> Object Library.

    ' Late binding could be used instead.?

    Dim appExcel    As Excel.Application

    Dim wb          As Excel.Workbook

    Dim ws          As Excel.Worksheet

    Dim iCols       As Integer

    Set rs = CurrentDb.OpenRecordset(TableOrQuery)

    Set appExcel = New Excel.Application

    Set wb = appExcel.Workbooks.Add()

    Set ws = wb.Sheets(1)

    ' Create a worksheet header row from the recordset's field names.

    With rs

        For iCols = 0 To .Fields.Count - 1

            ws.Cells(1, iCols + 1).Value = .Fields(iCols).Name

        Next

    End With

    ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True

    ' Starting on the next line of the worksheet, copy each row from the

    ' recordset to a new row in the worksheet.  Excel provides a simple

    ' method call to do this.

    ws.Range("A2").CopyFromRecordset rs

    ' Size columns to fit the data.

    ws.UsedRange.Columns.AutoFit

    ' Name the worksheet.

    If Len(WorksheetName) > 0 Then

        ws.Name = WorksheetName

    End If

    ' Save the workbook.

    wb.SaveAs WorkbookPath

    ' Terminate the Excel application.

    appExcel.Quit

End Sub

'------ end of code ------

The above function can can be called to copy a table or query into a new Excel workbook.

I know this is intentional, but I need a way to work around it. When exporting my data from the access database to an excel spreadsheet, it automatically truncates the data in longtext (memo) fields longer than 255 characters to the 255 limit. How can I go about getting around this using VBA? I'm currently doing DoCmd.TransferSpreadsheet and that doesn't work.

Are you exporting directly from the table, or from a query?  I don't have Access 2013 available at the moment, but in Access 2010 when you export a memo field to Excel, the field isn't normally truncated.  It will be truncated, though, if there is a format applied, or if you export from a query that groups on the field or (IIRC) uses SELECT DISTINCT.

If Access 2013 really won't export to Excel without truncating, then a workaround would be to use VBA automate an instance of Excel and push the data directly into the spreadheet cells.

Using Access 2013 with this code still truncated the long text fields to 255 Chars.

In my case I'm exporting from a Query.

I solve the problem with creating "Save Export" Definition (i.e. Export to Excel the Query and saving the export). 

Then used VBA with the following code:

     Dim strSavedExportFile As String

     strSavedExportfile = "Export-Notes_Excel"

    DoCmd.RunSavedImportExport (strSavedExportfile)

The Filename and path, format type are defined when manually exporting the query.

Option: "export data with formatting and layout" is checked. 

Do the "Save Export Steps" option, and save with a name that will be used above. 

This will export the file preserving the full text, and not requiring user intervention. 

Not my preferred solution, a bit kludgy, but it works.

What do you mean by "VBA automate an instance of Excel and push the data directly into the spreadsheet cells?" Isn't that what the DoCmd.TransferSpreadsheet command does?