Export Table to Excel using a filter on a query

[FrankParsosns911]

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

 

[arnelgp]

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]!

---

,

---

)

you can then export the query.

 

[Galaxiom]

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.

 

[FrankParsosns911]

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 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]!

---

,

---

)

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

 

[arnelgp]

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

---

.

criteria: [FORMS]![theNameOfyourFormHere]![txtHR]
on the click event of your button:

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 [COLOR="blue"]QExportCount[/COLOR] GROUP BY HR;", dbOpenSnapshot)
    
    With rsQuery
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            [COLOR="blue"]Me!txtHR = !HR[/COLOR]
            
            ' check if hrX.xlsx is available
            ' ignore if not
            If Dir("C:\Continuum\" & [COLOR="blue"]!HR[/COLOR] & ".xlsx") <> "" Then
                
                Set targetWorkbook = ExcelApp.workbooks.Open("C:\Continuum\" & [COLOR="blue"]!HR[/COLOR] & ".xlsx")
                Set rsSource = db.QueryDefs("[COLOR="blue"]QExportCount2[/COLOR]").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

 

[isladogs]

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

 

[FrankParsosns911]

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

---

FROM QExportCount GROUP BY

---

WHERE

---

= 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?

 

[arnelgp]

can you show the SQL of QExportCount query.

 

[FrankParsosns911]

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"));