Author Topic: Xl autofitering on date  (Read 129 times)

jmvmla

  • Newbie
  • *
  • Posts: 5
Xl autofitering on date
« on: July 26, 2019, 06:18:17 am »
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

  • Tech Support
  • *****
  • Posts: 3017
    • WinBatch
Re: Xl autofitering on date
« Reply #1 on: July 26, 2019, 07:15:34 am »
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

  • Pundit
  • *****
  • Posts: 936
Re: Xl autofitering on date
« Reply #2 on: July 27, 2019, 02:54:38 am »
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

  • Pundit
  • *****
  • Posts: 936
Re: Xl autofitering on date
« Reply #3 on: July 29, 2019, 05:04:23 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

  • Tech Support
  • *****
  • Posts: 3017
    • WinBatch
Re: Xl autofitering on date
« Reply #4 on: July 29, 2019, 07:46:04 am »
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