Appliying a VBA macro to an Excel file using win32com python library

Question

Im triying to apply a VBA macro using python and the win32com library, the idea is creating a fixed excel file, storing a macro and then running it. I took the idea from here: https://redoakstrategic.com/pythonexcelmacro/

The problem comes when the script have to work with the xlsm file and running the macro with the Application.Run(). My code is:

import pandas as pd 
import win32com.client 
import os

#creating the dataframe
df = df1=pd.read_excel ("normal_excel_file.xlsx", index_col=0)
filename = "normal_excel_file.xlsx"
writer = pd.ExcelWriter(filename, engine='xlsxwriter')
df.to_excel(writer, sheet_name='data', index=False)

#copiying and renaming the file to xlsm
shutil.copy("normal_excel_file.xlsx", "(1)normal_excel_file.xlsx")
os.rename("(1)normal_excel_file.xlsx", "macros_excel_file.xlsm")

#adding the macro to the workbook
filename_macro = r"C:\Users\John\Desktop\Python_scripts\Running VBA Macro\macros_excel_file.xlsm"
workbook = writer.book
workbook.filename = filename_macro
workbook.add_vba_project('vbaProject.bin')
writer.save()

And the conflicting part:

 if os.path.exists(filename_macro):
        xl = win32com.client.Dispatch('Excel.Application')
        xl.Workbooks.Open(Filename = filename_macro, ReadOnly=1)

        #assuming that there is only one macro, and is stored as "ThisWorkbook.Macro1" in the file

        xl.Application.Run("ThisWorkbook.Macro1") #I also try using only "Macro1" and the whole path of the file
        xl.Application.Quit()
        del xl

And i get the next error. First the error message:

com_error: (-2147352567, 'An exception occurred.', (0, 'Microsoft Excel', 'The "ThisWorkbook.Macro1" macro cannot be executed. The macro may not be available in this book or all of them may have been disabled. the macros. ',' xlmain11.chm ', 0, -2146827284), None)

And the whole error text:

com_error                                 Traceback (most recent call last)
<ipython-input-16-6e91b8d2f622> in <module>
      3     xl = win32com.client.Dispatch('Excel.Application')
      4     xl.Workbooks.Open(Filename = filename_macro, ReadOnly=1)
----> 5     xl.Application.Run("ThisWorkbook.Macro1")
      6     xl.Application.Quit()
      7     del xl

~\Anaconda3\lib\site-packages\win32com\client\dynamic.py in Run(self, Macro, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

~\Anaconda3\lib\site-packages\win32com\client\dynamic.py in _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args)
    285 
    286         def _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args):
--> 287                 result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
    288                 return self._get_good_object_(result, user, resultCLSID)
    289 

com_error: (-2147352567, 'An exception occurred.', (0, 'Microsoft Excel', 'The "ThisWorkbook.Macro1" macro cannot be executed. The macro may not be available in this book or all of them may have been disabled. the macros. ',' xlmain11.chm ', 0, -2146827284), None)

I go to the Trust Center, in Microsoft Office, and allow all macro types (https://support.office.com/en-us/article/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6?ui=en-US&rs=en-US&ad=US). But the error continues to happen

If anyone know how to fix it would be awesome

Thanks all

If you open the created xlsm file in Excel, in VBA developer mode, do you get an error? If not, is there a macro called Macro1 and is the workbook object called ThisWorkbook? — jmcnamara, Sep 22 '19 at 22:19
What does macro even do that you cannot write a Python win32com function? — Parfait, Sep 22 '19 at 22:47
hi @jmcnamara, when I try to access the xlsm file shows me this message /// "Excel cannot open the file 'filename.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."/// When go back and reconvert my file to xlsx i can open it normally, but because of being a xlsx format I cannot check the Macros — Posdam, Sep 24 '19 at 05:44
@Parfait for example the possibility of dropdown cells or the drag and drop construct sistem of the pivot tables in Excel. Im doing this for reporting to non technical people — Posdam, Sep 24 '19 at 05:49
Anything of the Excel object model can be handled in a COM connected language like Python. Remember VBA also makes a COM connection and has nothing to do with Excel. Under, Tools\References, VBA is first object checked. Therefore you should be able to translate even avoid security issues of `.xlsm`. — Parfait, Sep 24 '19 at 12:25

Parfait · Accepted Answer · 2019-09-25T20:44:08.990

Because .xlsx to .xlsm are fundamentally different types of binary files, you cannot simply copy and rename using lines:

shutil.copy("normal_excel_file.xlsx", "(1)normal_excel_file.xlsx")
os.rename("(1)normal_excel_file.xlsx", "macros_excel_file.xlsm")

(strangely, your tutorial link does not show how the Pandas generated .xlsx file becomes the .xlsm in the subsequent attached macro step.)

Instead, use the Excel writer object to migrate macro and save as .xlsm. Also, use with context manager on writer object to effectively close the i/o object after processing.

# creating the dataframe
path = "C:\Users\John\Desktop\Python_scripts\Running VBA Macro"
filename = "normal_excel_file.xlsx"
filename_macro = "macros_excel_file.xlsm"

df = pd.read_excel(filename, index_col=0)
with pd.ExcelWriter(filename, engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='data', index=False)
    workbook = writer.book
    workbook.filename = filename_macro
    workbook.add_vba_project('vbaProject.bin')
    writer.save()

# RUN MACRO
if os.path.exists(os.path.join(path, filename_macro)):
    wb = xl.Workbooks.Open(Filename = filename_macro, ReadOnly=1)
    xl.Application.Run("ThisWorkbook.Macro1")
    xl.Application.Quit()

awesome, and also makes sense. Thanks a lot. One thing, when I tried to run your code I get the following error /// Invalid extension for engine 'xlsxwriter': 'xlsm' /// in the "# adding the macro to the workbook" part. I will investigate it in the end of the day, in several hours, but may be you can illustrate me — Posdam, Sep 25 '19 at 05:48
See this [solution](https://stackoverflow.com/a/28170939/1422451) which apparently you do not need `shutil` at all and hence `SaveAs`. See my edit. — Parfait, Sep 25 '19 at 20:41
Killer. It works. Im so grateful. You are my hero right now. Thanks a lot — Posdam, Sep 26 '19 at 17:50

Appliying a VBA macro to an Excel file using win32com python library

1 Answers1