python可以添加,运行&无需中间保存步骤即可删除VBA宏以实现卓越效果? [英] Can python add, run & remove a VBA macro to excel without intermediate saving steps?
问题描述
我有一个长期运行的python管道,该管道生成一个pandas数据框.简要地说,我想:
I have a long-running python pipeline that generates a pandas dataframe. Briefly, I want to:
- 在excel中显示熊猫数据框
- 添加并添加运行VBA宏
- 删除VBA宏,并将(新格式化的)输出保存为
.xslx
面临的挑战是,我无法弄清楚如何没有中间的另存为xlsm
-文件" 步骤,这显然是将VBA宏添加到.xlsx
文件.由于效率低下,因此我想摆脱这一中间步骤.
The challenge is that I can't figure out how to do this without an intermediate save-as-xlsm
-file step, which is apparently required when adding a VBA macro to an .xlsx
file. Since this is inefficient, I want to get rid of this intermediate step.
这是代码:
1.在excel中显示熊猫数据框:
with pd.ExcelWriter('output.xlsx') as writer:
df_results.to_excel(writer, index = False, sheet_name = "Sheet1")
#...see below
2A.添加宏并分配.xlsm文件名,以便编写者可以保存宏
#... see above
writer.book.filename = 'output.xlsm' # Add .xlsm filename
writer.book.add_vba_project('VBA_script.bin') # This adds my macro
writer.save() # How to get rid of this step?
2B.运行宏
xl = win32com.client.Dispatch("Excel.Application") # Set up excel
xl.Workbooks.Open(Filename = 'output.xlsm') # Open .xlsm file from step 2A
xl.Application.Run("Module1.Main") # Run VBA_macro.bin
3.删除宏并保存到.xlsx
wb = xl.ActiveWorkbook
xl.DisplayAlerts = False
wb.DoNotPromptForConvert = True
wb.CheckCompatibility = False
wb.SaveAs('final_outfile.xlsx', FileFormat=51, ConflictResolution=2) # Macro disapears here
xl.Application.Quit()
del xl
xl = None
是否可以在没有中间.xlsm步骤&的情况下执行此操作用较短的代码?
Is it possible to do this without intermediate .xlsm step & with shorter code?
如何添加,运行&在excel上下文中删除VBA宏,而无需使用python进行中间保存步骤?
推荐答案
是的,创建实例,使用内置的VBA代码导入,运行宏,另存为xlsx来删除宏.
Yes, create the instance, use the built in VBA Code Import, run macro, save as xlsx to remove macro.
xl = win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Add
wb = xl.ActiveWorkbook
wb.VBProject.VBComponents.Import "Full Path\VBA_script.bin"
xl.Application.Run("Module1.Main")
xl.DisplayAlerts = False
wb.DoNotPromptForConvert = True
wb.CheckCompatibility = False
wb.SaveAs('final_outfile.xlsx', FileFormat=51, ConflictResolution=2) # Macro disapears here
xl.Application.Quit()
del xl
xl = None
这篇关于python可以添加,运行&无需中间保存步骤即可删除VBA宏以实现卓越效果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!