How to run an Excel macro using a Python script?

Question

I want to run a macro in an Excel file using a Python script.

import os
import win32com.client

if os.path.exists(""C:\\Users\\siddharth.mungekar\\Desktop\\MemleakTest\\test.xlsm"):
    xl = win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(Filename="C:\\Users\\siddharth.mungekar\\Desktop\\MemleakTest\\test.xlsm")
    xl.Visible = True
    xl.Run('Mem_Leak')

The script fails after opening an Excel file and gives the following error:

C:\Users\siddharth.mungekar\AppData\Local\Programs\Python\Python36\python.exe C:/Users/siddharth.mungekar/PycharmProjects/MacroViaPython/ExcelMacroEnabler.py Traceback (most recent call last): File "C:/Users/siddharth.mungekar/PycharmProjects/MacroViaPython/ExcelMacroEnabler.py", line 14, in xl.Run('Mem_Leak') File "", line 14, in Run File "C:\Users\siddharth.mungekar\AppData\Local\Programs\Python\Python36\lib\site-packages\win32com\client\dynamic.py", line 287, in ApplyTypes result = self.oleobj.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args) pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Cannot run the macro 'Mem_Leak'. The macro may not be available in this workbook or all macros may be disabled.", 'xlmain11.chm', 0, -2146827284), None)

I tried modifying the Trust Centre setting.

The code works when another Excel file has already been manually opened.

I tried opening a dummy Excel file and then opening the target Excel file and running the macro. This did not work. The dummy excel file has to be opened manually.

ClemP · Accepted Answer · 2018-12-03 13:31:43Z

1

I fixed this issue by putting my macro inside a new module instead of having it in "ThisWorkbook" and by calling:

xl.Run("Module1.UpdateTable")

Full code:

import os
import win32com.client

sXlFile = "C:\\Desktop\\test.xlsm"
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(os.path.abspath(sXlFile))
try:
  xl.Run("Module1.UpdateTable")
except Exception as e:
  print(e)

xl.Application.Quit() # Comment this out if your excel script closes
del xl

answered Dec 3, 2018 at 13:31

ClemP

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

user3015729 Over a year ago

This is absolutely helping :-)

Martin Evans · Accepted Answer · 2018-05-11 11:19:59Z

0

You could try updating the Macro settings in the registry to allow all macros to run. You would need to either run your Python script as an administrator, or ensure that the registry key has suitable permissions:

import winreg
import os
import win32com.client


def registry_set_key(hive, regpath, key, type, value):
    try:
        hkey = winreg.OpenKey(hive, regpath, 0, winreg.KEY_ALL_ACCESS)
    except FileNotFountError as e:
        hkey = winreg.CreateKey(hive, regpath, 0, winreg.KEY_ALL_ACCESS)

    try:
        old = winreg.QueryValueEx(hkey, key)
    except:
        old = None

    winreg.SetValueEx(hkey, key, 0, type, value)
    winreg.CloseKey(hkey)    

    return old



if os.path.exists(r"C:\\Users\\siddharth.mungekar\\Desktop\\MemleakTest\\test.xlsm"):

    # Set macro settings to 1 to allow all macros    
    old = registry_set_key(winreg.HKEY_CURRENT_USER, r'Software\Microsoft\Office\14.0\Excel\Security', 'VBAWarnings', winreg.REG_DWORD, 1)

    xl = win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(Filename="C:\\Users\\siddharth.mungekar\\Desktop\\MemleakTest\\test.xlsm")
    xl.Visible = True
    xl.Run('Mem_Leak')

    # If there was an existing value, put it back
    if old:
        registry_set_key(winreg.HKEY_CURRENT_USER, r'Software\Microsoft\Office\14.0\Excel\Security', 'VBAWarnings', winreg.REG_DWORD, old[0])

Note, you might need to adjust the registry path depending on the version of Office installed, use REGEDIT to check. i.e. make sure that Office\14.0 is present.

Also take a look at Microsoft Project – how to control Macro Settings using registry keys for more information.

edited May 11, 2018 at 11:19

answered May 11, 2018 at 8:53

Martin Evans

46.9k17 gold badges88 silver badges104 bronze badges

4 Comments

Siddharth Mungekar Over a year ago

Thanks, this isn't working in my case though. Can you suggest something else? Another query, why is the script working when an excel file is manually opened before running it? And why isn't it working if I open a dummy excel file with the same script before opening the target excel file and running the macro?

Martin Evans Over a year ago

Is your macro defined as Private or Public ?

Martin Evans Over a year ago

Switching to Public helps in some cases. I don't know why manually opening helps, sorry.

Arnold Souza Over a year ago

hello @SiddharthMungekar, I believe I have the same problem as you. If I manually try to run the macro it works. If I try to call it using Python an com_error is raised. Did you find a way to get over it?

Collectives™ on Stack Overflow

How to run an Excel macro using a Python script?

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related