Pywin32 Working directly with Columns and Rows like it would be done in VBA

I am starting to migrate some of my VBA code into Python, because it gives me more resources. However I am having troubles to work in similar way.

I wanted to clear some Columns, however I cannot work directly with the attribute Columns of the worksheet. And I have to do turnarounds like this (full code):

import win32com.client as win32

xl = win32.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True

wb = xl.Workbooks.Open(r'Test.xlsx')
sh = wb.ActiveSheet

xl.ScreenUpdating = True
last_col = sh.UsedRange.Columns.Count
last_row = sh.UsedRange.Rows.Count
my_range = sh.Range(sh.Cells(1, 1), sh.Cells(last_row, 4))
my_range.Select()
xl.Selection.ClearContents()

So, what I would like to do, is like in VBA:

Columns("A:D").Clear

Is there any way to work with rows and columns with Python, and not only with ranges?

Solution

Simply run: sh.Columns("A:D").Clear. It should be noted that you are not translating VBA code to Python. Both languages are doing the same thing in making a COM interface to the Excel Object library. Because the Office applications ships with VBA, it is often believed that VBA is the language for these softwares and the language is natively built-in but VBA is actually a component. Under Tools/References in IDE, you will see VBA is first checked item.

So, with the Windows Component Object Model (COM), any general purpose language including Python, PHP, R, Java, C#, etc. can connect to the Excel Object Library and make calls to workbook objects and methods. You just have to adhere to the languages' syntax. As example:

Python (win32com module)

sh.Columns("A:D").Clear()

PHP (COM class)

sh->Columns("A:D")->Clear()

R (RDCOMClient module)

sh$Columns("A:D")$Clear()

Also, consider running your code in try...except...finally blocks to catch exceptions and properly uninitialize COM objects regardless if your code errors out or not. Otherwise, the Excel.exe process will continue running in background.

import win32com.client as win32

try:
    xl = win32.gencache.EnsureDispatch('Excel.Application')
    xl.Visible = True

    wb = xl.Workbooks.Open(r'C:\Path\To\Test.xlsx')
    sh = wb.ActiveSheet

    xl.ScreenUpdating = True
    sh.Columns("A:D").Clear()

except Exception as e:
    print(e)

finally:
    sh = None
    wb = None
    xl = None