View Single Post
  #1 (permalink)  
Old April 27th, 2006, 04:46 AM
Vince_421 Vince_421 is offline
Friend of Wrox
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default transferspreadsheet command


I have some problems using the transferspreadsheet command. I'm kind of new on the vba-programming so I don't know what I'm doing wrong.

I would like to export data from a query to an excell-sheet using a command button an a form. The query does not have to be opened, the transfer should be done from the main menu without having to look at the data...

The name of the query I have is "qry kassatickets van de laatste 31 dagen".
I know I have to create an event procedure on the 'on click'-event of the command button, but with the vba-code I'm using it does not work.
The excell-sheet should be created and saved on the desktop under the name "kassatickets.xls".

The event procedure I'm using is the following:

Private Sub van_hoofdmenu_naar_exporteren_kassatickets2_Click( )
On Error GoTo Err_van_hoofdmenu_naar_exporteren_kassatickets2_Cl ick

    docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel8,"qry kassatickets van de laatste 31 dagen","Desktop\kassatickets.xls",True,

    Exit Sub

Err_van_hoofdmenu_naar_exporteren_kassatickets2_Cl ick:
    MsgBox Err.Description
    Resume Exit_van_hoofdmenu_naar_exporteren_kassa

End Sub

However, by using this event procedure, the command button on the menu form does not work.
Since it's the first time I'm trying programming, I really don't understand what mistake I'm making (probably a stupid and maybe big one)...
Could anyone help me and give me a detailed description of what I have to do to make the command button work?

Many Thanks