从Python调用Excel VSTO加载项中的函数(SAP分析插件VBA宏)(运行时错误1004:宏可能不可用)

2021-12-01 11:53:35 发布

您现在位置:Python中文网/ 问答频道 /正文

问题摘要

我遇到一个Python代码问题,该代码试图在Excel工作簿中执行VBA宏。每当它试图使用excel_instance.Application.Run()时,就会引发一个错误:

Run-time error '1004': Cant run the macro 'SAPLogon'. The macro may not be available in this workbook or all macros may be disabled.

VBA Error

因此,显然脚本没有识别函数SAPLogonSAPExecuteCommand(属于SAP VSTO插件)。我认为这是因为SAP插件没有加载(在“为什么我认为问题与加载插件有关”一节中有更多信息)到我创建的Excel实例中。至少我认为是这样的

有人知道怎么解决吗


详细问题

我对该项目的目的是使用SAP Analysis for Office插件自动刷新Excel工作簿。该插件与SAP连接,以从业务仓库提取信息。据我所知,它是通过VSTO编程制作的,它有一个宏API,在VBA代码的帮助下在Excel中使用它(更多信息here

我想将其与Python集成,这样我就可以将此代码嵌入到其他项目中,以自动从SAP获取数据片段。因此,在这个blog(用VBA实现了一个解决方案)的帮助下,我开始用Python寻找我自己的解决方案

下面你可以看到我想要做的流程: Macro-Process of the routine I want to script

在这里,您可以看到我想用Python脚本介绍的详细过程:
-绿色:表示我设法实现的流程
-红色:当前不工作的进程

Detailed process


我当前使用的Python代码

import win32com.client as win32  # allow easy access to Window’s Component Object Model (COM) and control Microsoft applications
from pathlib import Path  # Module to handle file paths

# Path of excel file to import
sap_aof_excel_file = Path.cwd().joinpath('Test_Data.xlsm')

# Declaring variables
bwclient = "999"
bwuser = "USERNAME"
bwpassword = "PASSWORD"

# Launch Excel: Create an Exel object and store it into a variable named 'excel_instance'
excel_instance = win32.gencache.EnsureDispatch('excel_instance.Application')

# Make it visible otherwise it doesn’t work
excel_instance.Visible = True
excel_instance.DisplayAlerts = False

# Open the file in the new Excel instance,
#     The 1st false: don't update the links 
#     The 2nd false: and don't open it read-only
workbook_sap = excel_instance.Workbooks.Open(sap_aof_excel_file, False, False)

# Force the plugin to be enabled in the instance of Excel
for addin in excel_instance.Application.COMAddIns:
    if addin.progID == 'SapExcelAddIn':
        if addin.Connect == False:
            addin.Connect = True
        elif addin.Connect == True:
            addin.Connect = False
            addin.Connect = True

# logs in to Business Warehouse
lResult = excel_instance.Application.Run("SAPLogon", "DS_1", bwclient, bwuser, bwpassword)

# execute a command to refresh the data
lResult = excel_instance.Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")

# Save the file and close it
workbook_sap.Save
workbook_sap.Close()

# force the application to close
excel_instance.DisplayAlerts = True
excel_instance.Application.Quit()

# Clear out the memory 
excel_instance = None
workbook_sap = None


为什么我认为问题与加载插件有关

我真的在试图找出解决这个问题的一个方法。我意识到,如果我自己打开Excel工作簿,当VBA宏调用SAPLogon函数时,它工作得很好。只有从Python脚本调用它时,它才起不到作用。所以我去了VBA Studio,看到由Python启动的实例显然没有加载插件

mylself打开的工作簿:在资源管理器中加载插件

Workbook opened by mylself

用Python语句打开的工作簿:没有插件 Python中用于打开实例的语句是win32.gencache.EnsureDispatch('Excel.Application')

Workbook opened by Python

所以我决定做一个测试。如果我调用另一个插件,比如数据分析它会工作吗?
答案是:不会。它会引发与SAP插件相同的错误

Also raise a error same like SAPLogon

这让我很高兴。至少我认为这是一个与COM接口有关的问题,与通过Python访问SAP插件的限制无关。经过几次研究,我发现可以使用RegisterXLL方法加载某些类型的插件。因此我在代码中添加了以下行:

# Register (or loads) XLL Excel Plugin called Data Analysis
excel_instance.RegisterXLL(r'C:\Program Files (x86)\Microsoft Office\root\Office16\Library\Analysis\ANALYS32.XLL')
# Call "Moveavg" Analysis ToolPack Function after execution of RegisterXLL
lResult = excel_instance.Application.Run("Moveavg", 'arnols', 'souza', False, True, False)

成功了!
代码执行了数据分析插件中的函数

It worked

很明显,我尝试以同样的方式加载SAP插件。但没有成功。这种方法只适用于XLL,而不适用于VSTO插件。此外,我也找不到正确加载VSTO插件的解决方案

excel_instance.RegisterXLL(r'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.vsto')

它会引发一个错误


我尝试过但没有成功的替代方案

  • 暂定1:从其他解决方案(Link)调用VSTO函数
    • 注意:我尝试使用下面的语句调用SAPLogon函数
      excel_instance.Application.COMAddIns['SapExcelAddIn'].Object.SAPLogon("DS_1", bwclient, bwuser, bwpassword)
    • 发生了什么:错误

AttributeError: .SAPLogon

  • 暂定2:尝试动态启动Excel实例
    • 注意:更改了代码中的语句
      excel_instance = win32.dynamic.Dispatch('Excel.Application')
    • 发生了什么:没什么不同
  • 暂定3:尝试指定插件所在的位置
    • 注意:尝试了两条指向插件所在位置的路径
# Need to load the add ins before opening the workbook
#addin_path = r'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.vsto'
addin_path = r'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.dll'
excel_instance.AddIns.Add(addin_path).Installed = True
  • 发生了什么:错误

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Add method of AddIns class failed', 'xlmain11.chm', 0, -2146827284), None)

  • 暂定4:尝试使用absulute路径调用函数
    • 注释:大量的破伤风
test0 = "SAPLogon"
test1 = r'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.vsto!SAPLogon'
test2 = r'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.vsto|vstolocal!SAPLogon'

test3 = r"'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.vsto'!SAPLogon"
test4 = r"'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.vsto|vstolocal'!SAPLogon"

test5 = 'SapExcelAddIn.vsto!SAPLogon'
test6 = 'SapExcelAddIn.vsto|vstolocal!SAPLogon'

lResult = excel_instance.Application.Run(testn, "DS_1", bwclient, bwuser, bwpassword)
  • 发生了什么:错误

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Cannot run the macro 'SAPLogon'. The macro may not be available in this workbook or all macros may be disabled.", 'xlmain11.chm', 0, -2146827284), None)