Replacing # sign in Bex Report.

[Former Member]

Hi Folks,

I want to replace the pound sign (#) with a blank in a bex report.

How can I accomplish that both in Web and Excel based reporting?

[Former Member · Accepted solution]

Hi,

If you are using Excel then you need to use the following procedure:

If you are using the BEX Analyzer the following procedure will be helpful:

-In your Workbook click on Alt+ F11 (open the VB editor)

-Right Click on Modules

-Click on Insert

-Insert a module named SAPBEX

-Paste the following code there:

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 ""

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

End If

End Sub

-Now click on Save

-Save also your Excel workbook

-Then Open your Excel workbook again and enable macros.

In case you use web, you'll need to use etiher Javascript or the Table interface - both quite tedious methods

Hope it helps (if so please assign some points..)

Gili

[Former Member · Accepted solution]

I have done what you suggested by I'm getting compile error, outside procedure.

please help.

[former_member188325 · Accepted solution]

Hi,

Try out this (ref: one of the threads)

Here are the steps to do the same

1) In excel menu Tools->macro

2) Enter the macro name say SAPBEXonRefresh

3) click 'create', will go to visual basic editor

4) To display '#' as '', paste the following code

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim c As Range

For Each c In resultArea.Cells

If c.Value = "#" Then c.Value = ""

Next c

End Sub

5) Close the editor and click on refresh again.

Thanks