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
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.
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 (https://stackoverflow.com/questions/18646952/excel-autofilter-dates-in-month-period)
For Tony: Const xlFilterAllDatesInPeriodAugust = 28 (&H1C) -from Excel 2019 Object Browser
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.
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.