Run Macro on Refresh Not Saving

[Former Member]

Hello,

I recently generated a macro in a module for a BEx workbook that has a query setup to "Refresh Workbook on Open".  Ideally I want this macro to execute on each Refresh.  After searching google I came across this linked article.

I then went into Workbook Settings opened the "Exits" tab and clicked on "Run Macro on Refresh".  I input the name of my subroutine from the recently added module.  On my macro I added "ParamArray varname()" as a parameter (ie: Sub CopyFormulas (ParamArray varname())    ).

When I test it and repull in data through "Change Variable Values" it seems to work.  My sub is called and appears to be working.  I then use the BEx save workbook option, but when I close and re-open my workbook the "Run Macro on Refresh" toggle is off and my macro name not listed.

Any help to resolve this issue would be greatly appreciate.

-Jon

[Former Member]

I Had the same problem and had to pass specific parameters in the sub and then called the sun in workbook properties EXIT. ICAN only give details tomorrow... Cheers

[abhishek_shanbhogue2]

Hello Jon,

Even I have come across multiple issues with Bex Macross upon upgrading to 7x from 3 and most of the customization need workaround. I am in process of writing a technical blog for all the workarounds so that its helpful. Below is one such macros which can be used to refresh workbooks but you will need to be on GUI730 and patch level 700 onwards to get this working. If your on a lower version then as a workaround until this Frontend patch is applied you can set this parameter ANA_ENHANCE_REFRESH  in transaction RS_FRONTEND_INIT to 'X'.

Sub REFRESHBEX()

Workbooks.Open ("C:\Program Files (x86)\Common Files\SAP Shared\BW\BExAnalyzer.xla")

        With Run("BExAnalyzer.xla!sapbexGetConnection")

            .CLIENT = "<Clinet Number>"

            .SYSTEM = "<SAP System>"

            .user = "<User ID>"

            .Password = "<Passcode>"

            .Language = "EN"

            .SystemNumber = "<System ID>"

            .ApplicationServer = "<Application Server Details/IP>"

            .UseSAPLOgonIni = False

            .logon 0, True

        End With

        Run "BExAnalyzer.xla!sapbexinitConnection"

        Run "BExAnalyzer.xla!SAPBEXrefresh", False, Worksheets '<Your workbook which need to be refreshed>

End Sub

Thanks

Abhishek Shanbhogue

[sapna_jgurudutt]

Hi Jon,

Can you please refer the following SAP KBA Note:

1939689 - BEx Button not working: Macro ... can't be executed.

And, what is the Release and patch level applied to SAP GUI for Windows and

what is the patch level applied to BI 7.0 ADDON FOR SAP GUI

Also, please refer the following SAP Note:

147519 - Maintenance strategy / deadlines for SAP GUI

Thanks & Regards,

Sapna

[Former Member]

SAP GUI for Windows:

Release: 730 Final Release

File Version: 7300.1.3.3263

Build: 1396014

Patch Level: 3

BI Addon:

BI AddOn: 7.X (Based on 7.3)

Support PAckage: 0

Revision: 1829

I ran into this issue when I took an existing excel workbook that would use data extracted from the SAP GUI and tried to add an Analysis Grid in a new tab to migrated the process.

[sapna_jgurudutt]

Hi,

Can you please apply latest patch of SAP GUI for Windows 7.30  and

latest patch for BI 7.0 ADDON FOR SAP GUI 7.30 and check whether it helps you.

Also, please refer the following SAP Note:

147519 - Maintenance strategy / deadlines for SAP GUI

Also, please refer the following SAP KBA Note which I mentioned earlier here and check whether it

helps you:

1939689 - BEx Button not working: Macro ... can't be executed.

Thanks & Regards,

Sapna

[Former Member]

Sapna,

I am not an IT administrator in my company and do not have the permissions to patch the SAP Windows GUI or the SAP BI addon.  As well, I would be surprised if this resolved my issue unless this is a know issue with our version of SAP.

I am completely unable to view the KBA articles you referenced.  When I try to register on the Service Marketyplace I get an error that the domain for my email address (using my work email) is not registered to our customer number.  I am therefore unable to create an account or view the articles.

I am absolutely flabbergasted at all the issues I am having with this BEX addon.  I was able to toggle the radio button to run macros on exit, reference a macro name, and it did eventually save this option when I would re-open the workbook.  However, while trying to get it working I tried hundreds of combinations of steps to take in order to get it to save.  The macro name that saved included the Macro Parameters which I later learned is incorrect.  Whenever I try to go back and remove the parameters and save, it does not persist when I reopen the workbook.

Sometimes when I save it keeps my workbook settings and the vast majority of the time they are lost.  This is beyond absurd how inconsistently this tool functions and I am at my wits end using it.