on 11-12-2008 1:54 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.