CODEWITHSUNDEEP
pythonvbacomvstowin32com
Arnold Souza
Arnold Souza

Reputation: 661

Call Function in Excel VSTO Add-in from Python (SAP Analysis Plug-in VBA macros) (Run-time error 1004: The macro may not be available)

Problem summary

I am having problem with a Python code that is trying to execute a VBA Macro inside a Excel Workbook. Whenever it tries to use excel_instance.Application.Run() it raises an error:

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

So, apparently the Script is not recognizing the functions SAPLogon and SAPExecuteCommand (that belongs to SAP VSTO Plug-in. I believe it is happening because the SAP Plugin is not loading (more information about that in the section: Why do I think the problem is related to load Plugins?) into the Excel instance I have created. At least that's what I think it is.

Does anyone know how to solve it?

Detailed Problem

My intention with this project is to automate the refresh of Excel Workbooks that uses SAP Analysis for Office AddIn. This Plug-In connects with SAP to extract information from Business Warehouses. As much as I could understand it is made with VSTO programing and it has a Macro API to use it inside Excel with the help of VBA Codes (more info here)

I want to integrate this with Python so that I can embed this code with another projects to automatically get pieces of data from SAP. So, with the help of this blog (that implements a solution with VBA) I started to look for my own solution with Python.

Bellow you can see the flow I want to do: Macro-Process of the routine I want to script

Here you can see the detailed process I want to cover with the Python Script:
- Green: Represent processes that I managed to implement
- Red: Process that currently is not working

Detailed process

Python code that I am currently using

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

Why do I think the problem is related to load Plugins?

I was really trying to figure out one solution to this problem. I realized that if I open the Excel Workbook by myself the SAPLogon function works well when called by a VBA Macro. It only does not work if I call it from a Python Script. So I went to VBA Studio and saw that the instance initiated by Python apparently do not load plugins.

Workbook opened by mylself: with the Plugins loaded in the explorer

Workbook opened by mylself

Workbook opened with Python statement: without the plugins
The statement used in Python to open up the instance was win32.gencache.EnsureDispatch('Excel.Application')

Workbook opened by Python

So I decided to make a test. What if I call another plugin, like Data Analysis will it work?
The answer is: No. It raises the same error as SAP Plugin.

Also raise a error same like SAPLogon

That made me happy. At least I think it is a problem related to the COM interface and not related to restrictions in the access of SAP Plugin through Python. After few research I found that you can load certain types of Plugins with the method RegisterXLL. So I added the lines bellow to my code:

# 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!
The code executed the function from Data Analysis Plugin.

It worked

So obviously I tried to load the SAP Plugin the same way. But it wasn't successful. This method only works for XLL, not VSTO Plugins. Also, I couldn't found a solution to load properly VSTO Plugins.

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

It raises an error. =(

Alternatives that I have tried without success

AttributeError: .SAPLogon

# 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)

Upvotes: 0

Views: 1766

Answers (2)

Pawn_Shop
Pawn_Shop

Reputation: 1

First post ever.

I used a workaround to solve the above and thought to help out as this post gave me a lot of hints on the problem.

Problem is that Python win32com.client library does not support .vsto addin. I ran the following test:

  1. Scanned all addins (I was able to see SAP AFO)
  2. Tried to trigger it using the index ID instead of name
  3. Used VBS

By using VBS, the addin was triggered when Excel opened (pretty much the same codes as Python - turning on all addins).

To understand if that was really the problem, I made a .xlsm workbook with a VBA that triggers addin, then used Python to trigger that VBA script and the AFO addin appeared.

This means that it was win32com.client library not triggering it (I read a bit of the code, it seems to target only normal addin extensions). Since it's a public library and not made by Microsoft, my guess is that they never thought to include .vsto files.

Solution: What I did was:

  1. Open the Excel (Allow Trust Center)
  2. Add in a VBA using Python into the .xlsx workbook
  3. Run the VBA
  4. Refresh file
  5. Save workbook as .xlsx (I played it safe and used Python to remove the codes as well)

The VBA contains the code to activate all addins.

Since Python cannot trigger the .vsto, therefore, I was forced to add a VBA code to trigger the addin.

Upvotes: 0

YUVRAJ
YUVRAJ

Reputation: 109

In your VSTO addin, if you create a macro for running some background code without any ui then I suggest to create XLL instead of VSTO addin so that you can register it with RegisterXLL function as you mention in your question.

You can create XLL with Excel-DNA framework. You dont need to write too much code, just copy paste VSTO code and changes some reference.

Or you can try to add VSTO addin using excel_instance.AddIns.Add(addin_path) and later on make installed=true. But before doing this, check your addin is register in your user/machine registry.

Upvotes: 1

Related Questions