How to call Excel VBA functions and subs using Python win32com?

python excel vba win32com
Yulia V picture Yulia V · Feb 6, 2013 · Viewed 13.5k times · Source

My Excel workbook contains VBA subs and macros similar to those below; they sit in Module1.

How to call them using Python win32com module?

Public Sub setA1(ByVal s As String)
    ThisWorkbook.ActiveSheet.Range("A1").Value = s
End Sub

Public Function getA1() As String
    getA1 = ThisWorkbook.ActiveSheet.Range("A1").Value
End Function

Many thanks in advance!

Answer

Yulia V picture Yulia V · Feb 6, 2013 
import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename="c:\\temp\\book1.xls",ReadOnly=1)
xl.Application.Run("setA1", '4')
res = xl.Application.Run("getA1")
print res
xl = 0

Just as simple as this ....

Related questions

How to call python script on excel vba?

trying to call a python script on Vba and I am a newb. I tried converting the main script to an exe using py2exe and then calling it from VBA (shell) but the main script calls other scripts therefore …

Read More
Using Python to program MS Office macros?

I've recently taken it as a project to teach myself how to program in Python. Overall, I must say that I'm impressed with it. In the past I've typically stuck to programming in VBA mostly for MS Excel (but also …

Read More
Creating pivot table in Excel using python causes pywintypes.com_error

I adapted the following code found here to create a pivot table in my existing excel sheet: import win32com.client as win32 win32c = win32.constants import sys import itertools tablecount = itertools.count(1) def addpivot(wb,sourcedata,title,filters=(),…

Read More