Workbook Refresh issue

[Former Member]

Hi All,

I have a problem with workbook refresh. Here is the issue:

I have inserted 3 queries into a workbook. As per requirement, I need a certain column values to calculate and populate a set of coulmns. I wrote a VB Macro. The issue is that the workbook refreshes without the macro,but when I insert the macro, it fails to refresh.

I am not sure if I am inserting the SAPBExOnRefresh code at teh right place. Here is what I am doing:

Open VB Editor->Right Click on Modules>Insert Module-->Name it as RefreshMacro.

In the Macro, here is the initial code I am writing.

Sub RefreshMacro (ParamArray varname())

Dim lData_Provider As String

lData_Provider = varname(0) ******Do I have to physically pass the Data Provider Name into varname(0)???????

Set lRange = varname(1)

MsgBox lData_Provider

End Sub

After this my Macro code is inserted.

Private Sub Workbook_Open()

Dim buss_seg As String

Dim mon_hnd As String........

I have in the workbook settings, checked the automatic refresh on Open and in the exits tab, changed the name for the Macro to RefreshMacro(the macro name of mine).

I have tried all the options but still the workbook does not refresh.

Any steps on where to insert the code and any other changes in the declaration that I need to make? Help is greatly appreciated and rewarded.

Regards,

Sree

PS: I am on BI SP12 and GUI 710 Patch 8.

Any Help or solutions?????

Edited by: Sree Rama Tata on Sep 15, 2008 5:38 PM

[Former Member]

Did you try to put a debug watch point in the macro ? Maybe you' ll find what's appening during the refresh.

[Former Member]

I did put a debug watch and when executing it is not even going into the macro. For some reason, even after following the directions on the blog in SDN, it fails to trigger. I have just called for the macro using standard SAP provided code. Nothing different. My guess is it is looking for the data provider in varname (0) to pass and there is no place that SAP recommends declaring that.

[Former Member]

Hi Ram,

I am in the same boat you are, but after searching SDN a lot, I did find a posted solution that works.

and here is what You need to do.

I am guessing you want your Macro to execute on Query Refresh.

Open VB Editor->Right Click on Modules>Insert Module. You should now have Moudule1 or an new Module inserted into the Project

Create a Sub that looks like below you can change the name.

Public Sub RefreshMacro( queryId as String, resultArea as Range)
     Msgbox "Queryid = " & queryID 
     
End Sub 

Then under the workbook settings, check he automatic refresh on Open and in the exits tab Enter Just

RefreshMacro

and nothing else.

This is a hack and now I understand it, What you are doing is making the 7.0 Analyzer think that the 3.5 Refresh Macro is being opened.

I am really shocked that No one in SAP has figured out that the Online Documentation example is incorrect and does not work.

HTH

Datta.

[Former Member]

Dattatreya,

I tried to use this in macro but its not working, Can you guide me through.

Thanks,

Nav

[Former Member]

Hi Dattatraya,

This code works really well - until I put it in a Workbook with more than one Data Provider assigned. It then runs the macro I specify twice! I'm assuming once per DP?

Public Sub AutorunMacro(queryId As String, resultArea As Range)

Call Macro1

End Sub

I got around this by inserting a line of code that kills the second run based on a change made by the first run:

If ActiveSheet.Range("A1").Text = "[New column header]" Then Exit Sub

before the 'Call Macro1' part.

Do you know if there is something I can instead do with the '(queryID As String' bit? Is it possible to specify that it runs Macro1 for only one of the assigned DPs?

Thanks!