how do i create/pass a variant in order to filter multiple criteria using win32com

Question

i am not strong with VBA, but as i understand it AutoFilter requires an Array() Variant when running xlFilterValues.

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
mv_wbk = excel.ActiveWorkbook
mv_sht = mv_wbk.Worksheets("mv")

# https://docs.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator
xlFilterValues = 7
# https://docs.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.varianttype?view=net-6.0
arrayId = 8192

fltrs = win32.VARIANT(arrayId,["uno","dos"])
mv_sht.UsedRange.AutoFilter(Field=15,Criteria1=f'<>Array{fltrs}',Operator=xlFilterValues)

when i run this, it returns 15.0, i'm guessing because that's the field. I've tried all variations i could think of, as well as looking at other stackoverflows - but there isn't much info on this.

best i found: Excel VBA Autofilter error when using criteria2 http://timgolden.me.uk/pywin32-docs/html/com/win32com/HTML/variant.html

but doens't apply it to AutoFilter

Thanks!

Answer 1

From various sources, it seems that you cannot filter out multiple criteria (i.e. exclude them from a filter).

You have to remove them from the sheet. So an array with <>'s doesn't work.

xlCellTypeVisible = 12
xlShiftUp = -4162
cols = mv_sht.UsedRange.Columns.Count
    
for owner in ["uno","dos"]:
    mv_sht.UsedRange.AutoFilter(Field=15, Criteria1=owner)
    lastRow = mv_sht.Cells.Find("*", SearchOrder=1, SearchDirection=2).Row
    mv_sht.Range(mv_sht.Cells(2, 1), mv_sht.Cells(lastRow, cols)).SpecialCells(Type=xlCellTypeVisible).Delete(
            Shift=xlShiftUp)

So, I just looped over each value of the array, filtered the sheet, and deleted those rows.

This seems terribly inefficient though.

Answer 2

I don't understand why you are quoting the array like that.
The call on VBA should look like this

RangeObjectToFilter.AutoFilter 
  Field:=ColumnNumber, 
  Criteria1:=ArrayMultipleCriteria, 
  Operator:=xlFilterValues

And I would try to declare the array like this:

ArrayMultipleCriteria = Array(10, 20, 30, "SomeValue")

You have to adjust the python code accordingly and I believe this should do the trick:

mv_sht.UsedRange.AutoFilter(Field=15,Criteria1=f'Array({', '.join(fltrs)})',
  Operator=xlFilterValues)