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:
Code:
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:
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExportMetrics", "c:\test\output.xls", , "Worksheet1$"
But using this format gives me an error.
Any Ideas?
Thanks!