excel - 将筛选的行从 Access 数据表导出到 Excel

Question

我使用下面的 VBA 代码仅将 Access 数据表中的可见列导出到 Excel。效果很好，但我也想根据用户为每列过滤的内容仅导出过滤后的行。我需要一些帮助来弄清楚如何做到这一点。任何帮助表示赞赏，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

Answer 1

子表单的过滤器属性应返回适合在 where 语句中使用的行。例如:

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

当我选择下拉列表并选择一些数字时返回。另外:

([My subform].[ID] In (719,720))