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!
CodePudding user response:
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)
CodePudding user response:
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.