Export 2 Subforms to separate tabs in Excel

[MissaLissa]

I'm hoping someone can help me with this issue... or direct me in a more appropriate way to accomplish this.

I'm able to export 1 subform using a macro, but I'm unable to export 2 subforms to separate tabs using the same method.

Here is what I developed for the 1 subform export:

When you click on the View Register button, the delete query deletes all records in the CalPERS Register table, then appends records based on the start and end dates that were chosen on the main form. the subofrm then shows those records. I'm then able to create a simple ExportWithFormatting macro to export those records to excel.

Private Sub ViewRegister_Click()
DoCmd.SetWarnings False
 DoCmd.OpenQuery "qDel_CalPERS Register"
 DoCmd.OpenQuery "qApp_CalPERS Register"
 DoCmd.SetWarnings True
DoCmd.Requery ("tbl_CalPERS Register subform")
End Sub

I want to be able to do this same thing with 2 subforms. The difference between the 2 subforms is the client number. The subforms use the same table. I want to be table to export the records to the same excel spreadsheet but keep the 2 subforms on separate tabs.

Private Sub ViewRegister_Click()
DoCmd.SetWarnings False
 DoCmd.OpenQuery "qDel_Genworth Register"
 DoCmd.OpenQuery "qApp_Genworth Register"
 DoCmd.SetWarnings True
DoCmd.Requery ("tbl_Genworth_054 Register subform")
DoCmd.Requery ("tbl_Genworth_128 Register subform")
End Sub

Is this possible?

Thanks in advance for the help and/or suggestions.

 

[ranman256]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData1", vFile, True,tab1
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData2", vFile, True,tab2