16 Replies Latest reply: Jul 31, 2014 6:27 PM by umur erkmen RSS

Logon to SESSION MANAGER in SAP with VBA

Suseno Agung
Currently Being Moderated

Hi...

I have Excel macro which extract data from SAP using transaction code via session manager , so when run this macro it's should manually log onto sap first then pop up session manager , the macro then can work smoothly.

 

Now, I want  Excel macro run automatically at night thus need to auto logon to sap until pop up session manager screen.

Please help me if you have code to enable excel macro to auto logon to sap or pls guide me to link which provide more details explanation on how to log on to session manager in sap.

 

However, I have dig into internet for couple of days to find some clues but still can not find it yet. the only code that during my trial can work is below code , but it's works under  vbs file and when translated to excel vba can not works.

 

If Not IsObject(SAPguiApp) Then
    Set SAPguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
End If
If Not IsObject(Connection) Then
    Set Connection = SAPguiApp.OpenConnection("SYSTEM", True)
End If
If Not IsObject(Session) Then
    Set Session = Connection.Children(0)
End If
Session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "USER"
Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "PASSW"
Session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
Session.findById("wnd[0]/usr/txtRSYST-LANGU").SetFocus
Session.findById("wnd[0]/usr/txtRSYST-LANGU").caretPosition = 2
Session.findById("wnd[0]").sendVKey 0

 

Thank you .

 

Best regards,

Agung

  • Re: Logon to SESSION MANAGER in SAP with VBA
    Michael Hobbins
    Currently Being Moderated

    Hello Agung, there are some differences between VBS and VBA, but they are very small. Here you have an example of code to call a transaction from VBA. The key lines are the first 10 lines I'm posting below (the rest should be the same you're actually using.

     

        Dim rfcctl
        Dim conn
        Set rfcctl = CreateObject("sap.functions")
        Set conn = rfcctl.Connection
        conn.Client = "100"
        conn.hostname = "sap_prd"
        conn.user = "user"
        conn.password = "password"
        conn.Language = "EN"
        conn.SystemNumber = "00"
    
        If conn.Logon(0, True) Then
            Set rfc = rfcctl.Add("....")
       'etc...

     

    Cheers,

    Michael

  • Re: Logon to SESSION MANAGER in SAP with VBA
    Script Man
    Currently Being Moderated

    Hi Agung,

     

    I am running the published code without problems in Excel with VBA. What is the exact error to you?

     

    Regards,

    ScriptMan

    • Re: Logon to SESSION MANAGER in SAP with VBA
      Suseno Agung
      Currently Being Moderated

      Hi ScriptMan,

       

      Thanks for running the code..

      My last try was using below vba code and it's still get an error on the "sapconnection.client=.."  etc.

      Could you pls take a look at the code and would really appreciate for the correction...

       

      Sub Logontrial()
      
      Dim SapGuiApp As Object
      Dim oConnection As Object
      Dim session As Object
      Dim SAPCon As Object, SAPSesi As Object
      Dim SAPGUIAuto As Object, SAPApp As Object
      Dim sapConnection As Object
      
      If SapGuiApp Is Nothing Then
          Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")
      End If
      
      If oConnection Is Nothing Then
          Set oConnection = SapGuiApp.OpenConnection("110 PRD-P01 ECC", True)
      End If
      
      'If IsObject(session) Is Nothing Then
      'If session Is Nothing Then
      '    Set session = oConnection.Children(0)
      'End If
          
       sapConnection.Client = "100"
       sapConnection.User = "USER"
       sapConnection.Password = "PASSW"
       sapConnection.Language = "EN"
        
       If sapConnection.Logon(1, True) <> True Then
         MsgBox "No connection to R/3!"
       End If
         
          'Set SAPCon = SAPApp.Children(0)
          'Set SAPSesi = SAPCon.Children(0)
          Application.DisplayAlerts = False
          
          With SAPSesi
              
          'start extraction
          
          .findById("wnd[0]").maximize
          .findById("wnd[0]/tbar[0]/okcd").Text = "/NZGLLISTBRULE"
          .findById("wnd[0]").sendVKey 0
           
          ...
          etc
          ...
          
          End With
            
           Application.DisplayAlerts = True
      
      End Sub

       

      Thank you

       

      • Re: Logon to SESSION MANAGER in SAP with VBA
        Script Man
        Currently Being Moderated

        Hi Agung,

         

        You should not mix the elements of RFC with SAP GUI Scripting. The following suggestion comes from SAP GUI Scripting.

         

        Sub Logontrial()
         
        Dim SapGuiApp As Object
        Dim oConnection As Object
        Dim session As Object
        Dim SAPCon As Object, SAPSesi As Object
        Dim SAPGUIAuto As Object, SAPApp As Object
        'Dim sapConnection As Object
        
        If SapGuiApp Is Nothing Then
            Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")
        End If
        If oConnection Is Nothing Then
            Set oConnection = SapGuiApp.OpenConnection("110 PRD-P01 ECC", True)
        End If
        If SAPSesi Is Nothing Then
           Set SAPSesi = oConnection.Children(0)
        End If
            
         'sapConnection.Client = "100"
         'sapConnection.User = "USER"
         'sapConnection.Password = "PASSW"
         'sapConnection.Language = "EN"
          
         'If nsapConnection.Logon(1, True) = True Then
         '  MsgBox "No connection to R/3!"
         'End If
           
          
            'Set SAPCon = SAPApp.Children(0)
            'Set SAPSesi = SAPCon.Children(0)
            Application.DisplayAlerts = False
            
         With SAPSesi
                
            .findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
            .findById("wnd[0]/usr/txtRSYST-BNAME").Text = "USER"
            .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "PASSW"
            .findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
            .findById("wnd[0]").sendVKey 0
                
            'start extraction
            
            .findById("wnd[0]").maximize
            .findById("wnd[0]/tbar[0]/okcd").Text = "/NZGLLISTBRULE"
            .findById("wnd[0]").sendVKey 0
             
            '...
            'etc
            '...
            
            End With
              
             Application.DisplayAlerts = True
             MsgBox "After clicking OK, this SAP session is terminated."
        End Sub

         

        Regards,

        ScriptMan

        • Re: Logon to SESSION MANAGER in SAP with VBA
          Suseno Agung
          Currently Being Moderated

          Hi ScriptMan,

           

          Thanks , It's works.

           

          Best regards,

          Agung

        • Re: Logon to SESSION MANAGER in SAP with VBA
          Alexander Fokin
          Currently Being Moderated

          Hello Script Man

           

          I get a run-time error while trying to repeat your code.

           

          The error appears at the step "Set oConnection = SapGuiApp.OpenConnection("110 PRD-P01 ECC", True)". Ofcourse I have a different system ID and I put it correspondingly.

           

          I got a pop up with "run-time error '1000' " message.

           

          Do you have any suggestions\ideas? Can it be related to some missing .OCXs or .DLLs files in the registry?

           

          Many thanks.

           

          Alex

        • Re: Logon to SESSION MANAGER in SAP with VBA
          Anu Venkatraman
          Currently Being Moderated

          Hi Scriptman,

           

          I want to restrict my code to run if anyother user is logged on in SAP while running the code.

           

          How to do this?

          Now I am running the SAP logon code successfully.

          • Re: Logon to SESSION MANAGER in SAP with VBA
            Paul New
            Currently Being Moderated

            http://scn.sap.com/thread/3208646

             

            Need help. In all fairness I have asked scriptman, but haven't resolved the issue. In short, I had a program @ my old job. You opened excel, clicked on connect to SAP. Once logged on you would record your Macro. Every field you clicked in SAP it added a new line in the excel file. You could flip back and forth to watch it record.

             

            When done, you would stop the Macro. There was another selection under the logon pick list that said generate input sheet. First you would put a check next to every line entry that had text (fields you wanted to chage). When the input sheet was created, it only put headers in the rows that had an X and test in them.

             

            Once complete, you could dump thousands of part #'s in the appropriate fields, run the macro, and it made the changes. So here are my questions.

             

            Is the log into SAP drop down an add on. If so, how do you create it?

             

            Is the create input sheet part of the add in. If so, how do you make that?

             

            Whether this is an add in or not, I assume it is some kind of VB script? I only say that since there was no Macro to start, just the record after logging in.

             

            So in conclusion, I'd like to know how to create the add in, make it request a password to log into SAP, create the lines for each field visited, create the input sheet and once rows get checked with an X, the input sheet is created so you can upload or downlaod data.

             

            I will also say I very few rights @ my new job. Likely this can only be used as a download tool until I can convince them I won't blow up the system. Any help is appreciated.

          • Re: Logon to SESSION MANAGER in SAP with VBA
            Thomas Bräutigam
            Currently Being Moderated

            Anu Venkatraman wrote:

             

            Hi Scriptman,

             

            I want to restrict my code to run if anyother user is logged on in SAP while running the code.

             

            How to do this?

            Now I am running the SAP logon code successfully.

            Are you running the Scripts in VBA or VBS ?

             

            If VBA you could only look if the File that provides the Scripts is allready opened.

            afaik there is no way to look if another user is using the Script -

             

            Greetings Thomas Br

        • Re: Logon to SESSION MANAGER in SAP with VBA
          Suseno Agung
          Currently Being Moderated

          Hi Scriptman / All,

           

          After quite some times using this macro running well  in excel 2002 , now I am using Excel 2010 , but when run this code in Excel 2010 it's appear run time error.

           

          and it's refer to connection setting like below

           

           

           

          Could you please help to solve this issue ?

          Thank you so much .

           

          Kind regards,

          Agung

  • Re: Logon to SESSION MANAGER in SAP with VBA
    Felipe Guida
    Currently Being Moderated

    Hello!

     

     

    I would like to know if have any program to update automatically the info record in SAP (ME11). I have tried to use a kind of auto click "PTFB Pro" but it not worked very well. I have an excel with all prices and information, I'm looking for a solution to update this data faster than manually.

     

     

    Thanks and best regards.

    Felipe Guida.

Actions