Running VBA code from Python: macros may be disabled

Question

Trying to run an Excel macro via Python I get the following error:

    Traceback (most recent call last):
    File ".\test.py", line 17, in <module>
        xlApp.Application.Run(MACRO)
    File "<COMObject <unknown>>", line 14, in Run
    File "C:\Users\twauchop\Desktop\Python\virtual_envs\gutenberg\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 'test'. The macro may not be available in this workbook or all macros may be disabled.", 'xlmain11.chm', 0, -2146827284), None)

I tried many of the fixes suggested in other questions.

I tried 'xlApp.Application.Run(wb.name + "!" + MACRO)'.

Is this a naming convention issue? I enabled everything via Trust Center and changed the VBA sub to public.

As a side-note, I also cannot run macros from the programmatically opened workbook (i.e. if I try manually). If I open the workbook manually, however, everything is fine.

I am running Python 3.6.5 on a 64 bit system, Windows 10.

Python:

    import win32com.client
    import os
    import traceback

    DIRECTORY = r'C:\\Users\\twauchop\\Desktop\\Excel\\'
    FILE = 'test.xlsb'
    MACRO = 'test'   
    path = os.path.join(DIRECTORY, FILE)

    if os.path.exists(path):
        try:
            xlApp = win32com.client.Dispatch('Excel.Application')
            xlApp.DisplayAlerts = False
            xlApp.Visible = True
            wb = xlApp.Workbooks.Open(Filename=path, ReadOnly=1)
            xlApp.Application.Run(MACRO)
            wb.Close(SaveChanges=1)
            xlApp.Application.Quit()
            print('Code ran successfully.')

        except:
            print('An error was encountered; see traceback.')
            print(traceback.format_exc())
            xlApp.Quit()

VBA:

Public Sub test()
MsgBox "Hello World!"
End Sub

I don't understand. Why try to run a VBA macro through python? I can't picture a suitable use case. Why not open the document in the native application? Can also set up Office documents with Workbook_Open routines to do what is wanted here. — AJD
– AJD, Commented Apr 23, 2018 at 6:51
have you tried without ReadOnly=1 when you open the workbook? your code works fine for me even with ReadOnly=1 but maybe it prevents you to use the macro with your settings — Ben.T
– Ben.T, Commented Apr 24, 2018 at 12:53
AJD: Setting up some ETL jobs and want to automate part of the data retrieval process. One of our service providers doesn't have an open API, but does offer some spreadsheets with a built-in macro that handles the curl request to their servers. Rather than reverse engineer that, I was hoping to just leverage the existing functionality. I work in the financial services industry. — Talin Wauchope
– Talin Wauchope, Commented Apr 25, 2018 at 5:32
Ben.T: I think I may have already tried that, but will try again to be sure. Thanks for the suggestion. — Talin Wauchope
– Talin Wauchope, Commented Apr 25, 2018 at 5:35

godGivesMeLanguages · Accepted Answer · 2022-11-21 01:23:29Z

1

xlApp.Application.AutomationSecurity=1 needs to go before ANY xlApp.Application.Run(excelMacroNameHere) code, as the AutomationSecurity is used to control (enable vs disable) macros and 1 means enable all macros.

answered Nov 21, 2022 at 1:23

godGivesMeLanguages

731 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

Running VBA code from Python: macros may be disabled

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related