Send filtered subform data to excel

[heidii]

Hello All

I am having trouble exporting my filtered by form subform to an excel
sheet. I can use the output to macro, but it send all the records.
How can I use an onclick even to send just the filtered records to
excel?

heidi

 

[Allen Browne]

The simplest solution will be to set up a query that can be used for the
export. You can then change the SQL of this query so it matches the
subform's filter. It will then be dead simple to export.

1. Create a query that selects all the fields you want to export to Excel.
Don't worry about the criteria. Save the query with a name such as
qryExport.

2. Switch the query to SQL View (View menu), so you can see the SQL
statement. You will use this as an example of what you need in your code.

3. In the Click event of the command button, concatenate the subform's
Filter property into the WHERE clause, and assign the whole thing to the SQL
property of the QueryDef you saved.

It will end up something like this:

Private Sub cmdExport_Click()
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT F1, F2, F3 ... " & vbCrLf & _
"FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID " & vbCrLf
Const strcTail = " ORDER BY F1, F2;"

With Me.Sub1.Form
If .FilterOn Then
strWhere = "WHERE " & .Filter & vbCrLf
End If
End With
CurrentDb.QueryDefs("qryExport").SQL = strcStub & strWhere & strcTail

strFile = "C:\MyFolder\MyFile.xls"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel8, "qryExport",
strFile
End Sub

 

[heidii]

Hello Allen:

I have never worked with QueryDef's before.

My subform is not bound to my main form. My main form only holds the
combo search boxes and my search buttons. I have done this on a main
form because I want my user to beable to see the results in a
datasheet format, and beable to hide unhide columns as needed. I also
have on my main form a button that sends what they filtered to a
report showing only the columns they have selected.

I am in the beginner stage of VBA.

heidi

 

[heidii]

Hi Allen:

I don't know how to do this part

3. In the Click event of the command button, concatenate the
subform's
Filter property into the WHERE clause, and assign the whole thing to
the SQL
property of the QueryDef you saved.

Heidi

 

[Allen Browne]

Take a look at the example, Heidi

It assumes:
a) You added a command button to your form for the export, and set the
button's Name to cmdExport.

b) You set the button's On Click property to:
[Event Procedure]
Then you click the Build button (...) beside this.
Access opens the code window.

c) You set up the code as in the example.

A QueryDef is just a saved query (i.e. the definition of the of the query,
not the actual data it returns.) It is defined as a SQL statement, so the
task of your code is to put the right WHERE clause into the middle of the
SQL statement.

To do this, you will look at the SQL statement in SQL View. The stub of the
SQL statement (everything before the WHERE) goes into the constant we named
strcStub at the top. The tail of the SQL statement (everything after the
WHERE clause), goes in the constant we named strcTail. Then you take
whatever is in the subform's Filter property, and use that as the WHERE
clause in the middle of the SQL statement.

Here's the example again:

Private Sub cmdExport_Click()
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT F1, F2, F3 ... " & vbCrLf & _
"FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID " & vbCrLf
Const strcTail = " ORDER BY F1, F2;"

With Me.Sub1.Form
If .FilterOn Then
strWhere = "WHERE " & .Filter & vbCrLf
End If
End With
CurrentDb.QueryDefs("qryExport").SQL = strcStub & strWhere & strcTail

strFile = "C:\MyFolder\MyFile.xls"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel8, _
"qryExport", strFile
End Sub

 

[tmdrake]

I am trying to do the same thing, however the information displayed in the
subform is based on a query from the Mainform using the following code:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

I am using the following code to export the results displayed in the subform:

Private Sub Export_to_Excel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked_Export", "(*.xls)",
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

I am receiving the following error: Run-Time error '2498': An expression
you entered is the wrong data type for one of the arguments.

Please tell what I am doing wrong.

--
tmdrake

Take a look at the example, Heidi

It assumes:
a) You added a command button to your form for the export, and set the
button's Name to cmdExport.

b) You set the button's On Click property to:
[Event Procedure]
Then you click the Build button (...) beside this.
Access opens the code window.

c) You set up the code as in the example.

A QueryDef is just a saved query (i.e. the definition of the of the query,
not the actual data it returns.) It is defined as a SQL statement, so the
task of your code is to put the right WHERE clause into the middle of the
SQL statement.

To do this, you will look at the SQL statement in SQL View. The stub of the
SQL statement (everything before the WHERE) goes into the constant we named
strcStub at the top. The tail of the SQL statement (everything after the
WHERE clause), goes in the constant we named strcTail. Then you take
whatever is in the subform's Filter property, and use that as the WHERE
clause in the middle of the SQL statement.

Here's the example again:

Private Sub cmdExport_Click()
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT F1, F2, F3 ... " & vbCrLf & _
"FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID " & vbCrLf
Const strcTail = " ORDER BY F1, F2;"

With Me.Sub1.Form
If .FilterOn Then
strWhere = "WHERE " & .Filter & vbCrLf
End If
End With
CurrentDb.QueryDefs("qryExport").SQL = strcStub & strWhere & strcTail

strFile = "C:\MyFolder\MyFile.xls"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel8, _
"qryExport", strFile
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen:

I don't know how to do this part

3. In the Click event of the command button, concatenate the
subform's
Filter property into the WHERE clause, and assign the whole thing to
the SQL
property of the QueryDef you saved.

Heidi