通过Python脚本实现VBA宏

Question

我试图通过python脚本执行宏。当我第一次运行它时，它运行正常，但当我在几分钟后再次运行它时，它会抛出错误

File "<COMObject <unknown>>", line 2, in run pywintypes.com_error:
(-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

我的剧本：

import win32com.client as win32
import  time
excel=win32.Dispatch("Excel.Application")
excel.visible=1
book=excel.Workbooks.open('C:\\Users\\C296442\\Documents\Application07012020.xlsm')
excel.application.run("Application07012020.xlsm!Sheet5.CommandButton12_Click")

Answer 1

我找到了两种方法来做这件事，两种方法都很好

from __future__ import print_function
import unittest
import os.path
import win32com.client

class ExcelMacro(unittest.TestCase):
    def test_excel_macro(self):
        try:
            xlApp = win32com.client.DispatchEx('Excel.Application')
            xlsPath = os.path.expanduser('C:\\Users\\rshuell001\\Desktop\\Valuation Code Rollover.xlsb')
            wb = xlApp.Workbooks.Open(Filename=xlsPath)
            xlApp.Run('Macro1')
            wb.Save()
            xlApp.Quit()
            print("Macro ran successfully!")
        except:
            print("Error found while running the excel macro!")
            xlApp.Quit()
if __name__ == "__main__":
    unittest.main()

---

import os
import win32com.client

#Launch Excel and Open Wrkbook
xl=win32com.client.Dispatch("Excel.Application")  
xl.Workbooks.Open(Filename="C:\Full Location\To\excelsheet.xlsm") #opens workbook in readonly mode. 

#Run Macro
xl.Application.Run("excelsheet.xlsm!modulename.macroname") 

#Save Document and Quit.
xl.Application.Save()
xl.Application.Quit() 

#Cleanup the com reference. 
del xl