73

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
Improve this question
7
  • 1
    Maybe don't use the same name for both the module and the method. Commented Oct 27, 2013 at 16:46
  • What's the macro definition? is it declared like Public sub testMacro or Private? Commented Oct 27, 2013 at 23:24
  • Do you need to run the macro from Python? Could easily just do the whole thing in Python anyway. Also use something like del xl rather than xl=0 to properly get rid of the reference to the object. Commented Oct 28, 2013 at 15:17
  • @TimWilliams elaborate? Commented Oct 29, 2013 at 1:44
  • @enderland Neither, It's just Sub testMacro Commented Oct 29, 2013 at 1:44

9 Answers 9

Reset to default
97
+100

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

This is starting up Excel in the background using win32com, so it would not work on Linux.
what should I do if my macro is saved somewhere else .xlsm and the file on which I have to run is another xls file
@NimishBansal, did you figure out a solution? I have a similar problem as well. One way, I can think of is to open both the xls and the xlsm file, just like you would do when you open a excel document manually..but haven't tried it out
@cole isn't running VBA/.xlsm on Linux a total non-starter?
Right. Definitely a non-starter :) If I recall correctly, someone asked about whether it would work on linux... otherwise I have no idea why I wrote that.
|
13

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
Improve this answer

1 Comment

Tested this on python 3.6, works like charm. Minor input, in my case "ThisWorkbook.Template2G" didn't work, rather giving the macro name directly worked. example: excel_macro.Application.Run("Template2G")
6

Just a quick note with a xlsm with spaces. 

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

Comments

3

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
Improve this answer

1 Comment

Is the enable vba model check box below in that same screen checked? Also ad you dere trying to generate a macro from python in a previous question, have you checked from Excel the xlsm file you open really contains the macro?
1

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

Improve this answer

1 Comment

This shouldn't be necessary. The macroname is not necessarily associated with a specific sheet. But are you trying to run macroname on a specific sheet named sheet_name?
1

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
Improve this answer

1 Comment

If I have multiple Sub s in Module1, should I include each name per time ?
0

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
Improve this answer

Comments

0

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
Improve this answer

Comments

-2

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.