Xl autofitering on date

Started by jmvmla, July 26, 2019, 06:18:17 AM

Previous topic - Next topic

jmvmla

Hi all,
I would like to filter an XL spreadsheet based on month and year of a date
To do that , I plan to use the autofiltering action but I have a problem with the constant value "xlFilterValues" ; which seems not existing
any advises will be welcomed

thanks

jm

td

Assuming you are using Excel, you could do one of the following:

  • Use the ObjectConstantsGet function to obtain all the enumeration values implemented by Excel
  • Use the WinBatch "WIL Type Viewer" utility to load all interfaces and enumerations supported by the Excel object.  Each enumeration will have the numeric value associated with the enumeration element.
  • You could perform a Web search on the value and discover the numeric value associated with the enumeration element.  If you did, you would this page as one of the search results: https://docs.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator

Once you have the numeric value you can use the number in place of the enumeration element or create a variable with the numeric value assigned to it and use the variable.  The latter makes for a more readable/ maintainable script.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Interesting.   There are Excel objects for filtering with code, for example Criteria1=  xlFilterAllDatesInPeriodAugust to get dates for August.  The secret would be to make it dynamic for any date.  This article may help


https://stackoverflow.com/questions/18646952/excel-autofilter-dates-in-month-period


For Tony: Const xlFilterAllDatesInPeriodAugust = 28 (&H1C)  -from Excel 2019 Object Browser

stanl

Quote from: stanl on July 27, 2019, 02:54:38 AM
For Tony: Const xlFilterAllDatesInPeriodAugust = 28 (&H1C)  -from Excel 2019 Object Browser


Only reason I included this: seems filter objects are iterative, i.e. January = 21 February=22 etc.  So wondering if the new WB Map functions could build a dynamic lookup to create criteria for the Excel filter.

td

Only tangentially topical - but adding an optional parameter to the ObjectConstToArray function to create a map of available constants instead of a regular WIL array of constants could be useful.  Don't know if the function is used enough to make it worth the bother, however. 
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade