Exporting Data to Excel Truncates Long Text Form

[nbonzani]

Hello all,
I'm having an issue where when I attempt to export data from an Access database to an excel spreadsheet using VBA it truncates any field longer than 255 characters to the 255 limit. I'm using

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tableName", "FilePath", True, ""

to export it (obviously with the table name and filepath filled in) but for some reason I cannot get it to export the entirety of the field to excel. I've been doing some digging on various forums around the internet and it seems as though it may be possible to split it into various excel cells then use automation to concatenate the cells. But considering this field I am trying to get not truncated can be up to 40,000 characters theoretically, it doesn't make sense to do it that way. Can anyone help? Do you need more information from me? I'm somewhat new to both Access and VBA so any help would be greatly appreciated!
I should probably mention, I'm running the Office 2013 suite of applications. If that makes any difference, I'm not sure it does

 

[nbonzani]

Anyone have any idea how to remedy this?

 

[Rx_]

Yes, I have published a solution to this. My Excel Reports have one field in Excel 2010 that concat memo fields over 512 char with results that range from 50 to 6,000 characters.
The docmd is a built in function with the limitation.
The Recordset should be used.
To get started review:
http://www.access-programmers.co.uk/forums/showthread.php?t=210673&highlight=excel
Sometimes, a data conversion is needed, this will shed some light on that process:
http://www.access-programmers.co.uk/forums/showthread.php?t=248093&highlight=excel
And then maybe finish up with:
http://www.access-programmers.co.uk/forums/showthread.php?t=217051&highlight=excel

In my case, the ID_Well is a foreign key in a Comment table. All of the comments for a ID_Well are are sorted in some order. Then all of them are bunched up together into one massive comment, then pushed into an Excel column ranging from small to very large. e.g. a Summary of Comments

This code uses a sql query, then moves the data to a local table. The debug.print wasn't as useful for troubleshooting huge strings. By moving all the data into a local temp table, it was possible to see the results were OK befor moving it to Excel.
A local table "CommentReport" (with a field of type memo) is first cleaned out (line 630) then rebuilt on demand. Once populated, the Recordset fields (line 780) uses the ConcatComment function to move data to it.

eventually this is used to move the data to Excel.
860 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsCommentsReport

Sorry for the brief response, I have some huge task to complete this week so I can take an extended weekend to Vail.

540   strSQLComments = "SELECT Wells_Areas.Area, Wells.Well_Name AS [Well Name], Last(Wells_Status1.Status1) AS [Well Status], Last(Comments.[User ID]) AS [LastOfUser ID], Last(Wells.ID_Wells) AS ID_Wells "
550   strSQLComments = strSQLComments & " FROM ((States INNER JOIN Wells_Areas ON States.ID_State = Wells_Areas.ID_State) INNER JOIN (Wells_Status1 INNER JOIN Wells ON Wells_Status1.ID_WellStatus1 = Wells.ID_WellsStatus1) ON Wells_Areas.ID_Area = Wells.ID_Area) INNER JOIN Comments ON Wells.ID_Wells = Comments.ID_Wells "
560   strSQLComments = strSQLComments & " GROUP BY Wells_Areas.Area, Wells.Well_Name, Well_Name_Sorted([Well_Name]), Wells.ID_Area "
570   strSQLComments = strSQLComments & " HAVING (((Last(Wells.Activity))='A') AND ((Wells.ID_Area) " & ID_Area & ")) "
580   strSQLComments = strSQLComments & " ORDER BY Wells_Areas.Area, Well_Name_Sorted([Well_Name]), Last(Comments.Date) DESC; "
          
590       Debug.Print "strSQLComments = " & strSQLComments
600       'Call LogUsage("Comments Report", "Open Dynaset", "Line") ' Log success
610       ObjXL.Visible = False
          'Debug.Print " sql string =  " & strSQLComments   ' for test purposes
620         DoCmd.SetWarnings False
630         DoCmd.RunSQL "DELETE * FROM CommentReport"   ' delete all records in local CommentReport Table
640         DoEvents
650         Set rsComments = CurrentDb.OpenRecordset(strSQLComments, dbOpenSnapshot, dbReadOnly + dbSeeChanges) ' suggestion was this could be faster but it is not
660         Set rsCommentsReport = CurrentDb.OpenRecordset("CommentReport", 2, dbSeeChanges) ' open local table
670         intRowPos = 6      ' Sets starting Row for data in Excel - reference fields to this
680         DoEvents
690         ObjXL.DisplayAlerts = False                        ' Turn off Display Alerts
700   If Not rsComments.EOF And Not rsComments.BOF Then
710       rsComments.MoveFirst ' start at first record
720       rsCommentsReport.AddNew
          'rsCommentsReport.MoveFirst
      ' use intmaxRecordCount to populate local tableCommentReport
730       Do While Not rsComments.EOF
740                 rsCommentsReport.Fields("Area") = rsComments.Fields("Area").Value
750                 rsCommentsReport.Fields("Well Name") = rsComments.Fields("Well Name").Value
760                 rsCommentsReport.Fields("Well Status") = rsComments.Fields("Well Status").Value
770                 rsCommentsReport.Fields("User") = rsComments.Fields("LastOfUser ID").Value    ' last user
780                 rsCommentsReport.Fields("Comment") = ConcatComments(rsComments.Fields("ID_Wells").Value)
      
790    rsCommentsReport.Update
800    rsComments.MoveNext
810    rsCommentsReport.AddNew

820       Loop
830   End If
      Dim LocalTableName As String
840   LocalTableName = "CommentReport"
850   Set rsCommentsReport = CurrentDb.OpenRecordset(LocalTableName, dbOpenTable, dbSeeChanges)
        
860     ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsCommentsReport                ' old way   rsComments
870      intMaxRecordCount = rsComments.RecordCount - 1