Export to Excel based on current form recordset

[MelB]

I guess I am not sure how to do this. I have a form, based on a query, that filters data to one of 7 divisions whe the form opens. On that form, I also have menu bar with the 'Filter by Form' and 'Toggel Filter' buttons. I would like to Export the current dataset, including any additional filter set with the filter by form, to an Excel worksteet. At that point I need to format some columns, delete some columns, rename come columns, so I can't simply use the Analyze with Excel because it does not allow that. I can't seem to figure out how to take the forms current recordset and export it. Basically I am using automation to open excel, and I can get this to work. If I open the records set below...

' Create recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryDivision", dbOpenSnapshot)

This gets me the initial recordset for the form but does not include any filtering that may have done.

I tried to set a variable to the forms filter property but that seems to be what the property was the last time the form was closed... not what it is currently filtered to. I can pass the string and set the recordset's filter property but it isn't sending the current filter.... any ideas

Any easier ways to do this??? Thanks in advance!

 

[chenn]

The forms filter property should return the current filter. I just tried it and that seem to be the case. When I want to send the recordsourse of a form or a report to excel, I usually create a query based on this information and export it. I added some code that you can put under a button that should dynamically create a query and then you can export that query. First is a function that creates a query when given a name and recordsource. Next I will breifly go over how you go about calling the function and exporting the query.

Function CREATE_QUERY(strQName As String, strRecordSource As String)

Dim qdf As QueryDef
'Refresh QueryDefs collection.
CurrentDb.QueryDefs.Refresh
'If query exists, delete it
For Each qdf In dbs.QueryDefs
If qdf.Name = strQName Then
CurrentDb.QueryDefs.Delete qdf.Name
End If
Next qdf
' Create new QueryDef object.
Set qdf = CurrentDb.CreateQueryDef(strQName, strRecordSource)

End Function

Private Sub Export_Button_Click()

Dim strFormRecords as string
Dim strFormFilter as string
Dim strNewSQL as string
Dim strNewQueryName as string

'set your initial values to the existing values from the form
strFormRecords = me.recordsource
strFormFilter = me.filter

'add the filter to the initial query string
'the below line assumes that your initial form's
'recordsource has no filter already included.
'If it does, you should be able to simply replace the
' WHERE ' with ' AND '
'remember a space before and a space after the word
'also, your forms initial recordsource has to be a sql statement
'you will also have to remove the ';' from the end
'of the sql statement and do some validation on if there is
'a filter selected or not
strNewSQL = strFormRecords & " WHERE " & strFormFilter

'now create the query
strNewQueryName = "Export Query"
CREATE_QUERY strNewQueryName, strNewSQL

'now output the query as an excel sheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strNewQueryName, "C:\Query.xls"

end sub

Try this, you may have to do some minor 'Tweeking' but it should get you rolling.

I generally build my own filters on my forms. It's more work, but it makes these kind of things easier. All I would have to do is to reference the forms recordsource and export that. The filter would already be there....
Post if you have any problems.

 

[hectonath]

Hi
Just some little corrections :

Sub CREATE_QUERY(strQName As String, strRecordSource As String)

Dim qdf As QueryDef
'Refresh QueryDefs collection.
CurrentDb.QueryDefs.Refresh
'If query exists, delete it
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = strQName Then
CurrentDb.QueryDefs.Delete qdf.Name
End If
Next qdf
' Create new QueryDef object.
Set qdf = CurrentDb.CreateQueryDef(strQName, strRecordSource)

End Sub

 

[Minty]

I think that's a record.

21 year-old thread being replied to... Impressively late to the party

 

[cheekybuddha]

...
'If query exists, delete it
For Each qdf In CurrentDb.QueryDefs
  If qdf.Name = strQName Then
    CurrentDb.QueryDefs.Delete qdf.Name
  End If
Next qdf
...

Did you get ChatGPT to write this code for you?

Since you know the name of the query, there is no need to loop all the queries in the db (especially since you don't break out of the loop once you have found the one you want to delete). If the db has thousands of queries this is a waste of time.

More simple to just use:

' ...
  db.QueryDefs.Delete strQName
' ...

And probably even easier just to change the SQL of the existing QueryDef rather than delete and recreate it.

 

[Mark_]

I think that's a record.

21 year-old thread being replied to... Impressively late to the party

The thread Necromancy is strong with this one!