Logon to SESSION MANAGER in SAP with VBA

[Former Member]

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

[script_man · Accepted solution]

Hi Agung,

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

Regards,

ScriptMan

[Former Member · Accepted solution]

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

[script_man · Accepted solution]

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

[Former Member · Accepted solution]

Hi ScriptMan,

Thanks , It's works.

Best regards,

Agung

[Former Member · Accepted solution]

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

[thomas_brutigam2 · Accepted solution]

In that case there is something wrong in your connection

Tried it myself today and it worked ---

To get your SiD look in your SAPL-Logon WIndow and replace the Bold Part with yours

(Indeed you only need the SId

Set oConnection = SapGuiApp.OpenConnection("110 PRD-P01 ECC", True)

it truly works

If you have Problems with the New Design you have to put Excel (or the Application youre running VBA from in the Option / Settings DIalog.

greetings

Thomas Br

[Former Member · Accepted solution]

Hello Thomas,

thank you for your answer.

Yes, its clear about the SID and I did the replace from the beginning. Also I have added Excel to the Applications in SAP GUI Configuration. But the error is still persisting:

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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.

[thomas_brutigam2 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Alexander - I ran into that issue as well. I found the problem as my customized logon pad. It was not mapping the text in my script to the logon pad text it calls up as default. Since I want to use my customized one, I found a quick way to make it work by simply adding the entry I have in my custom pad into the default pad and it works. I am sure there are other ways of doing this but this was quick and easy.

Hope this helps.

Regards

Umur

[Former Member · Accepted solution]

Hello Umur,

thank you very much for your answer.

I found out that I have two s8l. files - "Saplgpad" and "Saplogon", so I assume that Saplgpad is the standard one and it also refers to C:\Program Files\SAP\FrontEnd\sapgui\saplgpad.exe in its settings, but I've tried to all the combinations and I still get the same error.

Could you please specify where and what should be customized, since I have only one saplogon.ini file and both s8l. files use the same ini. file?

thanks a lot!

[Former Member · Accepted solution]

Hi Alexander - I already replied to you on this separately but can't post the file it here with the screenshots. If anybody else needs it I can send it through email. Hope it helped.

Regards

Umur

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

I am having the same run-time 1000 error. I have two PCs one works perfectly and the other gets the run time error. It appears to me that the PC that is not working does not recognize the SAP Description Name or System ID.

Did you ever resolve this issue? Any help or suggestions would be appreciated.

BTW: the PC that works has Winshuttle on it. I don't know if that program makes configuration changes that might make it work.

[Former Member · Accepted solution]

Solve this by using Set Connection = SAPguiAPP.OpenConnectionByConnectionString("xxx.xxx.xxx.xxx 10") instead of Set Connection = SAPguiAPP.OpenConnection("System Connection Description", True)

[Former Member · Accepted solution]

Please send me

[Former Member · Accepted solution]

this is how I refer to the file in vbs:

Set WshShell = CreateObject("WScript.Shell")

Set proc = WshShell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe /ini_file=C:\Users\myname\AppData\Roaming\SAP\Common\COPSAPLOGON.INI")

the .ini file referred to is the customized logon file.

The command below selects the production instance from the logon pad:

Set SapGui = GetObject("SAPGUI")
Set application = SapGui.GetScriptingEngine
Set connection = application.OpenConnection("1. Prod", True)

I have "1. Prod" as the customized production link in my ini file.

Hope this helps.

Regards

Umur

[Former Member · Accepted solution]

Script Man,

You always have insightful information. I often refer to your replies and have learned so much from you and have helped automate so many tasks.

Thank you.

[script_man · Accepted solution]

Hi Jordan,

I am glad if I could help someone in the big wide world. At the end we sit anyway all in this together.

Regards,

ScriptMan

[Former Member]

hi Scriptman

this is wonderful, it worked like charm, but now the issue is i couldn't export the transactions to excel. i recorded macro in SAP and pasted the code with yours but later when it tried to export and save it in excel i couldn't do it.the excel hangs and doesnt work. please help below is the code which is not working

.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").doubleClickCurrentCell

.findById("wnd[0]/usr/ctxtRYEAR-LOW").Text = "2015"

.findById("wnd[0]/usr/ctxtRYEAR-LOW").SetFocus

.findById("wnd[0]/usr/ctxtRYEAR-LOW").caretPosition = 4

.findById("wnd[0]/tbar[1]/btn[8]").press

.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").setCurrentCell 7, "AUBEL"

.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectedRows = "7"

.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu

.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"

.findById("wnd[1]/usr/radRB_OTHERS").SetFocus

.findById("wnd[1]/usr/radRB_OTHERS").Select

.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"

.findById("wnd[1]/tbar[0]/btn[0]").press

.findById("wnd[1]/tbar[0]/btn[0]").press

.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select

.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus

.findById("wnd[1]/tbar[0]/btn[0]").press

.findById("wnd[1]/tbar[0]/btn[0]").press

.findById("wnd[1]/tbar[0]/btn[0]").press

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "asdas.xlsb"

[Former Member]

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.

[michael_hobbins]

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

[Former Member]

Hi Michael,

Thanks for your reply ...but it's still difficult for me mix them around the code as several tried with NO luck yet.

Do you have any complete script to run ?

Thanks,

Agung

[michael_hobbins]

No I'dont have as I don't use SAP scripts, though I do work with VBAs.

Michael