通过 Python 运行 Excel 宏？

Running an Excel macro via Python?

提问by Ryflex

I'm trying to run a macro via python but I'm not sure how to get it working...

我正在尝试通过 python 运行一个宏，但我不知道如何让它工作......

I've got the following code so far, but it's not working.

到目前为止，我有以下代码，但它不起作用。

import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename="C:\test.xlsm",ReadOnly=1)
xl.Application.Run("macrohere")
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0

I get the following traceback:

我得到以下回溯：

Traceback (most recent call last):
  File "C:\test.py", line 4, in <module>
    xl.Application.Run("macrohere")
  File "<COMObject <unknown>>", line 14, in Run
  File "C:\Python27\lib\site-packages\win32com\client\dynamic.py", line 282, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u"Cannot run the macro 'macrohere'. The macro may not be available in this workbook or all macros may be disabled.", u'xlmain11.chm', 0, -2146827284), None)

EDIT

编辑

import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename="C:\test.xlsm",ReadOnly=1)
try:
    xl.Application.Run("test.xlsm!testmacro.testmacro")
    # It does run like this... but we get the following error:
    # Traceback (most recent call last):
        # File "C:\test.py", line 7, in <module>
        # xl.Workbooks(1).Close(SaveChanges=0)
        # File "C:\Python27\lib\site-packages\win32com\client\dynamic.py", line 192, in __call__
        # return self._get_good_object_(self._oleobj_.Invoke(*allArgs),self._olerepr_.defaultDispatchName,None)
    # com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
except:
    # Except isn't catching the above error... :(
    xl.Workbooks(1).Close(SaveChanges=0)
    xl.Application.Quit()
    xl=0

采纳答案by SMNALLY

I would expect the error is to do with the macro you're calling, try the following bit of code:

我希望错误与您正在调用的宏有关，请尝试以下代码：

Code

代码

import os, os.path
import win32com.client

if os.path.exists("excelsheet.xlsm"):
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(os.path.abspath("excelsheet.xlsm"), ReadOnly=1)
    xl.Application.Run("excelsheet.xlsm!modulename.macroname")
##    xl.Application.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
    xl.Application.Quit() # Comment this out if your excel script closes
    del xl

回答by Boud

I suspect you haven't authorize your Excel installation to run macro from an automated Excel. It is a security protection by default at installation. To change this:

我怀疑您没有授权您的 Excel 安装从自动 Excel 运行宏。它是安装时默认的安全保护。要改变这一点：

1. File > Options > Trust Center
2. Click on Trust Center Settings... button
3. Macro Settings > Check Enable all macros

1. 文件 > 选项 > 信任中心
2. 单击信任中心设置...按钮
3. 宏设置 > 选中启用所有宏

回答by SMNALLY

Hmm i was having some trouble with that part (yes still xD):

嗯，我在那个部分遇到了一些麻烦（是的，仍然是 xD）：

xl.Application.Run("excelsheet.xlsm!macroname.macroname")

xl.Application.Run("excelsheet.xlsm!macroname.macroname")

cos im not using excel often (same with vb or macros, but i need it to use femap with python) so i finaly resolved it checking macro list: Developer -> Macros: there i saw that: this macroname.macronameshould be sheet_name.macronamelike in "Macros" list.

因为我不经常使用 excel（与 vb 或宏相同，但我需要它在 python 中使用 femap）所以我最终解决了它检查宏列表：开发人员 -> 宏：我看到：这macroname.macroname应该sheet_name.macroname像在“宏”中列表。

(i spend something like 30min-1h trying to solve it, so it may be helpful for noobs like me in excel) xD

（我花了大约 30 分钟到 1 小时的时间试图解决它，所以它可能对像我这样的 excel 菜鸟有所帮助）xD

回答by abautista

I did some modification to the SMNALLY's code so it can run in Python 3.5.2. This is my result:

我对 SMALLY 的代码做了一些修改，以便它可以在 Python 3.5.2 中运行。这是我的结果：

#Import the following library to make use of the DispatchEx to run the macro
import win32com.client as wincl

def runMacro():

    if os.path.exists("C:\Users\Dev\Desktop\Development\completed_apps\My_Macr_Generates_Data.xlsm"):

    # DispatchEx is required in the newest versions of Python.
    excel_macro = wincl.DispatchEx("Excel.application")
    excel_path = os.path.expanduser("C:\Users\Dev\Desktop\Development\completed_apps\My_Macr_Generates_Data.xlsm")
    workbook = excel_macro.Workbooks.Open(Filename = excel_path, ReadOnly =1)
    excel_macro.Application.Run\
        ("ThisWorkbook.Template2G")
    #Save the results in case you have generated data
    workbook.Save()
    excel_macro.Application.Quit()  
    del excel_macro

回答by William Tong

I tried the win32com way and xlwings way but I didn't get any luck. I use PyCharm and didn't see the .WorkBook option in the autocompletion for win32com. I got the -2147352567 error when I tried to pass a workbook as variable.

我尝试了 win32com 方式和 xlwings 方式，但我没有任何运气。我使用 PyCharm 并且在 win32com 的自动完成中没有看到 .WorkBook 选项。当我尝试将工作簿作为变量传递时，出现 -2147352567 错误。

Then, I found a work around using vba shell to run my Python script. Write something on the XLS file you are working with when everything is done. So that Excel knows that it's time to run the VBA macro.

然后，我找到了使用 vba shell 运行 Python 脚本的解决方法。一切都完成后，在您正在使用的 XLS 文件上写一些东西。这样 Excel 就知道是时候运行 VBA 宏了。

But the vba Application.wait function will take up 100% cpu which is wierd. Some people said that using the windows Sleep function would fix it.

但是 vba Application.wait 函数会占用 100% 的 cpu，这很奇怪。有人说使用windows睡眠功能就可以解决。

 Import xlsxwriter


 Shell "C:\xxxxx\python.exe 
 C:/Users/xxxxx/pythonscript.py"

 exitLoop = 0

wait for Python to finish its work.

等待 Python 完成它的工作。

  Do

  waitTime = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 30)
  Application.Wait waitTime
  Set wb2 = Workbooks.Open("D:\xxxxx.xlsx", ReadOnly:=True)
  exitLoop = wb2.Worksheets("blablabla").Cells(50, 1)
  wb2.Close exitLoop

  Loop While exitLoop <> 1




  Call VbaScript

回答by PyBoss

For Python 3.7 or later,(2018-10-10), I have to combine both @Alejandro BR and SMNALLY's answer, coz @Alejandro forget to define wincl.

对于 Python 3.7 或更高版本，(2018-10-10)，我必须结合@Alejandro BR 和 SMNALLY 的答案，因为 @Alejandro 忘记定义 wincl。

import os, os.path
import win32com.client
if os.path.exists('C:/Users/jz/Desktop/test.xlsm'):
    excel_macro = win32com.client.DispatchEx("Excel.Application") # DispatchEx is required in the newest versions of Python.
    excel_path = os.path.expanduser('C:/Users/jz/Desktop/test.xlsm')
    workbook = excel_macro.Workbooks.Open(Filename = excel_path, ReadOnly =1)
    excel_macro.Application.Run("test.xlsm!Module1.Macro1") # update Module1 with your module, Macro1 with your macro
    workbook.Save()
    excel_macro.Application.Quit()  
    del excel_macro

回答by Peter Mabbott

A variation on SMNALLY's code that doesn't quit Excel if you already have it open:

SMALLY 代码的一个变体，如果你已经打开它，它不会退出 Excel：

import os, os.path
import win32com.client

if os.path.exists("excelsheet.xlsm"):
    xl=win32com.client.Dispatch("Excel.Application")
    wb = xl.Workbooks.Open(os.path.abspath("excelsheet.xlsm"), ReadOnly=1) #create a workbook object
    xl.Application.Run("excelsheet.xlsm!modulename.macroname")
    wb.Close(False) #close the work sheet object rather than quitting excel
    del wb
    del xl

回答by coldbyte

Just a quick note with a xlsm with spaces.

只是一个带有空格的 xlsm 的快速注释。

file = 'file with spaces.xlsm'
excel_macro.Application.Run('\'' + file + '\'' + "!Module1.Macro1")