Error Exporting Memo Fileds to Excle

[Betti]

Hi,

I have used a posted code from
http://www.mvps.org/access/modules/mdl0035.htm to create a recordset to
export to Excel range but I have couple of memo fields that are truncating
down to 255 when exporting. I have researched the issue but can't figure out
how to address it. Any help would be very much appreciate it.

Sub sCopyRSToNamedRangeProcesses()
'Copy records to a named range
'on an existing worksheet on a
'workbook
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
'Dim rs As Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Processes"
Const conWKB_NAME = "c:\temp\Data_Load_Template.xls"
Const conRANGE = "A11:X1999"
Dim rec As New ADODB.Recordset

Set db = CurrentDb
Set objXL = New Excel.Application
'Set rs = db.OpenRecordset("Processes", dbOpenSnapshot)
'Set rec = db.OpenRecordset("Processes", dbOpenSnapshot)

rec.Open "SELECT * FROM Processes", _
CurrentProject.Connection, _
adOpenKeyset, _
adLockOptimistic


With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
'objSht.Range(conRANGE).CopyFromRecordset rs
objSht.Range(conRANGE).CopyFromRecordset rec
If Not rec.EOF Then rec.MoveLast: rec.MoveFirst
Debug.Print rec.RecordCount
End With

objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
'************* Code End *****************

 

[ryguy7272]

Here's something to get you started:
http://bytes.com/topic/access/answers/773673-export-excel-truncates-255-chars

Few more ideas here; bottom of page:
http://allenbrowne.com/ser-63.html


The only 100% solution I know is to export the table to a Word file (e.g.
..rtf) to get all data visible without truncation.

 

[Betti via AccessMonster.com]

Thank you very much, I will try both and see if they work.

Betti

Here's something to get you started:
http://bytes.com/topic/access/answers/773673-export-excel-truncates-255-chars

Few more ideas here; bottom of page:
http://allenbrowne.com/ser-63.html

The only 100% solution I know is to export the table to a Word file (e.g.
.rtf) to get all data visible without truncation.

Hi,

[quoted text clipped - 58 lines]

'************* Code End *****************
.