Pass a variable to Invoke VBA activity, for the filter the excel file

[Amol_Golhar]

Dear Team,

Below is the script which i have recorded through Macro.

Sub DateTimeFilter()
’
’ DateTimeFilter Macro
’

’
ActiveWindow.ScrollColumn = 10
Columns(“N:N”).Select
Selection.NumberFormat = “dd-mmm-yy hh:mm:ss”
Range(“N2”).Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 14
ActiveSheet.Range(“$A$2:$BK$319”).AutoFilter Field:=14, Criteria1:= _
“<1/1/2024 16:28”, Operator:=xlAnd
End Sub

In above script i have to pass the current date variable instead fo hardcoded date “1/1/2024 16:28”.

I have tried below way by modifying the script,

Sub DateTimeFilter(DateTime1)
’
’ DateTimeFilter Macro
’

’
ActiveWindow.ScrollColumn = 10
Columns(“N:N”).Select
Selection.NumberFormat = “dd-mmm-yy hh:mm:ss”
Range(“N2”).Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 14
ActiveSheet.Range(“$A$2:$BK$319”).AutoFilter Field:=14, Criteria1:= _
“<Datetime1”, Operator:=xlAnd
End Sub

But it writing the “Datetime1”, it is not writing the value which i am passing through the Entry Method Parameter.

Please help me to solve the issue.

Thanks & Regards,
Amol Golhar

[Anil_G (Anil Gorthi)]

@Amol_Golhar

In macro you shuld write like this

"<" & DateTime1

Also while passing better give the format

Now.ToString("M/d/yyyy HH:mm")

Cheers

[Amol_Golhar]

Thank you for the help.