使用 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 dataframedf = 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 xlsmshutil.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 workbookfilename_macro = r"C:\Users\John\Desktop\Python_scripts\Running VBA Macro\macros_excel_file.xlsm"workbook = writer.bookworkbook.filename = filename_macroworkbook.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 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()