powerbi 中用到离线表excel的数据清洗,发现使用VBA比python处理Excel方便,快速。
VBA里面定义一个函数名macro,直接在python中调用执行对应的source 文件。
def run_macro(macro,source1="", source2=""):
try:
xlApp = win32com.client.DispatchEx('Excel.Application')
xlApp.Visible = True
xlsPath = os.path.expanduser(r'D:\Macro_Htm.xlsm')
wb = xlApp.Workbooks.Open(Filename=xlsPath)
xlApp.Run(r'Macro_Htm.xlsm!'+macro, source1, source2)
xlApp.Quit()
print("Macro ran successfully!")
except Exception as e:
print(e)
xlApp.Quit()
如果excel不方便写关闭保存,在python里加上wb.save() try: xlApp = win32com.client.DispatchEx('Excel.Application') xlApp.Visible = True xlsPath = os.path.expanduser(r'11C\11.xlsm') wb = xlApp.Workbooks.Open(Filename=xlsPath) xlApp.Run(r'111.xlsm!'+macro, source1) wb.Save() xlApp.Quit() print("Macro ran successfully!") except Exception as e: print(e) xlApp.Quit()