• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • 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?

Thanks
0
fbk2000
Asked:
fbk2000
  • 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
    xlw.Save
Next
xl.Quit

'Close the recordset object.
rs.close

Hope this helps.
-Jim
0
 
zuijdhoekCommented:
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, _
 AutoStart:=False

Good luck!
0
 
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.  
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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