起因
excel 运行宏的时候时间有点长,二十多秒,期间程序会出现假死,python调用vba时会报错。
解决办法,写一个vba在目录下生成一个txt,python用while去循环检测,如果有文件执行保存关闭excel命令,没有的话延时等待vba运行结束。
vba建txt代码
Sub create_txt()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile("D:\code\cq macro\vba.txt", True, True)
oFile.WriteLine "test"
oFile.Close
Set fso = Nothing
Set oFile = Nothing
End Sub
python代码
import win32com.client, time, os
start = time.time()
def check_txt_exist():
if os.path.exists(r"D:\code\cq macro\vba.txt"):
try:
os.remove(r"D:\code\cq macro\vba.txt")
except:
pass
print(1)
return True
else:
print('not exist')
return False
def useVBA(VBA):
try:
os.remove(r"D:\code\cq macro\vba.txt")
except:
pass
xlApp = win32com.client.DispatchEx("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = 0
# 宏所在的xls
xlApp.Workbooks.Open(r"D:\code\cq macro\combine2.xlsm", False)
#需要执行宏的xls
xlBook = xlApp.Workbooks.Open(file_path,False)
xlBook.Application.Run(VBA) #宏
txt_exist = False
while not txt_exist:
txt_exist = check_txt_exist()
time.sleep(1)
print(type(xlBook.Close(True)))
xlApp.quit()
file_path = r"D:\code\cq macro\combine.xls"
# 宏的文件名!模块名.函数名
useVBA(r"combine2.xlsm!Module1.MRPFomart")
end = time.time()
total_time = end - start
print('total time', total_time)
# useVBA(r"mac")