Pywin32 Working directly with Columns and Rows like it would be done in VBA
<p>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.</p>

<p>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):</p>

<pre><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()
</code></pre>

<p>So, what I would like to do, is like in VBA:</p>

<pre><code>Columns("A:D").Clear
</code></pre>

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

<p>Simply run: <code>sh.Columns("A:D").Clear</code>. 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 <em>is</em> 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. </p>

<p>So, with the Windows <a href="https://msdn.microsoft.com/en-us/library/windows/desktop/ms680573(v=vs.85).aspx" rel="nofollow">Component Object Model (COM)</a>, 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:</p>

<p><strong>Python</strong> <em>(win32com module)</em></p>

<pre class="lang-py prettyprint-override"><code>sh.Columns("A:D").Clear()
</code></pre>

<p><strong>PHP</strong> <em>(<a href="http://php.net/manual/en/class.com.php" rel="nofollow">COM</a> class)</em></p>

<pre class="lang-php prettyprint-override"><code>sh-&gt;Columns("A:D")-&gt;Clear()
</code></pre>

<p><strong>R</strong> <em>(<a href="http://www.omegahat.net/RDCOMClient/Docs/introduction.html" rel="nofollow">RDCOMClient</a> module)</em></p>

<pre class="lang-r prettyprint-override"><code>sh$Columns("A:D")$Clear()
</code></pre>

<hr>

<p>Also, consider running your code in <code>try...except...finally</code> 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.</p>

<pre><code>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
</code></pre>

python excel win32com