CODEWITHSUNDEEP
vbams-accessms-access-2010
Alec
Alec

Reputation: 11

Export two Access Queries to one excel file onto their own sheets

I have two access Queries that I want to export to the same workbook but on different sheets. Query1 and Query2 need to be put onto an excel file that ill name "QueryOverall", and on separate sheets. As of now it only lets me export one query to one excel file.

Upvotes: 0

Views: 8705

Answers (1)

Jaskier
Jaskier

Reputation: 1095

Try:

DoCmd.TransferSpreadSheet acExport, acSpreadSheetTypeExcel12Xml, "Query1", "C:\Users\me\Desktop\Email\QueryOverall.xlsx", True
DoCmd.TransferSpreadSheet acExport, acSpreadSheetTypeExcel12Xml, "Query2", "C:\Users\me\Desktop\Email\QueryOverall.xlsx", True

Where strFilePath is the path to the file you're wanting. This will create two Worksheets within the same Workbook, one for each query.

Edit:

Example of use:

Sub go()
    Dim strFilePath as String
    strFilePath = "C:\Users\me\Desktop\Email\"

    DoCmd.TransferSpreadSheet acExport, acSpreadSheetTypeExcel12Xml, "Query1", strFilePath + "QueryOverall.xlsx", True
    DoCmd.TransferSpreadSheet acExport, acSpreadSheetTypeExcel12Xml, "Query2", strFilePath + "QueryOverall.xlsx", True

End sub

You can also manually export them: Right-click on query > export > choose where > export to same file

Upvotes: 1

Related Questions