VBA and Workbook Precalculation using BEx Broadcaster

122 Views

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