pythonxlwings

Unable to set AutoFilter in .xlsx spreadsheet using xlwings


Using xlwings I'm trying to set an Autofilter on column E of a spreadsheet looking for text "DJM". My code just hangs at the AutoFilter line and does not do anything.

The code snippet is below:

import xlwings as xb
filename = r"djm.xlsx"    
wb = xb.Book(filename)
ws = xb.sheets("BCP")
totalrows = ws.range(1, 1).end('down').row
colrange = "E1:E" + str(totalrows)
ws.api.range(colrange).Autofilter(Field := 5, Criteria1 := "DJM")
wb.close()

I've searched multiple sites, watched multiple videos and I appear to be doing everything right. I'm using PyCharm and Windows 11.


Solution

  • Thank you everyone - this snippet works:

    import xlwings as xw
    
    if __name__ == '__main__':
        # Set the filepath to the spreadsheet
        filename = r"C:\Temp\djm.xlsx"
        # Open the spreadsheet
        wb = xw.Book(filename)
        # Select the required worksheet
        ws = wb.sheets("BCP")
        # Get the total number of rows from the worksheet
        totalrows = ws.range(1, 1).end('down').row
        # Create the range string using the 'totalrows' variable
        setrange = "A1:F" + str(totalrows)
        # Set the Autofilter on the required worksheet
        ws.api.Range(setrange).AutoFilter(Field := 5, Criteria1 := "DJM")