Hi,

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_van_hoofdmenu_naar_exporteren_kassa:
    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

Hi,

   It looks okay except the path statement to the saved file location. It is non-existent. It needs to be complete. So this line:

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

Should be:

docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,"qry kassatickets van de laatste 31 dagen","C:\Documents and Settings\All Users\Desktop\kassatickets.xls",True,

I am not sure whose desktop you want to put it on, so I used All Users. You may want to specify a particular user, but All Users means you can send this app to anyone's desktop and on computer.

Also, please consider removing spaces from all your objects name and using Pascal casing, like this:

From "qry kassatickets van de laatste 31 dagen"
To "qryKassaticketsVanDeLaatste31Dagen"

That's actually Camel/Pascal casing, but you get the idea. It makes referencing object names much easier in your code since spaces can cause problems in some queries for example.

Did this help?



mmcdonal

I still have an error. I'm using the option explicit in the editor and my line is in red. However, I don't know why...

But I am rebuilding my database to correct some errors I made while building it the first time. I also have some new ideas I'm trying to put into the database, so I will see about this problem later.

Thanks for your help.