Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-30-2019, 01:21 AM   #1
FrankParsosns911
Newly Registered User
 
Join Date: Sep 2019
Location: Eastern Australia
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
FrankParsosns911 is on a distinguished road
Unhappy Export Table to Excel using a filter on a query

What I have done so far:
Researched many sites including this one.
Tried a number of suggestions, copied code, modified it. Not successful.


What I don't need:
No DoCmd Transfers please as my Excel SS has 4 rows for headings, some cells merged. I have been down that path for weeks and my research says no go.

Success so far:
The code below successfully exports the data to my designated Excel spreadsheet, but ALL the data from the recordset is exported to the named sheets. In other words, too successful.

What I need help with:
Export a query to excel, but filter the query within the database.
I have named spreadsheets that I have to export data to.
I have tried WHERE statements to no avail, probably because I don't know the syntax.

I appreciate any suggestions. You will notice I have included some 'notes in my code. Please note that eventually I will loop from H1 to H60. That is why I have defined HR as a variable, but not yet used it.


Here is my code so far.

Private Sub Command137_Click()
'Export query results to a specific location in a specific spreadsheet

Dim dbs As Database
Dim HR As String

Set dbs = CurrentDb

'The target Excel SS is say (C:\Continuum\H42.xlsx). Set HR to H42

HR = H42

Set rsQuery = dbs.OpenRecordset("QExportCount")

' "QExportCount" contains 1300 records each with a HR field that ranges from H1 to H60. In this example I have targetted H42.

Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True

Set targetWorkbook = excelApp.workbooks.Open("C:\Continuum\H42.xlsx")
targetWorkbook.Worksheets("Counting").Range("A5"). CopyFromRecordset rsQuery

'This is where my difficulty lies; How to filter?

' I need to FILTER out say only the records that have Field HR = 42
'That will allow me to export only those records, say 25 of them.
'Do.cmd does not work for me as my Excel SS has 4 rows of headings, some meged cells.

End Sub

FrankParsosns911 is offline   Reply With Quote
Old 09-30-2019, 01:47 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Export Table to Excel using a filter on a query

is the table/query where the data of QExportCount query is based on the form?
if so, you can add a Criteria on QExportCount,
on design view of QExportCount, add criteria:

field: HR
criteria: Nz([Forms]![yourForm]![HR], [HR])

you can then export the query.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
FrankParsosns911 (09-30-2019)
Old 09-30-2019, 01:53 AM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,662
Thanks: 99
Thanked 1,501 Times in 1,416 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Export Table to Excel using a filter on a query

Recordsets come across with everything no matter what filters are applied so it is best to create one with the desired criteria at the outset.

If you need it with an arbitrary user-applied filter you can read the Filter property and generate the required SQL for the exported recordset dynamically in VBA.

Another way is to loop through the filtered recordset and write the records one by one. Not very efficient but it works. Or write one record at a time to a disconnected ADO recordset and send that to Excel.

You can write the headings one at a time in a loop.

BTW Excel has the Range.CopyFromRecordset method that can be useful sometimes if you are automating Excel from Access. Still ignores the Filter though.

Merged cells in Excel is asking for trouble when automating.

Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
FrankParsosns911 (09-30-2019)
Old 09-30-2019, 02:56 AM   #4
FrankParsosns911
Newly Registered User
 
Join Date: Sep 2019
Location: Eastern Australia
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
FrankParsosns911 is on a distinguished road
Re: Export Table to Excel using a filter on a query

Galaxiom,
Thank you for your swift reply.
Not much joy when I read that recordsets are exported ‘in all’ but I suppose that’s why it is named a record set.
Your next suggestion makes some sense and I will try applying it. It is a new lead for me.
As many of use do, we inherit a set of spreadsheets, merged cells and all, and are asked to work with them. Hence the four header rows which I am bound to.
I will try your suggestion tomorrow (Time zone here GMT +10)
FrankParsosns911 is offline   Reply With Quote
Old 09-30-2019, 03:01 AM   #5
FrankParsosns911
Newly Registered User
 
Join Date: Sep 2019
Location: Eastern Australia
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
FrankParsosns911 is on a distinguished road
Re: Export Table to Excel using a filter on a query

Quote:
Originally Posted by arnelgp View Post
is the table/query where the data of QExportCount query is based on the form?
if so, you can add a Criteria on QExportCount,
on design view of QExportCount, add criteria:

field: HR
criteria: Nz([Forms]![yourForm]![HR], [HR])

you can then export the query.
Thank you for your quick reply, arnelgp

The query stands alone. To export the data from that query, yes, I could add a criterion, but that would mean making 60 queries, each with a different H*.
The solution would be to add the criteria using a WHERE statement in the code and so cycle through the VBA code applying say, H42, then H43 etc.
It is very inefficient to make separate queries.
Your solution would work if I apply the criteria on the query outside this code but as I have 60 exports to make on 18 different categories, I need the criteria for the query applied within the code.
I appreciate your suggestion and know it works for a single case if I apply the criteria on the query itself.
Can you assist with my explanation above.
Cheers and thanks so far: arnelgp

Last edited by FrankParsosns911; 09-30-2019 at 06:36 PM. Reason: spelling
FrankParsosns911 is offline   Reply With Quote
Old 09-30-2019, 04:17 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Export Table to Excel using a filter on a query

from your reply, it's obvious to be that you don't have the table on your form where
you based your QExportCount query.

add an Unbound textbox to your form. name it txtHR.
copy/paste QExportCount. name the copy QExportCount2.
add criteria to the field [HR].

criteria: [FORMS]![theNameOfyourFormHere]![txtHR]
on the click event of your button:
Code:
Private Sub Command137_Click()
    'Export query results to a specific location in a specific spreadsheet
    
    Dim dbs As Database
    Dim HR As String
    Dim ExelApp As Object
    Dim rsQuery As DAO.recordSet
    Dim rsSource As DAO.recordSet
    Dim targetWorkbook As Object
    
    Set dbs = CurrentDb
    
    'The target Excel SS is say (C:\Continuum\H42.xlsx). Set HR to H42
    'HR = H42
    
    Set ExcelApp = CreateObject("Excel.application", "")
    ExcelApp.Visible = True
    
    ' open recordset from query (grouped by HR)
    Set rsQuery = dbs.OpenRecordset("SELECT HR FROM QExportCount GROUP BY HR;", dbOpenSnapshot)
    
    With rsQuery
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Me!txtHR = !HR
            
            ' check if hrX.xlsx is available
            ' ignore if not
            If Dir("C:\Continuum\" & !HR & ".xlsx") <> "" Then
                
                Set targetWorkbook = ExcelApp.workbooks.Open("C:\Continuum\" & !HR & ".xlsx")
                Set rsSource = db.QueryDefs("QExportCount2").OpenRecordset(dbOpenSnapshot)
    
                targetWorkbook.Worksheets("Counting").Range("A5").CopyFromRecordset rsSource
                targetWorkbook.Close True
                
                rsSource.Close
                Set rsSource = Nothing
                
                DoEvents
            End If
            ' move to another record
            .MoveNext
        Wend
        .Close
    End With
    Set rsQuery = Nothing
    ExcelApp.Quit
    Set ExcelApp = Nothing
    
    MsgBox "Done exporting HHs"
    
End Sub
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
FrankParsosns911 (09-30-2019)
Old 09-30-2019, 04:48 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,038
Thanks: 115
Thanked 3,020 Times in 2,747 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Export Table to Excel using a filter on a query

For info, I've moved this thread from the Introduce Yourself forum as its a technical question

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
FrankParsosns911 (09-30-2019)
Old 09-30-2019, 09:16 PM   #8
FrankParsosns911
Newly Registered User
 
Join Date: Sep 2019
Location: Eastern Australia
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
FrankParsosns911 is on a distinguished road
Re: Export Table to Excel using a filter on a query

anelgp

Thanks for your help.
Some of it works. You have got me a lot closer.

The code stops at:
Set rsQuery = dbs.OpenRecordset("SELECT HR FROM QExportCount GROUP BY HR;", dbOpenSnapshot)

The "QExportCount2" you suggested doesn't pick up the value on the form faithfully. It substitutes H10 be default for some reason.
If I type H60 and run the query independently, the query does select records based on the criterion.

However, although you have got me a lot closer, I would still need to type in a value for each export. hat would require 60 occurences for 18 categories. Must be a simpler way with a loop.

I need something like this.
Set rsQuery = dbs.OpenRecordset("SELECT [HR] FROM QExportCount GROUP BY [HR] WHERE [HR] = strHR;", dbOpenSnapshot)

The variable strHR would be picked up from the loop that cycles from say 1 to 60.
Also note taht I included the field brackets []. Are they necessary?

Is that possible. Can you suggest?
FrankParsosns911 is offline   Reply With Quote
Old 09-30-2019, 09:23 PM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Export Table to Excel using a filter on a query

can you show the SQL of QExportCount query.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
FrankParsosns911 (09-30-2019)
Old 09-30-2019, 09:45 PM   #10
FrankParsosns911
Newly Registered User
 
Join Date: Sep 2019
Location: Eastern Australia
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
FrankParsosns911 is on a distinguished road
Re: Export Table to Excel using a filter on a query

SELECT DISTINCTROW CO2.STKEY, CO2.Year, CO2.SCHOOL_YEAR, CO2.TAG, CO2.HR, CO2.SURNAME, CO2.[First Name], CO2.COUFS1A, CO2.COUFS1B, CO2.COUFS1C, CO2.COUFS1D, CO2.COUFS2A, CO2.COUFS2B, CO2.COUFS2C, CO2.COUFS2D, CO2.COUFS3A, CO2.COUFS3B, CO2.COUFS4A, CO2.COUFS4B, CO2.COU1S1A, CO2.COU1S1B, CO2.COU1S1C, CO2.COU1S1D, CO2.COU1S1E, CO2.COU1S1F, CO2.COU2S1A, CO2.COU2S1B, CO2.COU2S1C, CO2.COU2S1D, CO2.COU2S1E, CO2.COU3S1A, CO2.COU3S1B, CO2.COU4S1A, CO2.COU4S1B, CO2.COU4S2A
FROM CO2
GROUP BY CO2.STKEY, CO2.Year, CO2.SCHOOL_YEAR, CO2.TAG, CO2.HR, CO2.SURNAME, CO2.[First Name], CO2.COUFS1A, CO2.COUFS1B, CO2.COUFS1C, CO2.COUFS1D, CO2.COUFS2A, CO2.COUFS2B, CO2.COUFS2C, CO2.COUFS2D, CO2.COUFS3A, CO2.COUFS3B, CO2.COUFS4A, CO2.COUFS4B, CO2.COU1S1A, CO2.COU1S1B, CO2.COU1S1C, CO2.COU1S1D, CO2.COU1S1E, CO2.COU1S1F, CO2.COU2S1A, CO2.COU2S1B, CO2.COU2S1C, CO2.COU2S1D, CO2.COU2S1E, CO2.COU3S1A, CO2.COU3S1B, CO2.COU4S1A, CO2.COU4S1B, CO2.COU4S2A, CO2.SCHOOL_YEAR, CO2.ID, CO2.TIMESTAMP
HAVING (((CO2.SCHOOL_YEAR)<>"0"));

FrankParsosns911 is offline   Reply With Quote
Reply

Tags
export to excel , with filter in db

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop Through Table or Query and Export to Excel jhabey Modules & VBA 6 01-26-2014 06:19 AM
Filter Table and Export to Multiple Excel Workbooks Neilbees Modules & VBA 4 11-21-2013 08:19 AM
VBA Export query with filter to Excel papadega3000 Modules & VBA 10 11-04-2009 10:22 PM
Export a query (in pivot table view) to Excel graviz Forms 1 09-22-2009 03:46 PM
Export Pivot Table query view in Excel caciula Queries 2 06-27-2007 07:54 AM




All times are GMT -8. The time now is 08:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World