首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >通过Python运行Excel宏?

通过Python运行Excel宏?
EN

Stack Overflow用户
提问于 2013-10-27 16:48:24
回答 7查看 135.8K关注 0票数 56

我试图通过python运行一个宏,但我不确定如何让它工作……

到目前为止,我已经获得了以下代码,但它不起作用。

代码语言:javascript
复制
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

我得到了以下回溯:

代码语言:javascript
复制
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)

编辑

代码语言:javascript
复制
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
EN

回答 7

Stack Overflow用户

回答已采纳

发布于 2013-11-03 00:01:33

我认为这个错误与您正在调用的宏有关,请尝试以下代码:

代码

代码语言:javascript
复制
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
票数 75
EN

Stack Overflow用户

发布于 2017-08-12 07:05:52

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

代码语言:javascript
复制
    #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
票数 12
EN

Stack Overflow用户

发布于 2019-01-09 06:36:21

只需使用带空格的xlsm快速注释即可。

代码语言:javascript
复制
file = 'file with spaces.xlsm'
excel_macro.Application.Run('\'' + file + '\'' + "!Module1.Macro1")
票数 4
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/19616205

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档