Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 4th, 2006, 03:34 AM
Registered User
 
Join Date: Sep 2006
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.

 
Old September 7th, 2006, 08:07 PM
Registered User
 
Join Date: Sep 2006
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




 
Old September 8th, 2006, 12:48 AM
Registered User
 
Join Date: Sep 2006
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.
 
Old September 8th, 2006, 06:55 AM
Friend of Wrox
 
Join Date: Mar 2004
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
 
Old September 14th, 2006, 03:08 AM
Registered User
 
Join Date: Sep 2006
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


 
Old January 24th, 2008, 08:26 AM
Registered User
 
Join Date: Jan 2008
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
 
Old January 30th, 2008, 09:32 PM
Registered User
 
Join Date: Jan 2008
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.
 
Old February 8th, 2008, 07:23 AM
Registered User
 
Join Date: Feb 2008
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.





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





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