Solved: Exporting a memo field from access to excel

[sammy8932]

I've tried everything I can to get this to work. When I export my data to excel, the data from the memo field is cut off at 1830. It was being cut off at 255. I believe this was because I was padding my data before exporting to excel. When I say padding, I mean taking out carriage returns. Well I'm now doing that before the data (which is entered on a form) is saved to the table. I've read everywhere that excel 2000 allows roughly 32,000 bytes in a cell. Why is my data being cut off at 1830? I would appreciate all the help I can get on this problem. It's very important I get this to work and as soon as possible. The code I'm using to export my data to excel is below.


[VBA]Private Sub cmdExportExcel_Click()
Dim rsExport As DAO.Recordset

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object

Dim recArray As Variant

Dim qdfQDef As QueryDef
Dim strQuery As String
Dim strExcelConn As String
Dim strFilePath As String
Dim intCount As Integer

Dim strMachineName As String
Dim strFileName As String
Dim strFTPFileName As String

Dim strMemo As String

'Requery form to save current record
Form.Requery

strFileName = GetExportFileName()

Dim fso As New FileSystemObject

strQuery = "[Get_Export_Records]"

strFilePath = "C:\Documents and Settings\mcdear\My Documents\Cont_Excel_Export\"

Set qdfQDef = CurrentDb().CreateQueryDef("", "SELECT * FROM " & strQuery)
Set rsExport = qdfQDef.OpenRecordset(dbOpenDynaset)


PSRcount = 0

Screen.MousePointer = vbHourglass
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
'Set xlWb = xlApp.Workbooks.Open("C:\Documents and Settings\mcdear\Desktop\PSR_Report2.xls")
Set xlWs = xlWb.Sheets("Sheet1")


' Check version of Excel
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset

For intCount = 0 To rsExport.Fields.Count - 1
xlWs.Cells(1, intCount + 1) = rsExport(intCount).NAME
Next

' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rsExport



xlWs.SaveAs strFilePath & strFileName
End If

' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit


'closing Recordsets and Connection
rsExport.Close
Set rsExport = Nothing

xlWb.Save
xlWb.Close
Set xlWb = Nothing
Set xlWs = Nothing
End Sub[/VBA]

[xCav8r]

Sorry, Sam, I've never tried this. I'm willing to expend some energy troubleshooting though, if you provide me with something with which I can work.

[sammy8932]

Thanks xCav8r. I would appreciate it. Let me know what you need and I'll do what I can to get it to you. Thanks again.

[xCav8r]

I meant just gimme whatever you're working with (minus sensitive data), and I'll give it a go from my end.

[xCav8r]

Sammy, I know from our conversation that this is resolved. When you have the time, would you mind explaining how it was fixed? Also, would you mark this thread as solved?

[sammy8932]

I ended up using the following and it exported everything from my memo fields to excel without any truncating.

[VBA]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQueryName, strFilePath & strFileName
[/VBA]

Thanks xCav for your help.

[xCav8r]

Thanks for the credit for something you solved on your own!!!