我遇到一个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.
因此,显然脚本没有识别函数SAPLogon
和SAPExecuteCommand
(属于SAP VSTO插件)。我认为这是因为SAP插件没有加载(在“为什么我认为问题与加载插件有关”一节中有更多信息)到我创建的Excel实例中。至少我认为是这样的
有人知道怎么解决吗
我对该项目的目的是使用SAP Analysis for Office插件自动刷新Excel工作簿。该插件与SAP连接,以从业务仓库提取信息。据我所知,它是通过VSTO编程制作的,它有一个宏API,在VBA代码的帮助下在Excel中使用它(更多信息here)
我想将其与Python集成,这样我就可以将此代码嵌入到其他项目中,以自动从SAP获取数据片段。因此,在这个blog(用VBA实现了一个解决方案)的帮助下,我开始用Python寻找我自己的解决方案
在这里,您可以看到我想用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打开的工作簿:在资源管理器中加载插件
用Python语句打开的工作簿:没有插件
Python中用于打开实例的语句是win32.gencache.EnsureDispatch('Excel.Application')
所以我决定做一个测试。如果我调用另一个插件,比如数据分析它会工作吗?
答案是:不会。它会引发与SAP插件相同的错误
这让我很高兴。至少我认为这是一个与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)
成功了!
代码执行了数据分析插件中的函数
很明显,我尝试以同样的方式加载SAP插件。但没有成功。这种方法只适用于XLL,而不适用于VSTO插件。此外,我也找不到正确加载VSTO插件的解决方案
excel_instance.RegisterXLL(r'C:\Program Files (x86)\SAP BusinessObjects\Office AddIn\SapExcelAddIn.vsto')
它会引发一个错误
excel_instance.Application.COMAddIns['SapExcelAddIn'].Object.SAPLogon("DS_1", bwclient, bwuser, bwpassword)
AttributeError: .SAPLogon
excel_instance = win32.dynamic.Dispatch('Excel.Application')
# 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)
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)
在您的VSTO加载项中,如果您创建了一个宏来运行一些没有任何ui的后台代码,那么我建议您创建XLL而不是VSTO加载项,以便您可以使用您在问题中提到的RegisterXLL函数注册它
您可以使用Excel DNA framework创建XLL。您不需要编写太多代码, 只需将粘贴到代码并更改一些引用即可
或者,您可以尝试使用
excel_instance.AddIns.Add(addin_path)
和稍后的makeinstalled=true
将VSTO添加到addin。 但在执行此操作之前,请检查加载项是否已在用户/计算机注册表中注册相关问题 更多 >
编程相关推荐