Automate SAP Storage Bin Corrections with VBA Scripting

Introduction:

During a recent physical inventory review, we discovered an incorrect assignment of materials to storage bins in our SAP system. To address this efficiently, I developed a VBA (Visual Basic for Applications) script that automates the correction process for several thousand materials. This blog post details the script and provides guidance on its usage.

The Problem: We identified that numerous materials were assigned to incorrect storage bins within SAP, requiring a massive manual correction effort.

The Solution: To streamline this process, I created a VBA script that interfaces with SAP's 'LT10' transaction. This script adjusts the storage bin assignments in bulk, saving considerable time and reducing the potential for human error.

VBA Script Overview:

Sub Execute_LT10()
    Dim SapGui
    Dim Application
    Dim connection
    Dim session
    Dim WSHShell
    Dim ObjR3
    Dim Grid
    Dim sap_message
    Dim lastRow As Long
    Dim i As Long
    Dim inputString As String
    Dim outputNumber As String
        
    ' Assuming you want to write the sap_message to cell A2 in "Sheet1"
    Dim DestinationSheet As Worksheet
    Set DestinationSheet = ThisWorkbook.Sheets("Sheet1")
        
    lastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
        
    Set WSHShell = Nothing
    Set SapGui = GetObject("SAPGUI")
    Set Application = SapGui.GetScriptingEngine
    
    Set connection = Application.Children(0)
    Set session = connection.Children(0)
    
    ' Start of LT10
    session.findById("wnd[0]").resizeWorkingPane 130, 29, False

    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nlt10"
    session.findById("wnd[0]").sendVKey 0
    
    session.findById("wnd[0]/usr/ctxtS1_LGNUM").Text = "015"
    session.findById("wnd[0]/usr/ctxtS1_LGTYP-LOW").Text = "205"
    session.findById("wnd[0]/usr/ctxtS1_LGPLA-LOW").Text = ""
    
    ' Loop through each row in the Excel sheet
    For i = 2 To lastRow
            session.findById("wnd[0]/usr/ctxtMATNR-LOW").Text = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
            session.findById("wnd[0]/usr/ctxtMATNR-LOW").SetFocus
            session.findById("wnd[0]/usr/ctxtMATNR-LOW").caretPosition = 10
            session.findById("wnd[0]/tbar[1]/btn[8]").press
            session.findById("wnd[0]/tbar[1]/btn[45]").press
            session.findById("wnd[0]/tbar[1]/btn[48]").press
            
            
            On Error Resume Next
            session.findById("wnd[1]/usr/chkRL03T-SQUIT").Selected = True
            session.findById("wnd[1]/usr/ctxtLAGP-LGTYP").Text = "205"
            session.findById("wnd[1]/usr/ctxtLAGP-LGPLA").Text = ThisWorkbook.Sheets("Sheet1").Cells(i, 2).Value
            session.findById("wnd[1]/usr/chkRL03T-SQUIT").SetFocus
            session.findById("wnd[1]/tbar[0]/btn[0]").press
           On Error GoTo 0
                        
            session.findById("wnd[0]/tbar[0]/btn[3]").press

    Next i

    
End Sub

One straightforward step is to execute the macro in order to transfer from the incorrect storage bin in the system to the designated physical storage bin. Please carefully monitor for any unexpected errors while the macro is running.

One more thing, if you want to see SAP script 'LT10', please check the SAP script below:

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session,     "on"
   WScript.ConnectObject application, "on"
End If
session.findById("wnd[0]").resizeWorkingPane 130,29,false
session.findById("wnd[0]/tbar[0]/okcd").text = "/nlt10"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtMATNR-LOW").text = "SKDDCT0028"
session.findById("wnd[0]/usr/ctxtMATNR-LOW").setFocus
session.findById("wnd[0]/usr/ctxtMATNR-LOW").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[45]").press
session.findById("wnd[0]/tbar[1]/btn[48]").press
session.findById("wnd[1]/usr/chkRL03T-SQUIT").selected = true
session.findById("wnd[1]/usr/ctxtLAGP-LGTYP").text = "205"
session.findById("wnd[1]/usr/ctxtLAGP-LGPLA").text = "L01-07-01"
session.findById("wnd[1]/usr/chkRL03T-SQUIT").setFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press

Key Features of the Script:

• Automated Connection to SAP: The script establishes a connection to SAP and navigates to the relevant transaction.
• Error Handling: Includes logic to handle cases where there's no stock available for transfer, thereby skipping such records.
• Loop Through Excel Data: Iterates over a list of materials in an Excel worksheet, applying corrections for each.

Execution Instructions:

1. Prepare an Excel sheet with the list of materials and their correct storage bin assignments.
2. Open the SAP GUI and ensure you have the necessary permissions to run the 'LT10' transaction.
3. Run the VBA script. Monitor the execution for any unexpected errors and ensure the process completes successfully.

Safety Precautions:

• Test the Script: Always test the script in a non-production environment before applying it to live data.
• Backup Data: Ensure that you have a backup of your SAP data before running the script.

Conclusion: This script is a practical solution for mass updating storage bin assignments in SAP. It demonstrates how automation can significantly reduce the time and effort required for data correction tasks in enterprise systems. Please test thoroughly before use and adapt as necessary for your specific SAP environment.