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
  #11 (permalink)  
Old September 4th, 2006, 03:34 AM
Registered User
 
Join Date: Sep 2006
Location: Paranaque, , Philippines.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to lushh
Default

Quote:
quote:Originally posted by Steven
 Ummm, I hate to rain on everyone's coding parade - but if you export a query to excel, it exports it to a worksheet with the title of that query.
Therefore, if you export 5 queries to the same excel file, it will export them to 5 worksheets in that same file.

No need for Excel Automation or any real coding at all.

It's actually one of the easiest things to do. just do this:
Code:
DoCmd.TransferSpreadsheet 1, 8, "Query1", "C:\SomeFile.xls", True
DoCmd.TransferSpreadsheet 1, 8, "Query2", "C:\SomeFile.xls", True
That will export Query1 and Query2 to C:\SomeFile.xls with 2 worksheets, titled Query1 and Query2

It's that easy

I am a loud man with a very large hat. This means I am in charge
hi.. i was just wondering what are those "1" and "8" after DoCmd.TransferSpreadsheet for? sorry, i'm just a newbie. thanks.

Reply With Quote
  #12 (permalink)  
Old September 7th, 2006, 08:07 PM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Could anyone give an example of how the code below should be used as I use Access but do not a single clue on how to add this code to my database. I have a large amount of Queries I wish to put onto separate sheets within a Excel spreadsheet. I really would appreciate some help.


DoCmd.TransferSpreadsheet 1, 8, "Query1", "C:\SomeFile.xls", True
DoCmd.TransferSpreadsheet 1, 8, "Query2", "C:\SomeFile.xls", True




Reply With Quote
  #13 (permalink)  
Old September 8th, 2006, 12:48 AM
Registered User
 
Join Date: Sep 2006
Location: Paranaque, , Philippines.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to lushh
Default

you should place the code inside a command button. here is a more convenient way:

Go to Forms then Design. You will find a command button icon on the toolbar. Click and drag the button icon to your form. A command button wizard dialog box will appear. Choose "Report Operations" under Categories. Next, choose "Send Report to File" then choose report from the drop down list. You need to add your text to the command button then set the name for that button.

Hope this helps.
Reply With Quote
  #14 (permalink)  
Old September 8th, 2006, 06:55 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

Interesting that we never hear back from the original poster on whether this worked out or not.

I suggest "Integrating Excel and Access" by O'Reilly. ISBN: 0-596-00973-9.

This has all the fine grain control between the two apps.

Sorry Wrox, but it's a good book.



mmcdonal
Reply With Quote
  #15 (permalink)  
Old September 14th, 2006, 03:08 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

Could anyone give me an example of the code above so I can see how the code is used within an module. Just so I can get some points of reference.

Matt


Reply With Quote
  #16 (permalink)  
Old January 24th, 2008, 08:26 AM
Registered User
 
Join Date: Jan 2008
Location: Cali, Valle, Colombia.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Saludos
I need to know how to export a query in access larger than 65536 rows to excel in more than one worksheet. Without having to split the query in Access.Is this possible?

Thanks,

Bye,

DFR
Reply With Quote
  #17 (permalink)  
Old January 30th, 2008, 09:32 PM
Registered User
 
Join Date: Jan 2008
Location: , Ontario, Canada.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In response to Mattemery, the export could have been written like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "ExcelFile.xls", True

Some people prefer numbers. Instead of acExport, your choices are: acExport, acImport or acLink. Or, 1, 2, or 3.

Your spreadsheet type could be acSpreadsheetTypeExcel3 acSpreadsheetTypeExcel4, acSpreadsheetTypeExcel5, acSpreadsheetTypeExcel7, acSpreadsheetTypeExcel8, acSpreadsheetTypeExcel9, acSpreadsheetTypeLotusWJ2, WK1, WK3 or WK4 (excuse me for not typing all those out). Numbers could be substituted here as well. I find it clearer to use the words.

Running this multiple times creates multiple sheets or multiple workbooks depending how you set it up. Note the same command allows you to export import or link to Excel.
Reply With Quote
  #18 (permalink)  
Old February 8th, 2008, 07:23 AM
Registered User
 
Join Date: Feb 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

Is is possible to have a query import data from access to excel but putting multiple fields into one cell?

I have projects that are devided up by % of project done be each person and also lots of info on these projects. I want to be able to have all persons names in one row (cell) so that all the other data can be read in one physical line.

Thanks for any 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
Exporting Query Data From Access to Excel JimInSouthernCal Access VBA 3 December 21st, 2007 08:34 AM
Exporting form Access Database to Excel asters VB.NET 2002/2003 Basics 5 April 13th, 2007 02:23 PM
Exporting to Existing Excel Worksheet from Access kfs Access VBA 2 August 3rd, 2006 10:56 AM
exporting a workbook from excel to access zisko3 Access 1 February 3rd, 2004 12:05 PM
Exporting access database to Excel .xls Squall Leonhart Classic ASP Databases 5 December 2nd, 2003 07:42 PM



All times are GMT -4. The time now is 01:54 PM.


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