Export multiple access query to same workbook two sheets

[mr moe]

hello, i'm currently using this code to export an access query to excel, the query name is output_qry, it exports it to sheet1 and opens excel automatically.
i have another access query that I want to export to the same workbook as sheet2. how can i do it. here is the code below that works for one query to sheet1.


Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'Step 2: Identify the database and query
Set MyDatabase = CurrentDb
Set MyQueryDef = MyDatabase.QueryDefs("output_qry")
'Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 4: Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.workbooks.Add
.Sheets("Sheet1").Select
'Step 5: Copy the recordset to Excel
.ActiveSheet.Range("A2").CopyFromRecordset MyRecordset
'Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit


End With

 

[Gasman]

Repeat the steps that sets the recordset, selects the sheet and copyfromrecordset.

 

[mr moe]

Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'Step 2: Identify the database and query
Set MyDatabase = CurrentDb
Set MyQueryDef = MyDatabase.QueryDefs("output_qry")
'Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 4: Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.workbooks.Add
.Sheets("Sheet1").Select
'Step 5: Copy the recordset to Excel
.ActiveSheet.Range("A2").CopyFromRecordset MyRecordset
'Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit


End With

 

[mr moe]

this codes works for one query to be exported to one sheet in excel, Im trying to export anther query as sheet2 in the same excel file. Any help?

 

[theDBguy]

this codes works for one query to be exported to one sheet in excel, Im trying to export anther query as sheet2 in the same excel file. Any help?

Are all of that code necessary. Wouldn't TransferSpreadsheet do the same thing? Just curious...

 

[Gasman]

this codes works for one query to be exported to one sheet in excel, Im trying to export anther query as sheet2 in the same excel file. Any help?

Well the simplest method would be to repeat the steps?, that is why I suggested that?
theDBguy has suggested another option. I was just concentrating on what you were used to, and what you asked for.

 

[mr moe]

well Transferspreadsheet you would have to save the file into a directory before it opens and this is what i'm trying to avoid. the line above works perfectly but only for one query to one workbook as sheet1. I"m trying to have two queries into the same workbook as sheet1 and sheet2 using same code but adding sheet2. thanks.

 

[CarlettoFed]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", CurrentProject.Path & "\FileName.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query2", CurrentProject.Path & "\FileName.xls", True

and it will create the excel file "FileName.xls", with two sheets inside that will have the same name as the queries, in the same folder where the access file from which you are executing the code is located.

 

[mr moe]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", CurrentProject.Path & "\FileName.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query2", CurrentProject.Path & "\FileName.xls", True

and it will create the excel file "FileName.xls", with two sheets inside that will have the same name as the queries, in the same folder where the access file from which you are executing the code is located.

Thanks. But i'm trying to open the query in excel without saving it. thanks for the help!

 

[Pat Hartman]

It is hard for use to see the point of that but if you don't want to use TransferSpreadsheet, then you need to manually create the second tab and load it yourself. I don't know how to do that since Excel is not my application of choice. But, when I want to figure out the VBA I need to automate Excel, I open Excel, turn on the macro recorder, perform the action, then turn of the recorder and examine the code it created and figure out how to convert it to Access VBA.