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 June 5th, 2007, 11:08 AM
Friend of Wrox
 
Join Date: Apr 2007
Location: , , .
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default Export Values to Excel Sheet from Sub form

Hi All,

I have 3 combo boxes on main form and sub form is populating with query

Dim SupplierID
Dim ProductID

SupplierID = Me.cmbSupplierList
ProductID = Me.cmbProductTypes.Column(0)
Me.ExportProductToExcel_subform.Form.Recordsource = "SELECT Products.PID, Products.ProductType FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType WHERE (((ProductTypes.SID)= '" & SupplierID & "')) And (((ProductTypes.ProductType)= " & ProductID & "))"


Now i want to Export all these value in Subform into Excel Sheet

Any idea suggestion

Regards

Ayaz

Reply With Quote
  #2 (permalink)  
Old June 5th, 2007, 11:34 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

I am sure there is a more elegant way to do this, but my presumption is that you cannot export to excel from any dynamic query, it must come from a saved query or a table.

What I do is create a table that holds all the fields in my query, then create a delete query to empty the table, then create an append query to push new records into the empty table, then export the table to Excel.

In your case, you would create an Append query seperate from your subform, and then in the criteria lines for ProductType and Supplier, add:

[Forms]![myFormWithSubForm].[cmbSupplierList]
[Forms]![myFormWithSubForm].[cmbProductTypes]

No need to specify Column(0) since that should be the bound column anyway.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETETempTable"
DoCmd.OpenQuery "qryAPPENDTempTable"
DoCmd.SetWarnings True
DoCmd.... export to excel here

Did that help?









mmcdonal
Reply With Quote
  #3 (permalink)  
Old June 6th, 2007, 05:28 AM
Friend of Wrox
 
Join Date: Apr 2007
Location: , , .
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default

 Hi mmcdonal

Thanks for your help its brilliant.

Now few things

when I use DoCmd.OpenQuery --> gives error [Datatype mismatch in Criteria expression]
DoCmd.OpenQuery "AppendTempTableProduct"
here is query
"INSERT INTO TempTableProduct ( PID, SID, SupplierNum, Description, BoxQuantity, ProductType )
SELECT Products.PID,
 Products.SID,
 Products.SupplierNum,
 Products.Description,
 Products.BoxQuantity,
 Products.ProductType
FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType
WHERE (((ProductTypes.SID)='" & [Forms]![ExportToExcel].[cmbSupplierList] & "'))
And (((ProductTypes.ProductType)=" & [Forms]![ExportToExcel].[cmbProductTypes].Column(0) & "));
"
And with out column(0) is picking up first colum which i dont need in [Forms]![ExportToExcel].[cmbProductTypes].Column(0)

what i did

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete from TempTableProduct"
'DoCmd.RunSQL "INSERT INTO TempTableProduct ( PID, SID, SupplierNum, Description, BoxQuantity,ProductType ) SELECT Products.PID, Products.SID, Products.SupplierNum, Products.Description, Products.BoxQuantity, Products.ProductType FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType WHERE (((ProductTypes.SID)= '" & [Forms]![ExportToExcel].[cmbSupplierList] & "')) And (((ProductTypes.ProductType)= " & [Forms]![ExportToExcel].[cmbProductTypes].Column(0) & "))"
DoCmd.OpenQuery "AppendTempTableProduct"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempTableProduct", "c:\test2.xls"


any further advice

Regards

Ayaz

Reply With Quote
  #4 (permalink)  
Old June 12th, 2012, 03:18 PM
Registered User
Points: 6, Level: 1
Points: 6, Level: 1 Points: 6, Level: 1 Points: 6, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The most elegant way of exporting subform's data to Excel, I am aware of, is with use of MS Access add-in 'A2EE.mda'.
This add-in has been developed exactly for the purpose of exporting data from Access to Excel.
It can export data from both form and subform all at once and show them all in Excel in original Access-like drill-down structure.
You may find more details on:
http://www.limbersti.cz/A2EE/

Rgds
Norbert
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
Gridview data export to excel sheet abhishekkashyap27 C# 2005 1 August 1st, 2008 10:10 AM
Export data from a datalist to an Excel sheet see07 ASP.NET 1.x and 2.0 Application Design 3 February 23rd, 2007 07:20 PM
How To Export a specific Excel Sheet as a csv file mrjits Excel VBA 5 August 1st, 2006 03:04 PM
display excel sheet in form Kaustav Pro VB 6 0 March 8th, 2006 02:07 AM
Export Image to Excel sheet haribala.raj General .NET 0 September 13th, 2005 11:56 PM



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


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