Exporting Multiple Queries to 1 Excel Workbook with VBA

[Anthony]

Hi Everyone,

Does anyone know how to do this? I need to export a series of queries to 1
Excel workbook, with each query being a new tab. I have pretty extensive
DAO recordset knowledge, and the table which pulls the query names is being
generated on the fly based on what queries each individual user needs to
export.

I thought of doing a transferspreadsheet, but that seems to only make
accomodations for transferring tables, and the database is secured, but
allows users to design on-the-fly queries. The users' queries are simply
temp queries, created & deleted in each session, but the SQL is stored in a
(memo field in a ) table attached to their username. Hence, I can't change
all the queries to make table queries, as the users still need to be able to
create/modify queries.

Each worksheet also needs to have the query headers, as well.

Any help is greatly appreciated, as there are upcoming deadlines on this.

Thanks!!!!
-Anthony

 

[Matt]

I'm not sure on the security end of this, but...
If a query is saved, then you can export it to excel. Once
you export all of your queries, you can open the first
spreadsheet and begin moving the others into the same
workbook.

Not aware of an easier way.

 

[Anthony]

Matt,

Thanks for your response. I did find a way that I can loop through my
recordset & export to the same workbook using code. I did end up using
transferspreadsheet. It just figures I'd find it today after posting. I
looked for this solution for days!

Within a loop that goes through each query, call this function:

Private Function RunAndExport(strQueryName As String, strSQL As String,
strXLexport As String)
On Error GoTo RAE_Err

Set db = CurrentDb
If ObjectExists_rps("QUERIES", db, strQueryName) = -1 Then
DoCmd.DeleteObject acQuery, strQueryName
Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL)
Else
Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL)
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name,
strXLexport
DoCmd.DeleteObject acQuery, strQueryName

RAE_Exit:
Set qdf = Nothing
Set db = Nothing
Exit Function

RAE_Err:
MsgBox "There was an error." & vbCrLf & "Error Number " & Err.Number &
vbCrLf & "Error Description: " & _
Err.Description, vbExclamation, "Error"
Resume RAE_Exit

End Function