Access:- Execute Queries and export in single file

[sanket_sk]

Hi All,

I have small doubt in Access data base.

I have database in which I have setup 4 different queries.

I want functionality by which I can execute / refresh these queries and export all queries in single excel sheet.

Could you please help me performing this activity.

Sanket

 

[ranman256]

the last param is the tab name if you want different tabs:

vFile = "C:\mypath\files\" & format(date(),"yyyy-mm-dd" & "_ExportFile.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", vFile, True,"qry1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query2", vFile, True, "qry2"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query3", vFile, True, "qry3"

if you want ALL queries in 1 tab, then make a UNION query to put them all in 1 query, qnUnion:
select * from query1
union
select * from query2
union
select * from query3

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qnUnion", True,"unionqry"
BUT EVERY QUERY MUST HAVE THE EXACT SAME #FIELDS AND FIELD ORDER.