Skip to Content
avatar image
Former Member

How to do a Macro in BEx Excel Analyzer

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 12, 2008 at 03:20 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.

  • Nov 12, 2008 at 02:02 PM

    Check this blog :

    Long Texts in SAP BW: Displaying in BEx Analyzer. Introduction to Excel Workbooks Formatting. Part I.

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

    Hope this helps.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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?