<div>
And if .CopyFromRecordset doesn't play, then its CBAC. 
CBAC (Cell By Agonizing Cell) 
 
The guts of that are similar to this header row code 
For i = 1 To iNumCols 
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name 
Next 
 
Except you'd have an additional counter and loop 
 
dim r as integer 
r = 2 
do until rs.eof 
&nbsp; &nbsp; For i = 1 To iNumCols 
&nbsp; &nbsp; &nbsp; &nbsp; oSheet.Cells(r, i).Value = rs.Fields(i - r).Value 
&nbsp; &nbsp; Next 
&nbsp; &nbsp; r = r +1 
&nbsp; &nbsp; rs.movenext 
Loop 
 
It can be fun to code this and throw 
oApp.Visible = True 
oApp.UserControl = True 
in really early and you can watch Excel being filled in on the fly
</div>

<div>
@Anders. 
Max char limit was 32K. 
Max display in a cell was 255 
Max display in the address bar was 1024. 
 
It's not that you can't get 32000+ chars in a cell, it't that you can't see or print them😟
</div>

<div>
@Nick 
I have not tried how many chars I can get into a cell in total. 
I usually don't look in the address bar, so can't talk about that 
 
In 2010 I have just now inserted 5500 chars into a cell. They do display in the cell, uptil the max height of 409,5. Merging that cell with the cells below, allow me to extend the total height. I can now view and print all chars in that cell. Previous versions of excel were much less forgiving.
</div>

<div>
So after thinking about this and doing a little more research, it looks like I can export a TABLE (instead of a query) using COPY and PASTE SPECIAL as TEXT and I don't lose any of the data. &nbsp; &nbsp; 
 
Now my questions is 'Can I do a MAKE TABLE query and then export that table and paste special using access?' &nbsp; 
In other words, I don't want to have to physically do the copy and paste....but this will be a solution that would be the easiest (at this point). 
 
Any thoughts? 
 
LQ
</div>

<div>
@Anders, its the filetype rather than Excel version that has some of the different limitations. 
.xls has the limitations I've noted 
.xlsx has the greater flexibility you've seen. 
For giggles I have built a 32K string and rammed it into a cell in Excel 2003 / .xls 
You can't SEE most of the text, but it is there to get after with VBA. 
 
I don't know what happens when you open a .xlsx on Excel 2003- 
But then, Excel really isn't the thing for massive text blocks, anyway. 
 
 it looks like I can export a TABLE (instead of a query) using COPY and PASTE SPECIAL as TEXT and I don't lose any of the data. 
 
Most of the Saved Import/Export specification automations are likely going to encounter the same truncation limitations you are already seeing. &nbsp;I don't run O2007+ but I support those versions and have access to them. &nbsp;There are many questions on EE about automating the running of those specs -- because they are a bear to poke with VBA. 
 
Have you tried .CopyFromRecordset? 
Did It fail? 
 
Attached is a sample that gets it done without truncation 
<a href="https://filedb.experts-exchange.com/incoming/2016/02_w06/1079615/BigTextExport.mdb" target="_blank" class="file-inline" title="test sample (212 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>BigTextExport.mdb</a>
</div>

BigTextExport.mdb

<div>
Nick67 - 
Thank you for your comments and information. &nbsp; I have not tried '.CopyFromRecordset' but what I did was to create a 'make table' query to store the results (which doesn't truncate). &nbsp;I will have the user copy and 'paste special / text' into excel and that seems to work without any problems. &nbsp; The key is to use the PASTE SPECIAL and then choose TEXT. 
I'm going to look at CopyFromRecordset at some point to see if I can get that to work. &nbsp;I have another report from this same database that I will try this option. &nbsp; If it works, I'll convert the other since it will give me the flexibility to add titles and make the output more 'ready' to use when exported. 
 
Thank you. 
LQ
</div>

<div>
here is some code using CopyFromRecordset that was posted by Nate Oliver, whose code lives on ... 
<pre><code class="code-100 nolinks" id="code-20-41449397-1">Sub MakePullLists(pPath As String, pQname As String)
 
 On Error GoTo Proc_Err
 
 ' originally posted by NateO
 ' modified slightly by crystal
 
 'Declare your ADO Recordset
 Dim rs As ADODB.Recordset
 
 'Excel Objects
' Dim xlApp As Excel.Application 'early binding for dveloping
' Dim xlWb As Excel.Workbook
 Dim xlApp As Object 'late binding for distribution
 Dim xlWb As Object
 
 'Field Names - Stack into Array
 Dim fldArr() As String
 
 'Need some loop counters
 Dim j As Long _
 , iQ As Long _
 , i As Long
 
 Dim sFilename As String
 sFilename = pPath &amp; pQname
 
 i = 1
 
 'OLE - Create xl Objects
 Set xlApp = New Excel.Application
 
 'Add a new Workbook, with one Worksheet, to our Excel Application
 Set xlWb = xlApp.Workbooks.Add(1)
 
 'this is commented out because to show you can loop if you want
 'For i = LBound(sqlArr) To UBound(sqlArr)
 
 'New ADO Recordset
 Set rs = New ADODB.Recordset
 
 'Open the Recordset, Passing the Sql from our Array
 rs.Open CurrentDb.QueryDefs(pQname).SQL, CodeProject.Connection, _
 adOpenStatic, adLockReadOnly
 
 With rs
 'Stack a String Array with the Field Names
 ReDim fldArr(0 To .Fields.Count - 1)
 
 For j = LBound(fldArr) To UBound(fldArr)
 Let fldArr(j) = .Fields(j).Name
 Next j
 
 'Time to Pass some Data to Excel!
 With xlWb.Worksheets
 'Add a Worksheet if we're at 2nd Recordset or Greater
 If i &gt; 1 Then .Add After:=.Item(i - 1)
 'Refer to the Worksheet by Item Number
 'in the Collection of Worksheets (1-based)
 With .Item(1)
 'Pass our dynamic Field String Array to A1,
 'stretched to the Right for number of Elements
 Let .Range(&quot;a1&quot;).Resize(, UBound(fldArr) + 1).Value = fldArr
 
 'Copy our Current Recordset to A2
 .Range(&quot;a2&quot;).CopyFromRecordset rs
 
 'Rename Individual Worksheet
 .Name = &quot;WorksheetName&quot;
 
 'however many columns of data you have, if desired
 .Columns(&quot;A:G&quot;).EntireColumn.AutoFit
 
 End With
 End With
 End With
 
 'Moving on, no need to close or terminate our RS, yet,
 ' we're going to recycle in the Loop
 
 'end of optional loop
 'Next
 
 'Make Excel visible - (Otherwise Save and Close)
 With xlApp
 .Goto xlWb.Worksheets(1).Range(&quot;A1&quot;)
 'commented but can be activated if desired
 'xlSht.Cells(1, 1).Activate
 '.Visible = True
 End With
 
Save_Workbook:
 
 'delete file if it already exists
 If Dir(sFilename) &lt;&gt; &quot;&quot; Then
 On Error Resume Next
 Kill sFilename
 DoEvents
 On Error GoTo Proc_Err
 End If
 
 'commented because there was not code to disable in this case
 'if you are writing to a template with code, you may want
 'to disable events in the beginning
 'xlApp.EnableEvents True
 
 xlApp.ActiveWorkbook.SaveAs sFilename
 xlApp.ActiveWorkbook.Close False

Proc_Exit:
 On Error Resume Next
 
 'Terminate our Excel Object Variables
 Set xlWb = Nothing
 If TypeName(xlApp) &lt;&gt; &quot;Nothing&quot; Then
 xlApp.Quit
 Set xlApp = Nothing
 End If
 
 'Now close and terminate the ADO Recordset, we're all done!!
 rs.Close: Set rs = Nothing

 Exit Sub
 
Proc_Err:
 MsgBox Err.Description, , &quot;ERROR &quot; &amp; Err.Number &amp; &quot; MakePullLists&quot;
 Resume Proc_Exit
 'press Ctrl-Break to stop code at Msgbox
 'set this to be the next statement then F8 to step through code and debug
 Resume

End Sub
</code></pre>
 Nate's code uses NEW to create the Excel object which means you need early binding to do it that way. &nbsp;If you want to use late binding, the objects can be set differently.
</div>

<div>
another example that uses CopyFromRecordset to write to Excel from Access, and also does a lot of formatting, is here: 
 
Download -- Document Calculated Fields in Queries, write results to Excel and format 
<a href="http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html" rel="ugc">http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html</a> 
 
The code is this example is liberally commented to help you learn. 
 
video: Document Query Calculated Fields: Access to Excel (cc) 
<a href="https://www.youtube.com/watch?v=vS8KfHU6L90" rel="ugc">https://www.youtube.com/watch?v=vS8KfHU6L90</a> 
&nbsp;- SQL to show calculated fields in queries, use CopyFromRecordset to put in Excel and then format
</div>

<div>
create a 'make table' query 
I would caution you about 'make table' 
 
It has been my experience that Access doesn't really care for make/delete, make/delete rinse-and-repeat of tables. 
Eventually corruption and its heartache sets in. 
Avoid that. 
Make the table once, and then a Delete * from TheNewTable and an append query is really the way to go if that's what you choose to pursue.
</div>

<div>
&gt; &quot;The key is to use the PASTE SPECIAL and then choose TEXT.&quot; 
 
good tip, Linda ~ 
 
in the link I gave you for RogersAccessLibrary (Document Calculated Fields in Queries, write results to Excel and format) ... somewhere I have a version of that code that takes a query name and is more generic. 
 
I agree with Nick about not making a table (or if you do, make it in a temporary database and link to it). &nbsp;If you can use a query to get what you want, you can use that query to write to Excel too ~
</div>

<div>
Nick67 - &nbsp; The problem with an APPEND query is that it also truncates the data which is really odd. &nbsp; I may be forced into trying the CopyFromRecordset as I was not aware of the issues with 'Make Table' queries. &nbsp; I have used them in several databases without issue, but if I can avoid a potential hazard, I will. &nbsp; 
 
Thank you for the suggestion. 
LQ
</div>

<div>
Way at the beginning it was cited 
<a href="http://allenbrowne.com/ser-63.html" rel="ugc">http://allenbrowne.com/ser-63.html</a> 
 
There are ways to avoid truncation of memo fields -- but it does require some deliberate planning. 
Generally, you do one operation to get the non-problem fields sorted, and then a second with the bare minimum needed along with the memo fields. 
 
So, if you had a big ugly query with 9 tables and eight joins and three where items, you'd leave the memo field out. 
Then you'd construct a second query with the PK and memo field joined to the result of the first ugliness. 
 
That generally works. 
Have you tested my sample? 
It works.
</div>

<div>
I did not try any of the VBA solutions suggested. &nbsp; I ended up just highlighting the whole table and doing a COPY, then opening Excel and doing a PASTE SPECIAL and then selecting TEXT. &nbsp; this works very nicely and prevented any need for VBA at this time.
</div>

<div>
you should have tried the post at <a href="http:#a41448005" rel="ugc">http:#a41448005</a>&nbsp; it works.
</div>

<div>
Thanks Rey Obrero. &nbsp; I may try it in the future. 
I appreciate the follow-up. 
 
lq
</div>

<div>
<div class="content wysiwyg-content">
I have been searching the 'WWW' over trying to find out how to fix a problem with exporting a query or report in Access 2013 to Excel without truncating data. &nbsp; I have a 'long text' field that truncates to 255 characters when the data is exported to Excel. &nbsp;Frustrating and surprising this happens. 
Sadly, many of the report users require the format to be in Excel -- otherwise a PDF version looks terrific. 
 
I need to be able to export the query or report without loosing the characters in the 'long text' field. &nbsp;Any ideas on how to fix this frustrating problem? 
 
Thank you &nbsp;- lq
</div>
</div>

I have been searching the 'WWW' over trying to find out how to fix a problem with exporting a query or report in Access 2013 to Excel without truncating data.   I have a 'long text' field that truncates to 255 characters when the data is exported to Excel.  Frustrating and surprising this happens.
Sadly, many of the report users require the format to be in Excel -- otherwise a PDF version looks terrific.

I need to be able to export the query or report without loosing the characters in the 'long text' field.  Any ideas on how to fix this frustrating problem?

Thank you  - lq

Access 2013, how do I 'export' a report to EXCEL without the 'long text' field  getting trucated at 255 characters?

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.