Solved: BPC nw Excel vba macro security

Former Member · ‎10-01-2015

Hi,

My environment : SAP BPC NW 7.5 SP17 / Excel 2013

We have recently changed the macro security for Excel from "Enable all

macros" to "Disable all macros with notification".

We can no more refresh the data from the report nor send input data to

the database : it seems like that the functions "MNU_ETOOLS_REFRESH"

and"MNU_ETOOLS_EXPANDANDREFRESH" cannot be executed (no effects at all).

I have tried to change the macro security option to "Disable all macros

except digitally signed macros" and the two buttons work correctly ie

we can refresh and send data again.

Also, I have found out something strange after some other tests : even if i

have defined the macro security to "Disable all macros except digitally

signed macros", only the buttons with the behind functions

"MNU_ETOOLS_REFRESH" or "MNU_ETOOLS_EXPANDANDREFRESH" work properly.

I meant that if i add another button with the following VBA code :

msgbox "hello"

well, it doesn't work at all unless i changed once again the macro

security to "Enable all macros (not recommended..." : then, it works

fine.

If we want an input schedule work properly with BPC internal functions ("MNU_ETOOLS_REFRESH"

and"MNU_ETOOLS_EXPANDANDREFRESH") and VBA code (button), is it mandatory to

define the macro security to "Enable all macros...." ?

Are there some other ways to workaround this issue ?

Thanks for your feedback.

Former Member · ‎10-12-2015

Hi Vadim,

Which digital signature did you choose (editor) if you can provide it or give some names ?

What we need is to provide a way for all the Sap bpc users to be able to create their own Excel reports/input schedules and also to be able to share them with other users (some reports might include vba codes). So, i mean we need a "GROUP" certificate : do your users share the same issues that had been resolved through your digital signature ?

How can i get through the tests before really "paying" for it ?

Thanks.

Former Member · ‎10-28-2015

Hi,

Do you know if there is a way to change these two paths :

1) path where reports are downloaded when using open dynamic templates :

C:\Users\user\AppData\Local\Temp\OSoftTemp\<appset>\<applicationname>\eEXCEL\REPORTS

2) path where reports are downloaded when using BPF :

C:\Users\user\AppData\Local\Temp\<servername>\<user>\<appset>\<applicationname>

I was wondering if i could redirect to other folders so i can define "approved folders" in Excel in order to execute vba code macros.

There is an error message when i try to define these 2 paths as "approved folders" probably because of the "temp"

Thanks.

Former Member · ‎10-15-2015

Thanks for your feedback but it doesn't matter if i succeed in all the tests at home since the security team doesn't want to know anything about jailbreak.

After that, I have tried some tests with makecert :

How to create a self-signed certificate that can be used tosign MS-Office VBA projects (Excel/Word m...

Unfortunately, also unsuccessful.

I have 2 questions :

- is there a parameter on the IIS server (sap bpc) to define where the reports/input schedules will be downloaded when opened from BPF links ? in fact, i know where the documents are downloaded when opened but would need to redirect it to another folder since it is currently in "Temp" and i can not use the Excel "Repository approved" options. I learned that all macros would be executed without control when they are in an "approved folder".

- apart from using selfcert.exe to create a certificate, when i want to make a digital signature in a vba code in Excel, no certificate appears. Do you know what are the conditions for a certificate to appear in Excel/developer vba code digital signature ?

BR,

Steve.

Former Member · ‎10-12-2015

Yes, you're right : that is what i understood from you.

It is just that the AD admins didn't want to use the jailbreak application which was recognised as a virus (when downloaded) that i am blocked during my tests.

So, i am still looking for an alternative for the point 4 and 5.

Thanks for all your helpful answers.

Former Member · ‎10-08-2015

OK but unfortunately almost all the reports/input schedules are opened through BPF, so through the links to the files on the BPC server.

After completing the tests, these error messages are only related to the fact that the files (containing sap macros and/or vba code) were stored on the bpc server : if i open the report then the sap macro or vba code shows error message unless i signed it digitally.

I still continue to look for a solution.

Former Member · ‎10-08-2015

OK, thanks.

After some other tests, we have found out something strange for we didn't expect it : with the excel macro always defined at "Disable all macros with notification", we are able to create a report with a button containing vba code. We save it on the desktop and succeed at opening and executing the macro vba code behind then button (after closing excel naturally).

Finally, we come to the conclusion that, only when we save the report on the sap bpc server ("save dynamic templates") then we can no more execute the macro vba code behind the button (message error appears related to macro security).

Do you know any parameter related on the microsoft IIS server machine to modify in order to change this behavior or to workaround this issue ?

BR,

Steve

Former Member · ‎10-07-2015

I read the links. Thanks for them.

Sorry, to continue with the certificate, i was thinking of the following issues :

One person creates the self signed certificate on his laptop (say "Bpc_Cert") then add the digital signature in a bpc report with this certificate then put the report in the bpc server (for other users).

He exports the certificate to a filename then make it distributed to the group of users through a GPO (by an import...).

The other users open the report then "approve all from this editor" (the first time) and can use the report correctly (refresh button or button with vba code behind).

The question : what if the other users want to create reports by themselves then make them available for other users ? there is a problem, no ? because there is only one person who has distributed "HIS" self signed certificate through the gpo.

How do you use certificate for this situation ?

Is it possible to create a "Group" certificate so that all the people belonging to this Group could create or modify reports (with macro and vba code) and share them with others ?

BR,

Steve

Former Member · ‎10-07-2015

I have some certificate in my laptop but when i go to vba code to add the digital signature, it doesn't show up. There is only the certificate that i created from the SELFCERT.EXE application. What should be done to make other certificates showed ?

Otherwise, if i order not free certificate, what makes the difference ? i mean what does it bring more than the self generated one ?

Former Member · ‎10-07-2015

What if the laptop that create the certificate "crashs" ?

will it be possible to create in another laptop another certificate with the same name and will it work fine ?

Thanks for your help

Former Member · ‎10-07-2015

OK, i am currently working with the AD admin.

Does it exist others types of certificate than self generated Certificate to sign VBA code in Excel for BPC macro ?

Former Member · ‎10-06-2015

Thanks a lot for your quick reply.

After i have tested the self signed certificate with success on my pc, i ask the MS Active Directory administrators about how to distribute : they told me about gpo distribution.

As we are not allowed to change the GLOBAL gpo, is it the right way to distribute or you know other ways ?

Can you give more details or explain the way you see things ? i must confess that it is not quite clear for me.

Regards,

Steve

Former Member · ‎10-05-2015

Hi Vadim,

Thanks for your response : i am currently looking at it.

Another question : i have tried to use self generated Certificate to sign VBA code in Excel for BPC macro through the SELFCERT.exe.

It seems to work fine on my PC but how will i distribute this solution (all the input schedules that i will modifiy) for all the users ?

I read that this solution would only work for my own PC only (if it is true).

Another question :

The excel macro security is set to "Disable all macros with notification" : when prompted to "Enable", what is the behaviour expected ? I mean that if i click on "Enable", i can not execute the Refresh button (MNU_ETOOLS_REFRESH) nor the VBA code. So what ?

Regards,

Steve

former_member186338 · ‎10-02-2015

Hi Steve,

No surprise that "Disable all macros with notification" - disabled everything

"Disable all macros except digitally signed macros" - will enable only digitally signed macros coming from SAP like "MNU_ETOOLS_REFRESH" or "MNU_ETOOLS_EXPANDANDREFRESH".

To enable you own macros you have to sign the code Using at list self generated digital signature.

Please search Google - tons of documents how to digitally sign VBA code of Excel!

Vadim

BPC nw Excel vba macro security

Accepted Solutions (1)

Accepted Solutions (1)

Answers (12)

Answers (12)

Re: Asset Accounting

Re: Validation

Bank key for International banks

Re: Unable to configure SAP CC Catalog webservice ...

Revaluation of Batch Item Variance Recognition