使用 win32com python 库将 VBA 宏应用于 Excel 文件

Question

我尝试使用 python 和 win32com 库应用 VBA 宏，想法是创建一个固定的 excel 文件，存储一个宏然后运行它。我从这里得到了这个想法：https ://redoakstrategic.com/pythonexcelmacro/

当脚本必须使用 xlsm 文件并使用 Application.Run() 运行宏时，就会出现问题。我的代码是：

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()

和冲突的部分：

 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

我得到下一个错误。首先是错误信息：

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)

我转到 Microsoft Office 中的信任中心，并允许所有宏类型（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 )。但是错误继续发生

如果有人知道如何解决它会很棒

Answer 1

因为.xlsxto.xlsm是根本不同类型的二进制文件，所以不能简单地使用行复制和重命名：shutil.copy("normal_excel_file.xlsx", "(1)normal_excel_file.xlsx")os.rename("(1)normal_excel_file.xlsx", "macros_excel_file.xlsm")（奇怪的是，您的教程链接没有显示 Pandas 生成的.xlsx文件.xlsm在后续附加的宏步骤中是如何变成的。）相反，使用 Excel 编写器对象来迁移宏并另存为.xlsm. 此外，在编写器对象上使用上下文with管理器在处理后有效地关闭 i/o 对象。# creating the dataframepath = "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 MACROif 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()