cancel
Showing results for 
Search instead for 
Did you mean: 

Replacing # sign in Bex Report.

Former Member
0 Kudos

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?

View Entire Topic
Former Member
0 Kudos

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
0 Kudos

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

please help.

former_member188325
Active Contributor
0 Kudos

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