Solved

Export Memo Field from Access to Excel

Posted on 2010-08-29
7
902 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:RDLFC
  • 3
  • 2
  • 2
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33552768
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

        Next

        .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"

    Else

        ' 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

    xlApp.Quit

    Set xlApp = Nothing

    rs.Close

    Set rs = Nothing

 

    MsgBox "Done"

 

End Sub

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33552775
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
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 33568978
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

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23067605.html?#a20623596
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")

 xlObj.Workbooks.Add



 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

     Next

rs.MoveFirst

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

rs.MoveNext

Loop

End With



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



 Set Sheet = Nothing

 xlObj.Quit

 Set xlObj = Nothing

End Sub

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:RDLFC
ID: 33572670
capricorn1

How do I modify your code so the user is prompted to select a location to save the file.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33572962
what do you mean by prompted? are the user going to type in the path and file name? be specific.
0
 

Author Comment

by:RDLFC
ID: 33576860
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33576938
RDLFC,
what you are requesting is already another topic than what you originally requested.

see this link

http://www.mvps.org/access/api/api0001.htm
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now