Output a recordset to Excel File

Posted on 2005-04-15
Last Modified: 2008-03-06
I've got a Recordset that I need to output to an xls file.  Is there an easy way to do this in VBA?  Can doCmd.OutputTo do this?

Question by:fbk2000
    LVL 65

    Expert Comment

    by:Jim Horn
    Here's the template...

    Dim xl As New Excel.Application
    Dim xlw As Excel.Workbook

    xl.Workbooks.Open ("Your Excel Spreadsheet")

    rs.Open "Whatever", , adOpenStatic, adLockReadOnly

    xl.ActiveSheet.Range("Some Named Range, or top left of range like A1").CopyFromRecordset rs

    'Save and quit the Excel file.
    For Each xlw In xl.Workbooks

    'Close the recordset object.

    Hope this helps.
    LVL 4

    Expert Comment

    No, you can't use the docmd.OutputTo method directly, because using this method you're tied to tables and stored query's.
    However, you can easely create or change a querydef using the Source property of a Recordset object to work around this limitation. For example:

    '// in this case I changed a stored query
    CurrentDb.QueryDefs("qryTemp").SQL = rs.Source

    docmd.OutputTo _  
     ObjectType:=acOutputQuery,ObjectName:="qryTemp", _
     OutputFormat:=acFormatXLS,OutputFile:="C:\TEMP\TEST.XLS, _

    Good luck!
    LVL 65

    Accepted Solution

    Actually, the one-line, real easy way is...

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your Table or Query Here", "Your Excel Spreadsheet Here.xls", FieldNames True or False

    My (really long) example above does the same thing, but allows you to specify an exact range of cells.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    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…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    759 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

    8 Experts available now in Live!

    Get 1:1 Help Now