I am trying to move the results from an Access query to Excel. I have no problem doing this with table's but am running into some brick walls trying to do the same thing with a query instead of a table. Below is the code I am use to move the data in my table to Excel, not sure how much of it I would need to change to move my query data over instead. Once working correctly I also need to be able to change the query I am moveing based upon a selection from the user form.
Private Sub cmdRptExcel_Click()
On Error GoTo Err_cmdReport_Click
Dim txtReportType As String
Dim rst As ADODB.Recordset
Dim qdf As QueryDef
Dim XlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim TabName As String
Dim DateRange As String
Dim dbDataSet As String
Dim I As Integer
Set XlApp = New Excel.Application
Set XlBook = XlApp.Workbooks.Add
strReportID = Me.lstReports
If IsNull(strReportID) Then
MsgBox "Please select a report"
I was doing it that way, export with docmd and then run an excel macro to format the data and save it to a shared network drive. I want to be able to do all of that from Access though now, hit one button export the query to excel call my formatting macro from excel and then save it to my shared drive. I already do this with a table and have no problems, it is the moving of the query into Excel from Access with out the DoCmd I have problems with.
If you already have success doing what you want with tables, you could materialize your query into a table using a make table query. Then, once the export is complete, drop the table from your database.