cancel
Showing results for 
Search instead for 
Did you mean: 

VBA and Workbook Precalculation using BEx Broadcaster

Former Member
0 Kudos

Gurus,

I am currently prototyping the precalculation service. And using the precalculation service you can set a workbook for precalculation and also get it to email. But my current challenge is to save the refreshed version back. I basically want to deactivate the ‘auto refresh on open’ option and want the users to see the refreshed version without refreshing the workbook every time they open and as it is a large user base, I do not want to be emailing everyone.

To achieve the same, I did try putting the following VBA code in the workbook under the SAPBEXonRefresh event.

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)
If Run("SAPBEX.XLA!SAPBEXsaveWorkbook") = 0 Then
Else
    MsgBox "Error while saving the refreshed workbook"
End If
End Sub

If I refresh the workbook manually, everything works fine, but when I schedule via the precalculation service I get the following error...

Run-time error ‘1004’:
‘SAPBEX.XLA’ could not be found. Check the spelling of the file name, and verify that the file location is correct.

<Continue> <End> <Debug> <Help>

Has anyone tried this before and had success. If so, could you please help me in accomplishing the same...

Appreciate your time and effort in this matter.

Regards

_RJA

Message was edited by: RJA

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi RJA,

You will need to install the BW Front end and Excel in the server where your BEx Precalculation is installed. I had implemented with macro in my workbook and it should work for you.

Regards,

Meng T.

Former Member
0 Kudos

Make sure you open the workbook using BEx Analzer not just in Excel. SAPBEX.XLA is the Excel macro for BEx Analyzer.

Thanks.

Former Member
0 Kudos

Deepu,

Thank you for the response.

But as I did mention before, everything works in the interactive mode as in opening the Workbook using BEx and refreshing the workbook manually. But the issue is with the precalculation server. Looks like the service tries to run the refresh module in background and falls over because of the VBA script. And again, I am just trying to call a standard BEx module to save the workbook after refresh.

I may also want to rephrase my question again to avoid any confusion:

“Has anyone setup a ‘workbook with VBA code’ for precalculation using the precalculation server (Using BEx->Tools->Distribute Workbook option)?”

Regards

_RJA

Former Member
0 Kudos

Hi RJA,

To avoid the error message, you can change the code as follows:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

'

On Error Resume Next

'

If Run("SAPBEX.XLA!SAPBEXsaveWorkbook") = 0 Then

Else

MsgBox "Error while saving the refreshed workbook"

End If

End Sub

- Pete

Former Member
0 Kudos

Hello Peter,

That was a really useful tip. Thank you.

But again, "On Error Resume Next" seem to have only suppressed the error and has not helped in the execution of SAPBEX.XLA!SAPBEXsaveWorkbook ? Would you have any idea as to how do we get handle the exception ?

_RJA

Former Member
0 Kudos

Hello RJA,

I had the same problem, if you want to execute a workbook with VBA on the precalculation server you have to use SAPBEXP.XLA!xxxxxxxxxxxx, because in case of Precalculation Server, it'll be refered to a special macro called as 'sapbexp.xla'

See also note 974931.

In my case it doesn't work anyway, maybe because the precalulation server doesn't 'know' the SAPBEXP.XLA although the file exists on the server, has anybody an idea ?

-Norbert

Former Member
0 Kudos

Try to put these statements before calling Saveworkbook and check.

Workbooks.Open ("C:\Program Files\SAP\FrontEnd\Bw\sapbex.xla")

Run "sapbex.xla!sapbexinit"

Here C:\Program Files\SAP .... is the path where SAPBEX.xla is stored.You change it according to your file path...

Just try this code ....

Regs

Gopi

Message was edited by: Gopi