There is a VERY easy way to export a saved MS Access query (via VBA) to an Excel file. Basically, one line of code:
DoCmd.OutputTo acOutputQuery, "query name", acFormatXLS, "outputfilename", 0
This works great if you have a saved query. But I need to export a recordset, not a saved query. I keep thinking that there must be a simply way to do this (just like exporting a saved query). But I cannot seem to find anything on-line and the few things I've tried don't seem to work.
In the sample below, I create a recordset subset of records from a saved query. I've simplified the syntax, but I know that it's working because the msgbox returns a count of 50 records. So, the recordset has data.
Next, I tried using the basic query export command, replacing the query reference with the recordset name. But that doesn't seem to work. I get a message indicating "The expression you entered is the wrong data type for one of the arguments
Dim RS As DAO.Recordset
Dim sSQL As String
sSQL = "Select * from queryA where fieldX = 100"
Set RS = CurrentDb.OpenRecordset(sSQL)
RecCount = RS.RecordCount
If RecCount > 0 Then
DoCmd.OutputTo acOutputQuery, RS, acFormatXLS, MyPathFileName, 0
Is there a way to export a recordset like this? The other option would be to just save the query to the DB and export that saved query. Seems like there should be a way to do this.