I want to export multiple queries into an excel file, each query on a different sheet

[offie]

So let me explain what I wan the end product to be.
I have a form with text boxes FiscalYearFrom and FiscalYearTo, and checkboxes with the names of different queries.
The textboxes set the criteria for the queries.
What I want is that when I click on a command button the quries selected will be exported to a file, a query on each sheet.
Eventaully I want to go in and add code to make a chart and format it from the exported queries but I'm not quite there yet.



I know how to work with the checkboxes, what I'm having trouble with is the exporting.

I found this http://www.dbforums.com/microsoft-ac...xcel-tabs.html very helpful but it doesn't include how to move the data.
Here is the code from that example:

Option Compare Database 'Use database order for string comparisons    
Dim MyExcel As Object 'This is the excel object    
Dim MyBook As Object    
Dim MySheet As Object    

'This uses the DAO method of opening a query data sourceSub My_Excel_Book()

On Error GoTo My_Excel_Err  'Comment this line out if you need to debug in VB editor        

Dim Database As DAO.Database   
Dim MyRecSet As DAO.Recordset    
Dim Filename As String        

Dim BarCnt As Long, BarVal As Variant    'Variables for the progess bar in the task bar area       

'Set up the database source data. This example is query source    
Set Database = CurrentDb    
Set MyRecSet = Database.OpenRecordset("Acad_Union_Query", dbOpenDynaset)       

 'Set up the Excel objects    
Set MyExcel = CreateObject("Excel.Application")    
MyExcel.Visible = False    
Set MyBook = MyExcel.Workbooks.Add    
Set MySheet = MyBook.Worksheets.Add            'Add first new worksheet to book    
MySheet.Move After:=MyBook.Sheets(MyBook.Sheets.Count)    'Move worksheet to last sheet position        

MsgBox "This will Copy " & MyRecSet.RecordCount & " records" & vbCr & "into an Excel file in C:\temp\..."        

'Set the first sheet name    
MySheet.Name = MyRecSet![My_Field_Name] 'Set the name of the first sheet        

'Set up and initialize the optional progress bar    
BarCnt = MyRecSet.RecordCount    
BarVal = SysCmd(acSysCmdInitMeter, "Building New Excel Sheet...", BarCnt)   
BarCnt = 1        

Do While Not MyRecSet.EOF              
'Your code goes here that moves data from the access source to the excel sheet                                

'Now we start a new sheet, name and move it into position        
Set MySheet = MyBook.Worksheets.Add        
MySheet.Move After:=MyBook.Sheets(MyBook.Sheets.Count)        
MySheet.Name = MyRecSet![My_Field_Name] 'Set the name of the new sheet                

'This is optional if you want a progress bar...            
BarVal = SysCmd(acSysCmdUpdateMeter, BarCnt) 'Update the optional progress bar               

 BarCnt = BarCnt + 1   'Increment the optional progress Bar Count    
Loop    

My_Excel_Cleanup:    
'Save the Excel file and do some clean-up prior to exit    
Filename = "C:\temp\My_Excel " & Format(Now(), "mm-dd-yy hh-mm-ss") & ".xls"    
MyBook.SaveAs (Filename)    
MyBook.Close    
MyExcel.Quit    
Set MySheet = Nothing    
Set MyBook = Nothing    
Set MyExcel = Nothing    
MyRecSet.Close    
Set MyRecSet = Nothing    
MsgBox "File saved to:" & vbCr & vbCr & Filename    'vbCr is a visual basic carriage return    
BarVal = SysCmd(acSysCmdRemoveMeter) 'Remove the optional progress meter   
Exit Sub    

My_Excel_Err:    
MsgBox Error$    
Resume My_Excel_Cleanup    
End Sub

I tried to use DoCmd.TransferSpreadsheet but I can't seem to get the right format. The FieldName and Range inputs confuse me. I had seen an example like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExportMetrics", "c:\test\output.xls", , "Worksheet1$"

But using this format gives me an error.

Any Ideas?
Thanks!

[offie]

So I found http://www.accessmvp.com/KDSnell/EXCEL_Export.htm really useful and ended up with

Private Sub Command60_Click()
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Set dbs = CurrentDb

Dim strSQL As String, strQDF As String
strSQL = "SELECT ConstructionSurveyFYSearchGraphData.* FROM ConstructionSurveyFYSearchGraphData;"
      
strQDF = "CnstSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

If Forms!ChooseGraphs![Checkcnst] = True Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF, "C:\Users\Kyles\Desktop\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls"
dbs.QueryDefs.Delete strQDF
End If

'Start Here, Makes Sure that File Name stays the same.

If Forms!ChooseGraphs![Check100] = True Then

Dim strSQL2 As String, strQDF2 As String
strSQL2 = "SELECT 100SurveySearchGraphData.* FROM 100SurveySearchGraphData;"
      
strQDF2 = "100PctSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF2, strSQL2)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF2, "C:\Users\Kyles\Desktop\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls"
dbs.QueryDefs.Delete strQDF2

End If
'End Here

dbs.Close

'Edit Excel
Set xl = CreateObject("Excel.Application")
   xl.Workbooks.Open ("C:\Users\Kyles\Desktop\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls")
    
xl.Visible = True

Set dbs = Nothing

End Sub

Which works perfect, now I just have to figure out how to edit the data in excel