WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: jmvmla on July 26, 2019, 06:18:17 AM

Title: Xl autofitering on date
Post by: jmvmla 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
Title: Re: Xl autofitering on date
Post by: td on July 26, 2019, 07:15:34 AM
Assuming you are using Excel, you could do one of the following:

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.
Title: Re: Xl autofitering on date
Post by: stanl 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 (https://stackoverflow.com/questions/18646952/excel-autofilter-dates-in-month-period)


For Tony: Const xlFilterAllDatesInPeriodAugust = 28 (&H1C)  -from Excel 2019 Object Browser
Title: Re: Xl autofitering on date
Post by: stanl on July 29, 2019, 05:04:23 AM
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.
Title: Re: Xl autofitering on date
Post by: td 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.