SAP Analysis for Excel to start with Excel

[former_member725526]

Hi,

We are trying to port our VBA codebase to python (3.6) and I cannot figure out how to start Excel with SAP Analysis for Excel from Python. Below is the code I'm using.

import win32com.client as w32

f_path = "example.xlsm"

xl = w32.gencache.EnsureDispatch("Excel.Application")
wb = xl.Workbooks.Open(os.path.abspath(f_path))
xl.Visible = 1 
xl.WindowState = w32.constants.xlMaximized

With this method Excel will open however the Analysis add-in does not load. The things I've tried so far:

• Set the LoadBehaviour to 3 in regedit for the add-in (so it starts automatically)
• Try to launch the add-in with python (to no avail) in shell
• Include a macro within the Excel file that starts the add-in

If I start the file manually then the add-in loads properly. If I use the last two method then a new Excel instance will be initialized with the add-in while the file I was trying to use will not have the add-in loaded.

Did anyone else had this issue before?

Thanks

[former_member186338]

Full AO version info?

Try to reproduce the same with VBScript?

[former_member725526]

Excel 2016 - 64 bit, SAP Analysis for Excel - 64 bit
I did not try VBScript because we decided to go with Python.

[former_member186338]

"I did not try VBScript because we decided to go with Python." - just try to test!

[maagp · Accepted solution]

Hi Ben,

Excel won't load Add-Ins automatically when launching it via automation (your Python script will start Excel with the /automation and -embedding parameters) so you have to manually enable the Add-In which you should be able to achieve by adding following line to your script:

xl.COMAddIns("SAPExcelAddIn").Connect = True

Additionally we have the problem that Microsoft does not support VSTO Add-Ins when using embedding, therefore using Analysis Office in embedded mode / automation isn't supported by SAP (so AO won't load when it is detecting that Excel is running in embedded mode). Anyway you can bypass this by enabling the AO settings SupportAutomatedOffice and SupportEmbeddedMode. But please be aware that you do this on your own risk and that you might run into issues which can't be resolved when using AO in an embedded Excel.

[former_member725526 · Accepted solution]

Hey Patrik,

Thank you very, you don't know how much I was looking for this kind of information 🙂 I'll test it and get back to you!

[maagp · Accepted solution]

You are welcome! 🙂 Hope it will help you to get started and I am looking forward to your feedback.

[former_member725526 · Accepted solution]

Hi Patrik! Feedback time (sorry for being late :))

I've followed your guide and did the following:

• updated ao_user_roaming.config and cof_user_roaming.config files (by adding an extra line in the end)
• added the following line to my code

xl.COMAddIns("SAPExcelAddIn").Connect = True

Unfortunately it ran into an error.

I'm not sure if it is because of the fact that I do not have admin privilege or I did not follow yours steps correctly. Any guess?

Thanks
Ben

[maagp · Accepted solution]

Hi Ben,

Regarding the edited config files:
Could you kindly remove the colons ( : ) from the lines you added? They are not needed and will probably cause that the settings you are trying to change are not properly recognized by AO.

So the lines should look like this:

In Ao_user_roaming.config:
<SupportAutomatedOffice value="True" />

In Cof_user_roaming.config
<SupportEmbeddedMode value="True" />

Regarding the error in the python script:

It shouldn't be necessary to run your script or Excel as administrator to start an Add-In. On my machine it also worked fine without admin privileges.

Here's the script I successfully tested on my machine w/o admin privileges using AO 2.8 SP6:

import win32com.client as w32

f_path = "C:\\Temp\\testwb.xlsx"

xl = w32.gencache.EnsureDispatch("Excel.Application")
wb = xl.Workbooks.Open(f_path)
xl.Visible = 1 
xl.WindowState = w32.constants.xlMaximized

xl.COMAddIns("SAPExcelAddIn").Connect = True

Does AO currently get properly activated when starting Excel the normal way? If not, it might be possible that Excel added it to the "Disabled Items" list for any reason - this could be an explanation for the error.

Maybe you could also try to disable the add-in first before trying to activate it (when starting Excel via python your default add-ins will get displayed as enabled in the Excel UI - although Excel does not really enable them when launching via python.

xl.COMAddIns("SAPExcelAddIn").Connect = False

It works both ways on my machine (with and without setting Connect = False first).

Best regards,
Patrik

[former_member725526 · Accepted solution]

You are right!!! It works now, thank you very much 🙂