Skip to Content
At last, we arrived to the VBA code.

See the first part here:

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

SAPBEXonRefresh subroutine code

Double click in VBA Project area on SAPBEX module. You see the SAPBEXonRefresh subroutine. It’s empty.

image

Replace the code, so the whole subroutine looks like the following.

Formatting code

The following is a code I placed into the CommandButton1_Click subroutine.

There are also several routines and a function.

The code copies the report’s header and footer without changes (with appropriate column width and row height). Then rows of the report are copied with applying of the format in the format row.

In the beginning of the routine the code tries to determine the numbers of start and end rows and columns by using global variable gResArea that was filled out during the workbook refreshing. If this variable is not set (for example, during the 2nd run of the program while debugging), the numbers saved in the 2nd row of “BEx” worksheet are used.

The whole code, I believe, is rather universal. It might be not perfect but it works.
If your report’s output is not complex (similar to that shown here) you can use the code. All you have to do is to change the report’s header and footer in ‘Template’ worksheet and change parameters enclosed by asterisks and with the comment “ PARAMETERS TO BE CHANGED” (in CommandButton1_Click subroutine).

There are some notes regarding these parameters.

  • iHeaderLastRow (Header Last Row), iFooterFirstRow (Footer First Row), iFooterLastRow ( Footer Last Row), iFormatRow (Format Row Number) are self-explaining.
  • iLongText1 (First Column of Long Text) and iLongText2 (Last Column of Long Text) designate the first and last columns between which the peaces of long description are located. If you set both of them to zero, there will not be any concatenation.
  • RptHeaderRowsToIgnore (Number of rows in the BEx output to ignore) is a number of rows of the BEx’s output header (not navigation block), with the names of columns. We replace these rows by formatted header (in ‘Template’ worksheet).

    I believe that proposed here solution will allow formatting the most of reports very quickly. Moreover, this will make such a job unprecedently cheap.

More complex reports

I used to format much more complex reports. For example:

  • With hierarchies, where I needed instead of BEx hierarchy with indents to show each level of the hierarchy in a separate column.
  • With fixed formatted columns.
  • With complex column names replaced during the query run (for example, names of periods).
  • Reports which I had to show in two sets of columns.
  • Reports with Top N (or Bottom N) calculation along with total amounts and Total – Top N.
  • With replacement of values (for example, replacement of blank or zero values with ‘-‘).
  • With using custom fonts or colors.

Last preparations

To return from Visual Basic editor to Excel you can either click on the appropriate window or use the Excel’s menu path: View -> Microsoft Excel.

To run the formatting, click on the ‘Exit Design Mode’ icon.

image

Now clicking on the button will execute the code.

After you’ve done everything described here, you can save a new workbook giving it the description and technical name.

image

Formatted output

The output of our formatted report is nicely looking. The header a little differs from the template since I had to decrease the columns width in the template after report formatting for better visualization in the blog.

image

Pay attention to the fact that this is a static report, as needed for print. Drilldown of the BEx output and the appropriate changes of the formatted report are not supported. For this you must write much more sophisticated code.

In the output we have both, lowercase and uppercase letters, though we didn’t set any ‘Lowercase Letters’ flag in any of our infoobjects for long texts. It’s another advantage in using the long texts modeling scheme described in the first blog.

There is a very common delusion that if the text to be loaded contains lowercase letters, one either should use uppercase letters only or apply this lowercase letters flag in infoobject maintenance. That’s not true. Texts of infoobjects may be shown in lowercase letters regardless of any settings. Though, that might be a theme for another blog.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Kaspars Osis
    Hi, Eugene!
    All in all this is very helpful info.

    However I have a very simple issue – I just don’t see this “SAPBEX” module you are mentioning at the very begining of your post. Not even thinking about SAPBEXonRefresh subroutine I wanna use.

    I have gone through posts by Peter Knoer, too…
    We have Excel 2003; BI 2004s.

    “Module1” module is created when I add a module to my workbook. In this Module1 I’m adding then SAPBEXonRefresh subroutine. When I restart RRMX or Refresh the workbook, then the test code within the SAPBEXonRefresh is not being executed.

    Do you have any idea what it might be that I’m missing.

    Thanks in advance!
    With regards,
    — Kaspars

    (0) 
    1. Hi Caspars,

      Have you set an Excel security, as mentioned in the 1st part of the blog?

      Best regards,
      Eugene

      (0) 
      1. Kaspars Osis
        Hi Eugene,
        yes sure – I have even set the security level to Low. Also ‘Trust access to Visual Basic Project’ flag has been set …

        With regards,
        — Kaspars

        (0) 
        1. Hi Kaspars,

          Didn’t work with BEx 2004s yet.

          Try to insert SAPBEXRefresh sub and see if it works during refresh.

          See also some additional info gere:

          /message/1718729#1718729 [original link is broken]

          Re: VBA Interface & More

          Come back with feedback.

          Best regards,
          Eugene

          (0) 
          1. Kaspars Osis
            Hi Eugene,
            SAPBEXRefresh didn’t do any difference either;

            However your first reference had the solution (part of it)! It seems that the issue was that I had to switch to Design Mode; -> then select Workbook Settings; and then in the tab Exits had to enter a name of the macros (i.e. SAPBEXonRefresh).

            Second part was that in the same Workbook Settings windows in the tab General had to check “Refresh Workbook on Open” flag. Once done that, then it indeed showed the result of test code on open (otherwise only on refresh).

            So, thanks a lot for your help!

            With regards,
            — Kaspars

            P.S. I’ve never have given points in SDN yet; didn’t seem to be very obvious how to do it. Maybe you could give me a hint … thx!

            (0) 
            1. Hi Kaspars,

              I’m glad that was of a somewhat help to you.

              You can reward points to the answers to your questions posted in forums. Blogs are somewhat different.

              Good luck in your work!

              Best regards,
              Eugene

              (0) 
  2. Victor Figueroa
    Hi, Eugene!

    i am use VBA code for making the formated reports, now i have big problems.

    Need Capture the 3 extense long texts related to zcostcenter value.
    So and insert that long texts in workbook when filter is using zcostcenter value.

    Long text have media 255 a 300 Characters, using any values ASCII.

    Any idea for my problem ?
    Thank you for you time.

    (0) 

Leave a Reply