Export Filtered (Sub)Form

mjmact

New Member
Joined
Jan 22, 2007
Messages
36
I can't figure out how to do this, or even if it can actually be done. I have a form setup with some controls and a sub-form setup as a datasheet. The controls let the user filter the data on the datasheet sub-form (as well as allowing them to do other things). Once the choose what they want as the filters, and the datasheet has had the filters applied accordingly, I would like to allow the user to export the contents of the datasheet subform in its filtered condition (i.e. only export what is displayed with the filters active) Is there a good way to do this? Any help is greatly appreciated.
Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
What it sounds like to me is you have a query (SQL string) that is used for the RecordSource of the subform. You then build a filter to apply to the subform based on the contents of those text boxes on the main form. Therefore, what you basically have is a query (the record source) that you can add a WHERE clause to same as the filter for the subform, that you can then use as an SQL string to export the data you want.

Once you get the SQL string you want to export the data, you will then need to put that string into a query object so you can use that query object name in the Table Name field of the TransferSpreadsheet operation. To create a new query within VBA code you will need to be sure that the Microsoft 3.6 DAO object Library is referenced. Within the VBA editor, use the menu Tools, option References. If you don't see DAO then scroll down until you find it, put a check mark in the box, and then close the References dialog box.

Now the code will look something like this. Be sure to use your own names.
Dim MyQuerydef as DAO.QueryDef
Code:
Set MyQueryDef = Currentdb.CreateQueryDef("MyQueryName", "SELECT * FROM Table1;")
You can either put the SQL in as I did, or reference a variable name you have already used to build the SQL into.
 

merce33

Active Member
Joined
Oct 27, 2006
Messages
277
Vic:

What you posted is the exact situation that I am in. Actually, even though my code is messy, it turns out that i tried to do exactly what you instructed in your post.
But I am having a tough time adding the SQL for the Filter and OrderBy clauses to the SQL of my already existing subform RecordSource (a query).
I noticed your post is much simpler... but how do you get the Filter and OrderBy clauses into "Table1"?

Code:
Private Sub lblHeaderItem3_Click()
'On Error GoTo HandleError

    Dim db As DAO.Database
    Dim frm As Form             'reference to the NavPaneFields subform
    Dim qry As DAO.QueryDef     'reference to the NavPaneFields subform recordSource
    'new SQL and original SQL strings. need the original because it was saving the new SQL
    Dim strSQL, strOriginalSQL As String
    'portions of the SQL string containing the strings in the filter & orderBy properties
    'of the NavPaneFields subform
    Dim WHEREclause, OrderByClause As String
    
    Set db = CurrentDb
    Set frm = Forms!frmMainFunctions.NavPaneFields.Form
    Set qry = db.QueryDefs(frm.RecordSource)
    
    strOriginalSQL = qry.SQL
    strSQL = qry.SQL
    
    'build the SQL WHERE clause from the NavPaneFields subform's Filter property
    'if Filter clause is empty do nothing, if not, then strip the query name from the fields
    WHEREclause = IIf(IsNull(frm.Filter) Or Len(frm.Filter) = 0, "", Replace(frm.Filter, qry.Name & ".", ""))
    'add the filter contents to a valid SQL where clause
    WHEREclause = "WHERE " & IIf(Len(WHEREclause) = 0, "", WHEREclause & " AND ")
    'build the SQL ORDERBY clause from the NavPaneFields subform's OrderBy property
    'if no order by, finish the SQL by adding semi colon, if not add ORDERBY from form then semi-colon
    OrderByClause = IIf(IsNull(frm.OrderBy) Or Len(frm.OrderBy) = 0, ";", ", " & frm.OrderBy & ";")
    
    'insert the new clauses into the RecordSource query's SQL
    'replace old where clause with newly constructed one
    strSQL = Replace(strSQL, "WHERE ", WHEREclause)
    'add the order by clause on to the end
    strSQL = Replace(strSQL, ";", OrderByClause)
    
    qry.SQL = strSQL
    
    'export to excel
    DoCmd.OutputTo acOutputForm, qry.Name, acFormatXLS, , True
    'restore original SQL to the query so that it is left unmodified
    qry.SQL = strOriginalSQL
    
ExitSub:
    'On Error Resume Next
    qry.Close
    db.Close
    Set db = Nothing
    Set frm = Nothing
    Set qry = Nothing
    Exit Sub

HandleError:
    MsgBox Err.Description
    GoTo ExitSub
    
End Sub
 

merce33

Active Member
Joined
Oct 27, 2006
Messages
277
sorry, copy & paste error in my previous post.

Code:
DoCmd.OutputTo acOutputForm

should read:

Code:
DoCmd.OutputTo acOutputQuery
 

Forum statistics

Threads
1,181,054
Messages
5,927,855
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top