• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1010
  • Last Modified:

Export Memo Field from Access to Excel

When I export a set of memo fields from access to excel the memo fields are being truncated in Excel.  What is a simple way to export memo fields to excel without loosing informaiton?
  • 3
  • 2
  • 2
1 Solution
Patrick MatthewsCommented:
RDLFC,If you use OutputTo or TransferSpreadsheet, Memo columns will truncate at ~255 characters.  Instead you may want to try Excel's CopyFromRecordset method.  See below.Patrick
Sub ExportToExcel()
    ' uses late binding for Excel
    Dim rs AS DAO.Recordset
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object
    Dim Counter As Long
    Const SaveToPath As String = "c:\Results\Report_"
    Const QueryName As String = "NameOfQuery"
    Set rs = CurrentDb.OpenRecordset(QueryName)
    ' instantiate Excel object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.DisplayAlerts = False
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(1)
    With xlWs
        ' write recordset headings
        For Counter = 0 To rs.Fields.Count - 1
            .Cells(1, Counter + 1) = rs.Fields(Counter).Name
        .Cells(2, 1).CopyFromRecordset rs
    End With
    ' Excel 2007/2010 requires the file format to be specified, so check
    ' for application version.  see for more info:
    ' http://www.dailydoseofexcel.com/archives/2006/10/29/saveas-in-excel-2007/
    If Val(xlApp.Version) < 12 Then
        xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xls"
        ' to use XLSX format:
        xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsx", 51
        ' or to use XLSM format:
        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsm", 52
        ' or to use XLSB format:
        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsb", 50
        ' or to use good old XLS format:
        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xls", 56
    End If
    xlWb.Close False
    xlApp.DisplayAlerts = True
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
    Set rs = Nothing
    MsgBox "Done"
End Sub

Open in new window

Patrick MatthewsCommented:
Please note that for Excel 2003, if you have a Memo column with >= 911 characters, CopyFromRecordset will generate an error.  This seems to be a version-specific error.If you are running Excel 2003, you may want to look at this recent solution:http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26432434.html
Rey Obrero (Capricorn1)Commented:
here is how you can pass all the memo field without truncating to 255

this just a sample code, you have to replace some names of fields here

from this thread

Sub exportToExcel()
Dim rs As DAO.Recordset, iCol As Integer, iRow As Integer
Dim xlObj As Object, Sheet As Object

 Set xlObj = CreateObject("Excel.Application")

 Set rs = CurrentDb.OpenRecordset("table1")

 Set Sheet = xlObj.activeworkbook.worksheets("sheet1")
 'copy the headers
     For iCol = 0 To rs.Fields.Count - 1
         Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
With Sheet
iRow = 2
Do Until rs.EOF
    .cells(iRow, 1).Value = rs("Report Date")
    .cells(iRow, 2).Value = rs("Assessment ID")
    .cells(iRow, 3).Value = rs("SPM Proj Name")
    .cells(iRow, 4).Value = rs("TP Name")
    .cells(iRow, 5).Value = rs("TP Mgr")
    .cells(iRow, 6).Value = rs("Delegate")
    .cells(iRow, 7).Value = CStr(rs("Activity"))
    .cells(iRow, 8).Value = CStr(rs("Task Requirement"))
    .cells(iRow, 9).Value = rs("Issue Number")
    .cells(iRow, 10).Value = CStr(rs("Issues"))
    .cells(iRow, 11).Value = CStr(rs("OFM Comments"))
    .cells(iRow, 12).Value = rs("Issue Level")
    .cells(iRow, 13).Value = rs("Remediation Due Date")
    .cells(iRow, 14).Value = rs("Assessor")
    .cells(iRow, 15).Value = rs("Issue Status")
    .cells(iRow, 16).Value = CStr(rs("TP Mgr Comments"))
    iRow = iRow + 1
End With

 xlObj.activeworkbook.saveas "C:\MyExcel.xls"

 Set Sheet = Nothing
 Set xlObj = Nothing
End Sub

Open in new window

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

RDLFCAuthor Commented:

How do I modify your code so the user is prompted to select a location to save the file.
Rey Obrero (Capricorn1)Commented:
what do you mean by prompted? are the user going to type in the path and file name? be specific.
RDLFCAuthor Commented:
When the user selects the button to export, I want a save as box to pop up so the user can specify where the excel document is to be exported and saved.
Rey Obrero (Capricorn1)Commented:
what you are requesting is already another topic than what you originally requested.

see this link

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now