Skip to Content
avatar image
Former Member

BPC nw Excel vba macro security

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

13 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 12, 2015 at 08:27 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • I have already explained you everything, you have to test yourself!

      1. You create self-signed certificate with some "Group" name like "Company XXX development"

      2. Using standard Windows applet export this certificate without private key - xxx.cer file.

      3. Distribute using AD this xxx.cer to trusted publishers to all users that will have to run macros signed by this certificate.

      4. Extract this certificate with private key using jailbreak - resulting in xxx.pfx file

      5. Import the certificate with private key to the persons who will create and sign macros in VBA.

      Done.

      Vadim

  • Oct 02, 2015 at 03:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 05, 2015 at 08:19 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Steve,

      "I read that this solution would only work for my own PC only (if it is true)." - not true ๐Ÿ˜Š

      You have to distribute self signed certificate to ALL users. Please talk with MS Active Directory administrators in your company.

      Then any code signed by this certificate will work without warnings...

      "Disable all macros with notification" is the same as "Disable all macros without notification" - the only difference is notification! It will disable ALL macros!

      Vadim

  • avatar image
    Former Member
    Oct 06, 2015 at 04:29 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 07, 2015 at 09:23 AM

    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 ?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 07, 2015 at 09:38 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 07, 2015 at 09:43 AM

    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 ?

    Add comment
    10|10000 characters needed characters exceeded

    • You have to export non-exportable certificate ๐Ÿ˜Š:

      iSECPartners/jailbreak-Windows · GitHub

      Please read documentation...

      Vadim

      "Otherwise, if i order not free certificate, what makes the difference ? i mean what does it bring more than the self generated one ?" - it's useful for software developers to distribute signed software. Please read at least Wiki to understand the certificates magic ๐Ÿ˜Š

  • avatar image
    Former Member
    Oct 07, 2015 at 03:57 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 08, 2015 at 02:40 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • "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 ?" - no workaround... It will work with normal file storage only.

      Vadim

  • avatar image
    Former Member
    Oct 08, 2015 at 04:03 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded