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

Output a recordset to Excel File

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?

  • 2
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
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!
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.  
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

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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