Double click in VBA Project area on SAPBEX module. You see the SAPBEXonRefresh subroutine. Its empty.
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 reports 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 reports output is not complex (similar to that shown here) you can use the code. All you have to do is to change the reports 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 BExs 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 Excels menu path: View -> Microsoft Excel.
To run the formatting, click on the Exit Design Mode icon.
Now clicking on the button will execute the code.
After youve done everything described here, you can save a new workbook giving it the description and technical name.
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.
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 didnt set any Lowercase Letters flag in any of our infoobjects for long texts. Its 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. Thats not true. Texts of infoobjects may be shown in lowercase letters regardless of any settings. Though, that might be a theme for another blog.