How to open sap lh1 by using vba excel codes and do trancations

[ahmed18]

Hello All

I want help in Vba coding to Login SAP and to do the Transaction by using excel macro

Steps
1.need to login in SAP with LH1
2.FBl5N -T-code to report Need to extract from SAP
3.export as Excel sheet to Destination Folder

Please help in this at the earliest..
Ahmed_muzamil18@yahoo.com

Thanks in advance

 

[dchaney]

I use the following to log into SAP and run a company specific Tcode, I do not have access to FBL5N here at work so not sure this will work for you or not...

This assumes that LH1 is the name of the SAP Client you are login into...

SAP has a Script recorder that you can use to get the information you require if this does not work completely, here is the link to a document

Sub SAPLoginMacro() 'DESIGNED TO LOG IN & EXTRACT DATA FROM SAP USING USER LOGIN AND PASSWORD

Dim stSapUName As String, stSapPW As String
Dim SapguiApp As Object, connection As Object, session As Object

stSapUName = InputBox("Please enter your SAP User name", "SAP User Name")
stSapPW = InputBox("Please enter your SAP Password", "SAP Password")

On Error GoTo errFailed
    '****************************************************************************************
    'ESTABLISH CONNECTION TO SAP                                                            *
    '****************************************************************************************
    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("LH1", True)
    Set session = connection.Children(0)
    
On Error GoTo 0

    With session
        '************************************************************************************
        'LOGON TO SAP                                                                       *
        '************************************************************************************
        .findById("wnd[0]/usr/txtRSYST-BNAME").Text = stSapUName
        .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = stSapPW
        .findById("wnd[0]").sendVKey 0
    
        '************************************************************************************
        'PROCESSES THE POPUP IF MULTIPLE LOGINS DETECTED                                    *
        '************************************************************************************
        If .Children.Count > 1 Then
            .findById("wnd[1]/usr/radMULTI_LOGON_OPT1").Select
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End If
    
        '************************************************************************************
        'NAVIGATES TO THE TCODE                                                    *
        '************************************************************************************
            .findById("wnd[0]").resizeWorkingPane 105, 31, False
            .findById("wnd[0]/tbar[0]/okcd").Text = "FBl5N"
            .findById("wnd[0]").sendVKey 0
        
        On Error Resume Next
        '************************************************************************************
        'SELECTS THE FILE TYPE SPREADSHEET AND SAVES FILE TO HARD DRIVE                     *
        '************************************************************************************
        .findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell").pressToolbarContextButton _
        "&MB_EXPORT"
        .findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell").selectContextMenuItem "&XXL"
        .findById("wnd[1]/usr/chkCB_ALWAYS").Selected = True
        .findById("wnd[1]/tbar[0]/btn[0]").press
        .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Export.xlsx"
        .findById("wnd[1]/tbar[0]/btn[11]").press
    End With

    On Error GoTo 0
    '****************************************************************************************
    'CLEAR ALL SET VARIABLES                                                                *
    '****************************************************************************************
    Set session = Nothing
    Set connection = Nothing
    Set SapguiApp = Nothing
           
Exit Sub

'********************************************************************************************
'DISPLAY ERROR HANDLING MESSAGE BOX                                                        *
'********************************************************************************************
errFailed:
    MsgBox "The connection to SAP has been halted by the user."
    End
    
End Sub