Command Button to export to excel in 2007

[DarkOne72]

Hi...I have a question hopefully someone can resolve:

Is there a way to create a command button on a form to export query results to excel automatically in Access 2007?

I know it is probably simple but my current db in 2003 works fine and if I open it with 2007 the export feature dosn't work.

Thanks in advance

 

[Remou]

What is the line that works in 2003 but not in 2007?

 

[DarkOne72]

I have it on the On Click with an event procedure, this procedure makes a popup appear saying it may take some time to run and then has an hourglass show until everything is complete. Here is the code:


Private Sub Command18_Click()
Dim i As Double
Dim ReturnValue As Variant
Dim maxvalue As Double
Dim stepp As Double
Dim X As Variant
Dim Total As Double

maxvalue = 700000
stepp = 100 / maxvalue

ReturnValue = SysCmd(SYSCMD_INITMETER, "Building query", 100)

Response = MsgBox("This may take some time." _
& Chr(10) & Chr(13) & _
"Push OK to continue, and Cancel to quit", 1)
If Response = 1 Then
DoCmd.Hourglass True
For i = 1 To maxvalue
Total = Total + stepp
X = Int(Total)
ReturnValue = SysCmd(SYSCMD_UPDATEMETER, X)
Next i
DoCmd.Hourglass True
DoCmd.OpenQuery ("csearch")
DoCmd.Hourglass False
End If

ReturnValue = SysCmd(SYSCMD_SETSTATUS, "Updating query")
ReturnValue = SysCmd(SYSCMD_REMOVEMETER)

End Sub

 

[Remou]

The above code would appear to simply open (or run) a query. To export you need something on the lines of:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "C:\Docs\Query1.xls"

 

[DarkOne72]

hmm...looks like I forget the DoCMD macro to export...I will try both ways..
Thank you for letting me see my oversight and your help.