Excel Export Filtered Form Data To Excel

[ocm]

Greetings,

I created a form whereby users answer to a prompt and click a button to display a report based on selections.
This works as planned. I then created a second button to export the filtered records to excel using the following code which gives me everything (not filtered) as this doesn’t have the ability to filter record:

Private Sub cmdExport_Click()
DoCmd.OutputTo acOutputQuery, "My_Query_Name", acFormatXLSX, , True
End Sub

Someone in this forum had similar question and the following code was posted as an example (I failed to copy the link to the discussion, only copied the following code)

Transferspreadsheet does not have the ability to filter records.
If you elect to use vba, here’s an example:

Private Sub yourButtonToImportName()

Dim db As dao.Database
Dim qrydef As dao.QueryDef

Dim strSQL As String
Dim bolWithFilterOn As Boolean
Dim strTempQryDef As String
Dim strRecordSource As String

strTempQryDef = "__zqry"

bolWithFilterOn = Forms("yourMainFormName")("yourSubFormName").Form. FilterOn

strRecordSource = Forms("yourMainFormName")("yourSubFormName").Form. RecordSource

If InStr(strRecordSource, "SELECT ") <> 0 Then
strSQL = strRecordSource
Else
strSQL = "SELECT * FROM [" & strRecordSource & "]"
End If

' just in case our sql string ends with ";"
strSQL = Replace(strSQL, ";", "")

If bolWithFilterOn Then
strSQL = strSQL & _
IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
Forms("yourMainFormName")("yourSubFormName").Form. Filter
End If

Set db = CurrentDb

'create temporary query
Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL)
db.QueryDefs.Append qrydef
Set qrydef = Nothing

DoCmd.TransferSpreadsheet TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=strTempQryDef, _
FileName:=Replace(CurrentProject.Path & "\", "\\", "\") & strTempQryDef & ".xlsx"

' Delete the temporary query
db.QueryDefs.Delete strTempQryDef

Set db = Nothing

End Sub

Can you please assist?

TIA

Regards,