Access:- Execute Queries and export in single file

sanket_sk

New Member
Joined
Dec 27, 2016
Messages
45
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,897
the last param is the tab name if you want different tabs:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,561
Messages
5,487,563
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top