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 osimport win32com.clientsXlFile = "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 closesdel xl

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 winregimport osimport win32com.clientdef 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 oldif 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 thatOffice\14.0 is present.

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

• python
• excel
• vba