Based on a script by S. Schnell you can use the follwing function to find a free session
Function findGuiSession(ByVal sapSID As String, Optional tCode As String) As SAPFEWSELib.GuiSession
' this will find a free session using the systemnam resp. SID
' and optional one can also supply a transaction to
Dim CollCon As SAPFEWSELib.GuiComponentCollection
Dim CollSes As SAPFEWSELib.GuiComponentCollection
Dim guiCon As SAPFEWSELib.GuiConnection
Dim guiSes As SAPFEWSELib.GuiSession
Dim guiSesInfo As SAPFEWSELib.GuiSessionInfo
Dim i As Long, j As Long
Dim SID As String, transaction As String
'On Error GoTo EH
Dim guiApplication As SAPFEWSELib.guiApplication
Set guiApplication = getGuiApplication
If guiApplication Is Nothing Then
Exit Function
End If
Set CollCon = guiApplication.Connections
If Not IsObject(CollCon) Then
Exit Function
End If
' Loop through all existing connections
For i = 0 To CollCon.Count() - 1
Set guiCon = guiApplication.Children(CLng(i))
If Not IsObject(guiCon) Then
Exit Function
End If
Set CollSes = guiCon.Sessions
If Not IsObject(CollSes) Then
Exit Function
End If
' Now loop through all existing sessions
For j = 0 To CollSes.Count() - 1
Set guiSes = guiCon.Children(CLng(j))
If Not IsObject(guiSes) Then
Exit Function
End If
If guiSes.Busy = vbFalse Then
Set guiSesInfo = guiSes.Info
If guiSesInfo.user = "" Or guiSesInfo.user = "SAPSYS" Then
' Logon Screen - cannot be used
Else
If IsObject(guiSesInfo) Then
SID = guiSesInfo.SystemName()
transaction = guiSesInfo.transaction()
' Take the first one - In case one could also use the transactionaction addtionally
If Len(tCode) = 0 Then
If SID = sapSID Then
Set findGuiSession = guiSes
'FindSession = True
Exit Function
End If
Else
If SID = sapSID And transaction = tCode Then
Set findGuiSession = guiSes
'FindSession = True
Exit Function
End If
End If
End If
End If
End If
Next
Next
Exit Function
'EH:
End Function
Function getGuiApplication() As SAPFEWSELib.guiApplication
On Error GoTo EH
Set getGuiApplication = GetObject("SAPGUI").GetScriptingEngine
EH:
End Function
For this code to run you need to add a reference to the SAP library, described here
The following piece of code uses the above function to connect to a system with the name P11, starts the transaction MB52 and downloads the result in a Excel file
Option Explicit
Sub getMB52_data()
Dim guiSes As SAPFEWSELib.GuiSession
Set guiSes = getGuiSession("P11")
If Not guiSes Is Nothing Then
With guiSes
.StartTransaction "MB52"
.FindById("wnd[0]/usr/ctxtMATNR-LOW").Text = "<MATNR_LOW<" ' replace with a material nr
.FindById("wnd[0]/usr/ctxtMATNR-HIGH").Text = "<MATNR_HIGH<" ' replace with a material nr
.FindById("wnd[0]/usr/ctxtWERKS-LOW").Text = "<WERKS>" ' replace wiht a plant
.FindById("wnd[0]/tbar[1]/btn[8]").Press
.FindById("wnd[0]/tbar[0]/okcd").Text = "&XXL"
.FindById("wnd[0]/tbar[0]/btn[0]").Press
.FindById("wnd[1]/tbar[0]/btn[0]").Press
.FindById("wnd[1]/usr/ctxtDY_PATH").Text = "<xlPath>" ' Pathname
.FindById("wnd[1]/usr/ctxtDY_FILENAME").Text = "<xlFile>" ' filename
.FindById("wnd[1]/tbar[0]/btn[11]").Press
End With
Else
MsgBox "No free SAP Session", vbOKOnly + vbInformation, "SAP Verbindung"
End If
End Sub
Function getGuiSession(sapSID As String, Optional tCode As String) As SAPFEWSELib.GuiSession
Dim guiApp As SAPFEWSELib.guiApplication
Set guiApp = getGuiApplication
If Not guiApp Is Nothing Then
Set getGuiSession = findGuiSession(sapSID, tCode)
End If
End Function
Additonal remarks: (hopefully answering some questions in the comments)
Gui Connection: A GuiConnection represents the connection between SAP GUI and an application server. Connections can be opened from SAP Logon or from GuiApplication’s openConnection and openConnectionByConnectionString
methods
So, in other words a gui connection is a kind of login to an SAP system. And usually you have more than one SAP system in your organization. If you follow the guidelines you have a DEV, QUAL and PROD for a given system environment. Each of this system is identifid by a SID
What is SID?
SID is a unique identification code for every R/3 installation (SAP system) consisting of a database server & several application servers. SID stands for SAP System Identification. SAPSID — a three-character code such as C11, PRD, E56, etc.)
An SID is unique within the organization. Usually SAP licence only allows a user to login to a productive system only once, i.e. you cannot use the same user on different computers and you cannot even login to a SAP system with the same user on the same computer twice.
Having said that: One might be tempted to use guiApplication.Children(0) instead of looping through all connections as done in findGuiSession. And this will work as long as you can make sure that you are only logged on to one SAP system and it is the right one. But in my experience this is often not the case.
The parameter SID in findGuiSession tells the function which system to look for. As written above SID is unique and therefore identfies the system you want to use.
Using tCode in findGuiSession is optional and just forces the user to have a session in place with a given tCode already started. I use this very seldom.
OpenConnection: If you open a connection with the function OpenConnection("<SYSTEM>") you can, of course, use the returned object in order to work with it. But this only does a logon to the system if you have a kind of single sign on in your organization in place. Otherwise you have to provide logon credentials. I do not use this because I do not want to take care of this. And it also can happen that a password change is requested during the logon and I sure do not want to script this.
Example code
Rem Open a connection in synchronous mode
Set Connection = Application.OpenConnection( "U9C [PUBLIC]", True)
Set Session = Connection.Children(0)
Rem Do something: Either fill out the login screen
Rem or in case of Single-Sign-On start a transaction.
Session.SendCommand( "/nbibs")
