SAP ALV Excel inplace with macro recording

[aromalr]

Summary

This article explains how we can make use of MS Excel Inplace functionality in the SAP ALV toolbar with macro recording to avoid repetitive tasks such as adding custom calculation field in the standard /Custom/ Query reports (e.g.  Order Qty- Delivery Qty, Pivot Table chart etc)

Introduction

It is quite often that we have downloaded the standard/custom SAP ALV report as Excel document to the front-end computer and added some calculation fields, converting to pivot table etc. These repetitive tasks in Excel can be recorded as an Excel macro and we can upload this excel layout containing macro to SAP as a new ALV layout, so that, when you run the report/selecting the layout, immediately the output will be displayed as Excel with the repetitive task automatically executed. With this we can avoid building of Custom fields (derived fields) in standard / Custom / Query report and save lot of time.

General Settings to enable macro in Excel 2007

When we run the Excel in place functionality in the SAP ALV, by default, the data will not get automatically transferred to Excel. For that we have to do some macro security settings. Following screen shots explains how to enable a macro in excel 2007. After activating this we have to close Excel and run Excel in place functionality once again. Then the data can be seen in Excel. Excel In place will also work in MSExcel 2010 and MSExcel 2003 with Latest GUI (7.20 advisable) + patches installed at frond end computer

Step 1: Select Excel options

Step 2: Go to Trust center settings

Step3: Go to Macro Settings and select details as per following screen shot.

Addition of a custom field in ALV report using Excel Inplace and Macro

    E.g. Adding Rejection% column in standard report (E.g. t-code COID)

In the standard report transaction COID we want to add a Rejection % column. Either we can do it by ABAP code or download the report to excel and do a formula calculation. But there is an easier method than writing code in Enhancement spot/Exits/download to achieve this. Following screen shots explain step by step methods to run a macro along Excel in place functionality.

Step 1: Select Excel in place functionality in the ALV.

Normally in standard /custom report we can also see Excel Inplace functionality like this.

Step 2: After seeing the data in Excel go to RawData tab

Step 3: Now go to view tab and select Record Macro.

Step 4: Give the name for the macro and assign a shortcut key (Ctrl + Shift + L)

Step 5: Write the formula in the cell as shown. Also leave one cell for heading. (It is better to put the Heading after the formula calculation otherwise we have to re-adjust the macro code)

. Eg (ROUND(h2/e2*100,2) which will give you rejection percentage rounded upto 2 decimal place

Step 6: Double click/drag outside cell selection, so that this makes the formula applicable for the entire column.

Step 7: After formula, enter the heading for the column (eg. Rejection %)

Step 8: Apply borders for the column if needed and do necessary formatting

Step 9: Stop recording the macro as shown below. Now the tasks that we have done from step 5 to step 8 has been recorded as macro named RejectionPer (see Step 4).

Step 10: Now you can see the recorded macro in View Macros.

Step 11: Save the Excel in the front end computer

Step 12: Now select change layout to upload the excel macro (alv_macro_eample.xls) in front end machine to SAP .

Step 13: Now go to the view tab and select Microsoft Excel and use Upload to BDS option

Step 14: Select the excel file we saved in the desktop and upload

Step 15: Save this layout by selecting the template we have just uploaded.

Step 16. Now go out of the report and run the report and select the layout we have saved.

Step 17: You can run the macro by pressing the shortcut Ctrl+Shift+L(we assigned it in Step 4)  or go to view tab and run the macro manually.

Step 18: We can see the output now as below

We can automatically run the macro when we select the Raw Data tab in the Excel .For this, Edit the macro and put below piece of code in ThisWorkbook ->Workbook->SheetActivate method (as shown below).

    If Sh.Name = "RawData" Then

        Module1.RejectionPer

    End If

Summary

All the above tasks we can easily do within couple of minutes. If we consider enhancing the standard report and transporting it through the landscapes to PRD or downloading it to excel and do the activities, this functionality saves a lot of time for the consultants/Users.

  Try to record some Pivot table with charts and start exploring.

[jayakumarkb_81]

Good work. keep it up Aromal.

[Former Member]

Hello, I Tried to do this process but the problem i have is during step 13 there is not showing the icon to "Upload to BDS option" i just have details button update view and determine defaul tamplate, do you now how to display the other temples right now is just showing sap_om.xls created in 2000.

[aromalr]

I think it is an authorization issue. Do you have the authorization to save the layout? if not get that authorization.most probably it will come.

[Former Member]

Thanks a lot Aromal, you were right there was an authorization problem. Thank you again  

[timothy_eckersley]

Just to let you know that, thanks to you and your article, I was able to implement 5 custom reports in a customer project with zero ABAP coding. Granted, I did have to make a couple of queries to get the datasets I needed.

The customer is very impressed with the solution in general and particularly with the flexibility it offers. We have been able to tweak the report templates and macros many times to improve on the original designs during customer testing, and we are aiming to hand-off the solution to the customer entirely now they understand how it works. All they need is someone with some knowledge of Excel macros / VB script, which is the real selling point for me.

Many thanks!

[aromalr]

Good to hear that!!!

[Former Member]

Thanks for the Tutorial,

I tried and everything was OK, except for one thing

in Step 15 i can't see the SAVE button, anyone know what could it be? I think it's not authorization problems because in my test environment i have SAP_ALL.

I only can use my Excel templates loading the files every time i open a report.

Thanks in advance

best regards.

[aromalr]

Hi,  Could you please take any other report and check wheter save layout option is there? If not then you don't have sufficient privilege. Otherwise let me know which sap gui version you are using. Also try it in some other machine if you have SAP_ALL privilege.   Regards Aromal R

[timothy_eckersley]

Hi Ricardo,

You just need the authorization object S_ALV_LAYO = '23' to be able to save global ALV layouts.

If you want to upload custom Excel templates so they can be used globally in the SAP system, you can upload them to BDS with the file name prefix 'cus_' as customer standard templates. You can do this with report BCALV_BDS_UPLOAD and manage them thereafter from transaction OAOR. You must execute transaction OAOR with the criteria below:

Class name: ALVLAYOUTTEMPLATES

Class type:  OT

Object key: CUS_STANDARD_TEMPLATE

You can see some examples of 'CUS_' templates in Aromal's screenshot above in Step 15. Once you have uploaded a customer standard template, assigned it to an ALV layout and saved this layout, you can apply this template each time without having to upload it again.

Hope this helps!

Tim

[Former Member]

Thanks everyone... I solved by saving the template from the menu.

I was confuse because if i upload a template, only works that time and when i open again the report again i cant see my template.

But when i save the template, i open again the report, the template is not there until i call the variant (I supose that is the normal behavior, isn't it?).

Timothy, Thanks! That's almost what i want

When i execute OAOR i think in a format like:

_20130520163046USERTEST5199C0C37DC5151DE10000000

I see all the files i uploaded.

Now i have two questions:

1. Can i upload a Custom template only for a report? (Maybe upload some templates especific for a report, and another ones to other reports?)

2. If all my previous uploads are there, is there a way i can use that? or why are they uploaded, do i have to delete them every now and then?

Thanks!!!

Best Regards!

Ricardo Leon

[Rajuyr]

Very useful. Thanks for creating this document.

[timothy_eckersley]

When you upload templates via ALV reports they are assigned the kind of long and random document id you mention above. If you upload them via the upload program I mentioned, you can assign any name you like, as long as it begins with the prefix 'CUS_'.

To answer your questions:

1. You can't upload a template just for use in one report. All templates you upload to BDS will be visible in the list of templates you see in the ALV report. You can, however, make one template the default to use with a given ALV layout. In Step 15 above, on the View tab there is a button between Refresh and Download from BDS. Select your template, push this button and your template will appear in the Template Selected field at the bottom. If you then save the ALV layout, this template will be your default for that ALV layout only.

2. If you upload from the ALV report, even the same template file will be given an new random id like the one you mention above. If you want to reuse templates you should upload them to BDS as customer standard templates with the upload program I mention. This way, you can assign meaningful document id's, keep versions of the same template or overwrite a version of an existing template, so you don't get loads of rubbish building up over time. You can also edit templates directly and delete the ones you don't need any more in transaction OAOR.

[suman_sardar2]

Really useful.

A new thing to learn.

Thanks for sharing.

[former_member210879]

Thanks for creating this document

[Former Member]

Nice blog.

Instead of using vb script you can also save the Excel file with macros active (xlsm) and then upload in SAP ALV grid.

You can add SAP as a trusted publisher.

Check trusted publishers in Excel:

1. Click the Microsoft Office Button, and then click Excel Options.
2. Click Trust Center, click Trust Center Settings, and then click Trusted Publishers.
3. In the Trusted Publishers list, click the name of the publisher whose certificate you want to view, and then click View.

I think this will allow you to keep the Macro switched off expect for trusted publishers.

[aromalr]

Thank you Dennis . Your suggestions are much appreciated.It is a very good option if we ate using Ms excel 2007+ versions.

Regards

Aromal Raveendran

[Harsh9]

Hello,

Firstly, very nice tutorial. Kudos to that.

In my development system, I got through the solution. But in production system, user is not being able to see the "Upload to BDS" option while uploading the excel template.

I read in comments written upwards that it is related to authorization issue. But can you throw some more light on this.

Regards,

Harsh

[aromalr]

Hi Harsh,

Check the authorization object "S_BDS_D" or "S_BDS_DS"

Regards

Aromal R

[Harsh9]

Hi Aromal,

Thanks, it is resolved.

Regards,

Harsh

[jj]

nice document. I tried and worked for me. :smile:

[aromalr]

Thanks Joffy.

[former_member210879]

nice document

[former_member210892]

Hi Raveendran,

After we upload the custom excel, everything is fine except that we get an additional "Header" Tab which we do not want. Is there anyway to delete this "Header" tab as well

[former_member210892]

How can we delete the "Header" tab from the Excel sheets. Even if we delete the tab and upload the custom excel, this Header Tab keeps coming.

[Former Member]

I put code at the end of my things to hide that sheets...

[former_member210892]

Thank you Ricardo. See this custom ALV that we have just has one tab that we need across all reports in the system. So along with this one tab , every report fetched gives a Header tab also. Can this be done system wide to remove the "Header" Tab.

[Former Member]

as long as i remember, there is a place in customizing where you put the default formats (sap_om, sap_mm) if you manage to replace them with one with the code at some point to hide that ... sounds possible, not really complex and feasible... in the other hand I only put that function at the end of my code in every custom format i upload, and works fine... it's easy and works

[former_member210892]

Thanks Ricardo. Could you help me with this custom code to hide this template and where we need to put the code in the custom excel template.

[Former Member]

Before the "End Sub"

     Sheets("Header").Visible = False

Should do the trick.... I done this a long time ago and apparently lost the sources... so... i'm not really sure ... sorry

[wernervm]

Hi Aromal;

Great tutorial! Thanks

I do have a question.

I've completed a macro which represents data in pivot table format (sheet "pivot" = active sheet). Works 100% in foreground.

I then scheduled batchjob (SM36) with correct variant and program to send (email report) to users daily.

Problem is that HTM file attached to email only shows one sheet, i.e. think its Header.

Any idea if it's possible to show "pivot" sheet in attachement (as defined in macro)?

Kind regards

Werner

[Former Member]

the macro runs in the client side... so i'm afraid it's not possible 😞

[Former Member]

Not recommend but think it is possible to use windows task scheduler to start SAP GUI and run the report then download the file to a shared folder (might need to do some ABAP work to get download working).

[former_member700353]

Hi, very good job. Everything works for me perfectly.

I have a single detail, once I apply the macro from the layout selection, the resulting file I want to download and save on my PC. How can I do it?

your help please! since when I record the macro in theory I save the file and then I stop the recording but when I execute it it does not get there.

 

Sub Macro1()
'
' Macro1 Macro
'

'
Application.DisplayAlerts = False
Sheets(Array("Format", "Header", "Pivot", "Sub1", "Sub2", "Sub3", "Sub4", "Sub5", _
"Sub6", "Sub7", "Sub8", "Sub9", "Sub10")).Select
Sheets("Sub10").Activate
ActiveWindow.SelectedSheets.Delete
Sheets("RawHeader").Select
ActiveWindow.SelectedSheets.Delete
Sheets("RawData").Select
Application.DisplayAlerts = True

Sheets("RawData").Name = "ZBRG"
ActiveWindow.SmallScroll Down:=-12
Range("A2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=-120
Range("G2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=-108
Range("G2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=-117
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll Down:=-3
Range("J13").Select
ActiveWindow.SmallScroll Down:=-12
Cells.Select
ActiveWorkbook.Worksheets("ZBRG").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ZBRG").Sort.SortFields.Add Key:=Range("G2:G118"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("ZBRG").Sort
.SetRange Range("A1:P118")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J9").Select

Dim MyFile As String

MyFile = ActiveWorkbook.Name
' Do not display the message about overwriting the existing file.
Application.DisplayAlerts = False
' Save the active workbook with the name of the
' active workbook. Save it on the E drive to a folder called
' "User" with a subfolder called "JoeDoe."
ActiveWorkbook.SaveAs Filename:="C:\Users\Lin\Downloads\" & MyFile
' Close the workbook by using the following.
ActiveWorkbook.Close

End Sub