3

I'm using the VBA code below to export just the visible columns in an Access Datasheet to Excel. Works great, but I also want to export only the filtered rows, based on what the user has filtered for each column. i could use some help figureing out how to do that. Any help is appreciated, lq

 Public Function ExportToExcel()
 On Error GoTo myErr

     Dim strDestFolder As String
     Dim strSQL As String
     Dim qdf As dao.QueryDef
     Dim ctl As Control
     Dim strRandomString As String

     strDestFolder = "MyDestinationPath"
     strRandomString = "AQueryDefName"

     For Each ctl In Me.Child.Form.Controls

         If Not ctl.ColumnHidden Then
             If Len(strSQL) = 0 Then
                 strSQL = "SELECT " & ctl.ControlSource & ", "
             Else
                 strSQL = strSQL & ctl.ControlSource & ", "
             End If
         End If

     Next ctl

     If Len(strSQL) > 0 Then
         strSQL = Left(strSQL, Len(strSQL) - 2)
         strSQL = strSQL & " FROM tblMyTableName WHERE Something = '" & Me.Something & "'"
     End If

     Set qdf = CurrentDb.CreateQueryDef(strRandomString, strSQL)

     DoCmd.OutputTo acOutputQuery, strRandomString, "MicrosoftExcel (*.xls)", strDestFolder & ".xls", True

 myExit:
     CurrentDb.QueryDefs.Delete strRandomString
     Exit Function
 myErr:
     MsgBox Err.Number & " - " & Err.Description, vbCritical, "VBA Error"
     Resume myExit
 End Function
Lauren Quantrell
  • 2,647
  • 6
  • 36
  • 49

1 Answers1

1

The filter property of the subform should return a line suitable for use in a where statement. For example:

([My subform].[ID] Not In (1,2,4,6))

Is returned when I select the dropdown and choose some numbers. Also:

([My subform].[ID] In (719,720))
Fionnuala
  • 90,370
  • 7
  • 114
  • 152