CODEWITHSUNDEEP
pythonexcelvbapython-2.7
Ryflex
Ryflex

Reputation: 5769

Running an Excel macro via Python?

I'm trying to run a macro via python but I'm not sure how to get it working...

I've got the following code so far, but it's not working.

import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename="C:\test.xlsm",ReadOnly=1)
xl.Application.Run("macrohere")
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0

I get the following traceback:

Traceback (most recent call last):
  File "C:\test.py", line 4, in <module>
    xl.Application.Run("macrohere")
  File "<COMObject <unknown>>", line 14, in Run
  File "C:\Python27\lib\site-packages\win32com\client\dynamic.py", line 282, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u"Cannot run the macro 'macrohere'. The macro may not be available in this workbook or all macros may be disabled.", u'xlmain11.chm', 0, -2146827284), None)

EDIT

import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename="C:\test.xlsm",ReadOnly=1)
try:
    xl.Application.Run("test.xlsm!testmacro.testmacro")
    # It does run like this... but we get the following error:
    # Traceback (most recent call last):
        # File "C:\test.py", line 7, in <module>
        # xl.Workbooks(1).Close(SaveChanges=0)
        # File "C:\Python27\lib\site-packages\win32com\client\dynamic.py", line 192, in __call__
        # return self._get_good_object_(self._oleobj_.Invoke(*allArgs),self._olerepr_.defaultDispatchName,None)
    # com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
except:
    # Except isn't catching the above error... :(
    xl.Workbooks(1).Close(SaveChanges=0)
    xl.Application.Quit()
    xl=0

Upvotes: 71

Views: 207210

Answers (9)

MT1
MT1

Reputation: 974

I am using Windows 22H2 Build 22621.1194 64 bit

Python 3.11.2

Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20186) 64-bit

I needed python -m pip install pywin32 for the win32com.client

Python code

import time
import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename=r"C:\Users\david\Desktop\xl_HW.xlsm",ReadOnly=1)
xl.Application.Run("sbHelloWorld")
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0
time.sleep(10)

The module name was not needed, because the subroutine was declared Public

and the routine in VBA is -

Public Sub sbHelloWorld()
    MsgBox "Hello World"
End Sub

Upvotes: 0

abautista
abautista

Reputation: 2780

I did some modification to the SMNALLY's code so it can run in Python 3.5.2. This is my result:

    #Import the following library to make use of the DispatchEx to run the macro
    import win32com.client as wincl

    def runMacro():

        if os.path.exists("C:\\Users\\Dev\\Desktop\\Development\\completed_apps\\My_Macr_Generates_Data.xlsm"):

        # DispatchEx is required in the newest versions of Python.
        excel_macro = wincl.DispatchEx("Excel.application")
        excel_path = os.path.expanduser("C:\\Users\\Dev\\Desktop\\Development\\completed_apps\\My_Macr_Generates_Data.xlsm")
        workbook = excel_macro.Workbooks.Open(Filename = excel_path, ReadOnly =1)
        excel_macro.Application.Run\
            ("ThisWorkbook.Template2G")
        #Save the results in case you have generated data
        workbook.Save()
        excel_macro.Application.Quit()  
        del excel_macro

Upvotes: 13

Peter Mabbott
Peter Mabbott

Reputation: 125

A variation on SMNALLY's code that doesn't quit Excel if you already have it open:

import os, os.path
import win32com.client
    
if os.path.exists("excelsheet.xlsm"):
    xl=win32com.client.Dispatch("Excel.Application")
    wb = xl.Workbooks.Open(os.path.abspath("excelsheet.xlsm"), ReadOnly=1) #create a workbook object
    xl.Application.Run("excelsheet.xlsm!modulename.macroname")
    wb.Close(False) #close the work sheet object rather than quitting excel
    del wb
    del xl

Upvotes: 1

coldbyte
coldbyte

Reputation: 76

Just a quick note with a xlsm with spaces. 

file = 'file with spaces.xlsm'
excel_macro.Application.Run('\'' + file + '\'' + "!Module1.Macro1")

Upvotes: 5

SMNALLY
SMNALLY

Reputation: 1381

I would expect the error is to do with the macro you're calling, try the following bit of code:

Code

import os, os.path
import win32com.client

if os.path.exists("excelsheet.xlsm"):
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(os.path.abspath("excelsheet.xlsm"), ReadOnly=1)
    xl.Application.Run("excelsheet.xlsm!modulename.macroname")
##    xl.Application.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
    xl.Application.Quit() # Comment this out if your excel script closes
    del xl

Upvotes: 95

PyBoss
PyBoss

Reputation: 631

For Python 3.7 or later,(2018-10-10), I have to combine both @Alejandro BR and SMNALLY's answer, coz @Alejandro forget to define wincl.

import os, os.path
import win32com.client
if os.path.exists('C:/Users/jz/Desktop/test.xlsm'):
    excel_macro = win32com.client.DispatchEx("Excel.Application") # DispatchEx is required in the newest versions of Python.
    excel_path = os.path.expanduser('C:/Users/jz/Desktop/test.xlsm')
    workbook = excel_macro.Workbooks.Open(Filename = excel_path, ReadOnly =1)
    excel_macro.Application.Run("test.xlsm!Module1.Macro1") # update Module1 with your module, Macro1 with your macro
    workbook.Save()
    excel_macro.Application.Quit()  
    del excel_macro

Upvotes: -2

William Tong
William Tong

Reputation: 475

I tried the win32com way and xlwings way but I didn't get any luck. I use PyCharm and didn't see the .WorkBook option in the autocompletion for win32com. I got the -2147352567 error when I tried to pass a workbook as variable.

Then, I found a work around using vba shell to run my Python script. Write something on the XLS file you are working with when everything is done. So that Excel knows that it's time to run the VBA macro.

But the vba Application.wait function will take up 100% cpu which is wierd. Some people said that using the windows Sleep function would fix it.

 Import xlsxwriter


 Shell "C:\xxxxx\python.exe 
 C:/Users/xxxxx/pythonscript.py"

 exitLoop = 0

wait for Python to finish its work.

  Do

  waitTime = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 30)
  Application.Wait waitTime
  Set wb2 = Workbooks.Open("D:\xxxxx.xlsx", ReadOnly:=True)
  exitLoop = wb2.Worksheets("blablabla").Cells(50, 1)
  wb2.Close exitLoop

  Loop While exitLoop <> 1




  Call VbaScript

Upvotes: 0

user1839053
user1839053

Reputation:

Hmm i was having some trouble with that part (yes still xD):

xl.Application.Run("excelsheet.xlsm!macroname.macroname")

cos im not using excel often (same with vb or macros, but i need it to use femap with python) so i finaly resolved it checking macro list: Developer -> Macros: there i saw that: this macroname.macroname should be sheet_name.macroname like in "Macros" list.

(i spend something like 30min-1h trying to solve it, so it may be helpful for noobs like me in excel) xD

Upvotes: 1

Zeugma
Zeugma

Reputation: 32085

I suspect you haven't authorize your Excel installation to run macro from an automated Excel. It is a security protection by default at installation. To change this:

  1. File > Options > Trust Center
  2. Click on Trust Center Settings... button
  3. Macro Settings > Check Enable all macros

Upvotes: 3

Related Questions