Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 27th, 2006, 04:46 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default transferspreadsheet command

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

Reply With Quote
  #2 (permalink)  
Old April 27th, 2006, 06:35 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old May 3rd, 2006, 11:29 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
TransferSpreadsheet darkhalf Access VBA 2 February 15th, 2008 04:23 PM
TransferSpreadsheet stealthdevil Access VBA 2 March 16th, 2007 02:45 PM
TransferSpreadsheet kaleb0521 Access VBA 1 August 28th, 2006 12:59 PM
transferspreadsheet ajmil11 Access 3 January 19th, 2006 03:42 AM
TransferSpreadsheet ajmil11 Access VBA 1 January 19th, 2006 02:07 AM



All times are GMT -4. The time now is 03:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.