Skip to main content

Increase efficiency and speed of your sap support tasks with the help of Microsoft Excel and VBA

Background
SAP Tcode SM30, is a very widely used transaction. Many IMG activities are simply update of table views using SM30. For example MRP controller IMG activity is nothing but sm30 on sap table view V_T024D. Apart from IMG activities, most of the SAP installations use many custom tables which are maintained using SM30. usually, the volume of such IMG activities in a support environment is a few records. As mentioned earlier, the amount of  MRP controllers that needs to be created when a new plant is created is usually huge. Here comes the problem. Many medium sized companies use a couple of 100 mrp controllers. The same problem happens when the business wants to add a new MRP controller to all existing plants. This again is easily a couple of hundreds. Moreover MRP controller IMG activity is not a table control; it has to be added one by one. This trick explained in this blog is very useful and saves time if the number of records exceeds hundred. This is limited to inserting new records. Editing records using SM30 is out of scope of this article.

Concept
Speed is achieved using Excel and VBA. Apart from some minor differences, all SM30 transactions have a standard structure. If you look at the recording of any SM30 transaction, you will notice the following structure

The tcode and the table/viewname and  OKCODE for selection of update option comprises the fixed portion of the recording. This is same for any SM30 view or table or even IMG. I have not encountered any thing different than this.
                                              T SM30                                         
SAPMSVMA 0100 X                                                                                                                                     
                                              BDC_CURSOR
                                              BDC_OKCODE /00
                                              VIEWNAME
                                              VIMDYNFLDS-LTD_DTA_NO X
SAPMSVMA 0100 X                                                                                                                                     
                                              BDC_CURSOR
                                              BDC_OKCODE =UPD
                                              VIEWNAME
                                              VIMDYNFLDS-LTD_DTA_NO X


This is s followed by multiple occurrences of BDC screens for each record added. This is different for each table/view.

For MRP controller it looks like this

SAPL0PP3 0060 X                  
                                     BDC_OKCODE =NEWL
SAPL0PP3 0061 X                  
                                     BDC_OKCODE =BACK
             V_T024D-WERKS
             V_T024D-DISPO
             V_T024D-DSNAM


All that is required is a small VBA script which should get the fixed portion first and build the variable portion of the recording merging with the actual data.

How to do steps

  • We need a microsoft excel workbook with three sheets
    • Sheet1 - Recording. This sheet should contain the skeleton of the sm30 recording. basically fixed portion and the repeating portion
Fixed portion


Repeating portion


    • Sheet 2 - Structure. This should contain some details required to generate the BDC
      • Program name - get it from the sm30 recording
      • Table name - the table or view name
      • screen number - of the repeating section. in some cases there 2 screen numbers (example MRP controller)
      • table field names
    • Sheet 3 - Data. Should have the actual data for the Sm30
  • Enter the details in Sheet Structure, from the recording


  •  Structure sheet should specify the links to data column and the field name. The structure below shows that the data sheet contains Plant in column A MRP controller in column B and Name in Column C.It is assumed that the data sheet contains column header and so row 1 in data sheet is ignored. 

    • Tables / Views where sm30 uses table control, requires suffix (01) to the field name. For example Weighting groups uses table control. field names should be
      • A - GEWGR(01)
      • B - COUN2(01)
      • C - GWFAK(01)

  • Copy the data into the DATA sheet of this workbook and run macro Generate_bdc_session. This macro will create a new sheet "Session" with the BDC data for the transaction. Save this sheet as a text file and import into SHDB and process using option "display Errors".
  • Important thing to note here is that the data is not saved. After the process is complete you can check the created data and save if all ok.
I am happy to share this sheet along with macro - send a request in the comment. I am happy if you refer back this blog.



Comments

Post a Comment

Popular posts from this blog

Vendor Consignment Business Process

Wikipedia offers a very simple definition for consignment . It is a process of placing a thing in the hand of another, but retaining the ownership until the goods are sold or used. In SAP consignment business process can be implemented very easily using the following steps. Activate Consignment inforecords using tcode OMEV. This activates the consignment prices to be picked up using the consignment info records. configure a special procurement type using IMG->Production->MRP->Master Data->Define special procurement type. In OBYC configure the consignment payables account (transaction KON). Assign the special procurement type created in step 2 to all the materials that are to use the consignment process. This is done in the MRP2 view of the material master maintenance transaction Create Consignment info record using tcode ME11. Select info category as consignment. Create general data, Purchase Org data and conditions. Conditions allow you to create time dependent prices. Cre
16 comments
Read more

Stock Differences in HU (Handling Unit)

This blog post might help you to solve the problem with these messages. HUGENERAL 017 Handling unit &1 has the status "goods issue posted", cannot be changed HUFUNCTIONS 251 No posting-relevant material items could be found Many times it happens that the standard functionality provided by SAP is not sufficient. IN HU if customisations have been done, it is possible that, you end up with some data inconsistancy. The worst part about this is that not much is available in SAP or even internet. I learnt these hard way. The solution might look very simple, but it has consumed quite a lot of tense days. This could be due to some MDE customisations also. The above messages might appear if you are trying to transfer HU from blocked to unrestricted or vice versa using VLMOVE. Or if you are trying to pick a HU from stock. What happened in my case is that the inventory stock was transferred to Blocked stock, but the HU was not updated. So, the following standard transactions gives
2 comments
Read more

LSMW Tip

This tip is very useful for Master Data LSMWs that updates many fields. Usually it happens that during a specific project we realise a need to update specific fields. We design these LSMWs in such a way that these values supplied in the input file is updated in the master file. Later on we try to reuse the LSMW for updating a subset if these fields. If we leave the values of other fields blank in the input file, depending on how the LSMW conversion rules were designed, the the master data will be updated with initial values. Common practice in many organisations is to create a new LSMW for the new set of fields. This is not required if the LSMW uses Direct Input. Direct input uses a special character to denote no value. The default is "/". This allows us to reuse the LSMW. Reformat the input text file and fill fields that are not to be disturbed with this character. For example if the LSMW is for updating the following fields 1. storage bin 2. picking area 3. reord
2 comments
Read more