Exporting Query to Excel from Custom Form

[Seanter]

I would like to be able to change the code below so that user has the option to pull same data for their employees by exporting the query into Excel. Right now they are able to pull the data for individual employees in a form. I've had requests to give the users the option to export the data to Excel.

Please note that I am very much a novice at programming; it took a VERY long time for me to come up with the following code, with a lot of help. I’ve read where the TransferSpreadsheet code might be a solution, but not sure.

Where it says

DoCmd.OpenForm "_frm_Scorecard_Mth_2012"
​

Would I change that to

DoCmd.OpenQuery "query_Name
​

Also, not sure where I would put the TransferSpreadsheet code

Would anyone be able to help me?

P.S. If possible, I would like to have the new Excel file formatted a certain way, so would imagine a template would need to be created?



My custom Switchboard (Excel buttons have been created and are waiting for code )




VB code for first button (2012: 1/21/12-present) to open form is as follows:

'------------------------------------------------------------
' cmdFirst6Mos12_Click
'
'------------------------------------------------------------
Private Sub cmdFirst6Mos12_Click()
On Error GoTo cmdFirst6Mos12_Click_Err

'------------------------------------------------------------
' error messages
'
'------------------------------------------------------------
If Forms!Switchboard!DeptCombo & "" = "" And [Forms]![Switchboard]![SupCombo] & "" = "" Then
MsgBox "Missing Dept and Supervisor"
Exit Sub
End If

If Forms!Switchboard!DeptCombo & "" = "" Then
MsgBox "Missing Dept"
Exit Sub
End If

If [Forms]![Switchboard]![SupCombo] & "" = "" Then
MsgBox "Missing Supervisor"
Exit Sub
End If
'------------------------------------------------------------
' END error messages
'
'------------------------------------------------------------

If (Forms!Switchboard!DeptCombo = "Tech Ops") Then
' Open Tech Ops Scorecard Metrics Form
DoCmd.OpenForm "_frm_Scorecard_Mth_2012", acNormal, "", "[_qry_Scorecard_Mth]![Supervisor] Like ""*"" & [Forms]![Switchboard]![SupCombo] & ""*"" And [_qry_Scorecard_Mth]![EmpName] Like ""*"" & [Forms]![Switchboard]![EmpCombo] & ""*"" And [_qry_Scorecard_Mth]![MthEndDate]>#1/1/2012#", , acNormal
End If
DoCmd.Close acForm, "Switchboard"

cmdFirst6Mos12_Click_Exit:
Exit Sub

cmdFirst6Mos12_Click_Err:
MsgBox Error$
Resume cmdFirst6Mos12_Click_Exit

End Sub

 

[MStef]

I suggest to do it via OUTPUTTO function.
Look at "DemoQueryExcelA2000.mdb (attachmnet, zip).
Open Form and try.
Adapt it as you need.

 

[Trevor G]

Welcome to the Forum,

Look to create a MACRO to work with either the OutputTo or TransferSpreadsheet once you have it working you can then convert the macro to code and you then have the code which you can combine with the other code as necessary.