cancel
Showing results for 
Search instead for 
Did you mean: 

How to do a Macro in BEx Excel Analyzer

Former Member
0 Kudos

Does anybody have easy worksteps and the code to put in a Macro on an Bex Analyzer workbook.

I have a query on a workbook where there are blanks (e.g. no data) and I want it show zero. It's a payroll costing report and for crtain months against certain GL's there are no costings. It's not that the spreadsheet isnt showing zero's. It's that there physically is nothing against the Amount key figure for that month. So it's no good trying calculated key figures to get it to show zero. It cant show zero if there is no actual key figure for that month. I'm not supressing zero's and display zero's in turned on in Excel.

I want to use a macro in Excel to look at the query I've inserted and where a cell is blank I want it put in zero. I dont know how to use VB or Macro's so any worksteps and code etc would be much appreciated.

Thanks

Joel

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Joel

You can check your security setting in Excel. Perhaps this can solve the problem.

Goto Tools|Macro|Security and set security level to Medium or Low. Also tick in "Trust access to VB projects" in the Trusted Publishers tab.

Note:

Sometimes Companies put restriction on the level of macro Security level. Then you might need your macro signed.

Good Luck

/Pontus

Former Member
0 Kudos

I noticed in SAP Help the following:

Exits

On the Exits tab page, you can choose whether a macro can be called during the refresh. You can choose any macro name. Note that the macro is only called when the workbook is opened if the Refresh Workbook on Open indicator is set on the General tab page.

The signature of the macro must be as follows: Sub <MACRO_NAME>(ParamArray varname())

<MACRO_NAME> is a place holder for the name of the macro, which can be any text of your choice, for example, RefreshMacro.

The name of the data provider is in varname(0).

The range is contained in varname(1).

Example:

Sub RefreshMacro(ParamArray varname())

Dim lData_Provider As String

lData_Provider = varname(0)

Set lRange = varname(1)

MsgBox lData_Provider

End Sub

Here it says the signature should be Sub <MACRO_NAME>(ParamArray varname()) but I've got Sub SAPBEXRefresh(queryID As String, resultArea As Range)???

Is this the signature about which I'm getting an error? Or is this signature error more to do with the security surrounding Macro in Excel? I'm confused.

I've set my Excel to Low on Security, to allow VB script and SAP AG.

Former Member
0 Kudos

Hi Joel,

For Some reason the help SAP has does not work. before I upgraded from 6.4 GUI to 7.1 I had got this work, but the same code is now not working for me.

Before I had put in Just the Macro Name and left every thing out in the Exits Tab. Not sure why its not working will post back if I find a Solution.

This was my Code and in the Exits Tab I Just had MyFunc

 
Public Sub MyFunc(queryId As String, resultArea As Range)
    MsgBox "Query = " & queryId
    
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = Now
End Sub

Former Member
0 Kudos

This code works fine for changing cells to zero.

Sub SAPBEXRefresh(ParamArray varname())

Dim queryID As String

Dim resultArea As Range

queryID = varname(0)

Set resultArea = varname(1)

Dim c As Range

For Each c In resultArea.Cells

If c.Value = "" Then c.Value = "0"

Next c

End Sub

However I now realise I dont want to do all cells only certain columns of the query e.g. the key figures. Is there anyway I can restrict my code to certain columns or certain cells?

Former Member
0 Kudos

Hi Joel,

You can Pick out the Columns from the Range by using

 
resultArea.columns(1)

And then set it to 0

Can give us Step by Step Instruction as to how you got this work.

I tried it and I am still getting Macro not found Error.

Former Member
0 Kudos

We've got this working. This is the code we've used.

Sub SAPBEXRefresh(ParamArray varname())

Dim queryID As String

Dim selectArea, resultArea As Range

Dim x As Long

queryID = varname(0)

Set resultArea = varname(1)

Set selectArea = resultArea.Columns(7)

For x = 8 To resultArea.Columns.Count

Set selectArea = Union(selectArea, resultArea.Columns(x))

Next x

Dim c As Range

For Each c In selectArea.Cells

If c.Value = "" Then c.Value = "0"

Next c

End Sub

It kicks in on Column 7.

Answers (1)

Answers (1)

former_member205352
Active Contributor
0 Kudos

Check this blog :

You may get an idea as to how to write macro.

Hope this helps.

Former Member
0 Kudos

Thanks . That doc was helpful.

I've created a module in VBA called SAPBEX and I've added in a bit of code I've lifted from and post. The code is:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

' First Query in the workbook

If queryID = "SAPBEXq0001" Then

' Selects the area where the report query is rendered

resultArea.Select

'Replace "" with 0

Selection.Cells.Replace What:="", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=True

End If

End Sub

But I changed SAPBEXonRefresh to SAPBexRefresh as I'm on 2004s. But now I'm getting the error

Diagnosis

Macro SAPBEXREFRESH does not have the correct signature. The workbook property specifies a macro that is to be called when the workbook is refreshed. However, the specified macro has a different signature to the one specified in the BEx Analyzer documentation.

System Response

The BEx Analyzer tries to call the macro and returns a warning that the macro has an incorrect signature.

Procedure

1. Check whether the workbook contains the macro specified in the workbook properties.

2. If the workbook contains the macro, correct the signature of the macro to the correct parameters as specified in the BEx Analyzer documentation or remove the macro name from the workbook properties.

Procedure for System Administration

Any ideas?