UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3  (Go to first unread post)
   Reply to this topicStart new topic
> Export Filtered Result Excel, Access 2016    
 
   
OCM
post Sep 14 2017, 01:49 PM
Post#41



Posts: 111
Joined: 12-September 02
From: Eastern, USA


tehDBguy,

In my OP (Post #1), I included a code for on click event of “run Query” button which works beautifully as follows:
1. Clicking he ‘Run Query’ button w/o applying filters to the form, the report displays everything.
2. Clicking the ‘Run Query’ after applying filters to the form, the report displays filtered records

Based on the code on my OP & your last suggestion, can you please give me a line of two syntax as an how to re-write the code?


TIA

Regards,
Go to the top of the page
 
theDBguy
post Sep 14 2017, 02:04 PM
Post#42


Access Wiki and Forums Moderator
Posts: 70,666
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Okay, you can use almost everything you got in there, if you're using the same form and table. Let's assume the name of your table is Table1 and you want to export all the fields in it, then you can try something like this:

CODE
Dim qdf As QueryDef
Dim strWhere As String


... All the code in your original post to build strWhere here...


On Error Resume Next
DoCmd.DeleteObject acQuery, "qryTemp"
On Error GoTo 0

Set qdf = CurrentDb.CreateQueryDef("qryTemp", "SELECT * FROM Table1 WHERE " & strWhere)

DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, "C:\FileName.xlsx", True

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
OCM
post Sep 14 2017, 03:10 PM
Post#43



Posts: 111
Joined: 12-September 02
From: Eastern, USA


theDBguy,

Okay, I think we are getting closer.

I’m using the same form, and the form is based on a query which includes multiple tables.
Using your example, do I need to list all my tables like for example:
Set qdf = CurrentDb.CreateQueryDef("qryTemp", "SELECT * FROM Table1 WHERE " & strWhere)
Set qdf = CurrentDb.CreateQueryDef("qryTemp", "SELECT * FROM Table2 WHERE " & strWhere)
.
.
.
Set qdf = CurrentDb.CreateQueryDef("qryTemp", "SELECT * FROM Table5 WHERE " & strWhere)

TIA

Regards,
Go to the top of the page
 
theDBguy
post Sep 14 2017, 05:23 PM
Post#44


Access Wiki and Forums Moderator
Posts: 70,666
Joined: 19-June 07
From: SunnySandyEggo


No, just replace Table1 with the query's name. For example,

SELECT * FROM QueryName WHERE...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
OCM
post Sep 18 2017, 11:53 AM
Post#45



Posts: 111
Joined: 12-September 02
From: Eastern, USA


Hi theDBguy,
Many thanks, exporting filtered records to excel works beautifully.
I greatly appreciate all your guidance on this.

Regards,
Go to the top of the page
 
theDBguy
post Sep 18 2017, 02:19 PM
Post#46


Access Wiki and Forums Moderator
Posts: 70,666
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You're welcome. Glad to hear you got it working. Sorry it took so long. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
OCM
post Sep 19 2017, 08:08 AM
Post#47



Posts: 111
Joined: 12-September 02
From: Eastern, USA


Thank you very much!!!

Regards,
Go to the top of the page
 
3 Pages V < 1 2 3


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2017 - 03:45 PM