Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export Memo Field from Access to Excel

Posted on 2010-08-29
7
Medium Priority
?
1,002 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 93

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 93

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 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