Open and LOGON on SAP with Excel Macro

[Former Member]

Hi,

i am trying to start SAP from excel VBA and input my information, like Log in, user name, password, etc.

To sum up, SAP is not open. I want to open it by clicking on the macro it will also input my user name and password into SAP.

Here is my coding.

When I click on the macro, its give me the following. (see image).

Any help?

Sub macro2()



Dim sapConn As Object

Set objshell = CreateObject("WScript.Shell")
Set objapp = objshell.Exec("C:\Program Files\SAP\FrontEnd\SAPgui\saplogon.exe")
Set sapConn = CreateObject("SAP.Functions") 'Create ActiveX object

  sapConn.connection.ApplicationServer = "C:\Program Files\SAP\FrontEnd\SAPgui\saplogon.exe"
  sapConn.connection.client = "100" ' only read access
  sapConn.connection.user = Environ("USER")
  sapConn.connection.Password = "PASSWORD"
  sapConn.connection.System = ".VCM Global Production"
  sapConn.connection.Language = "EN"
  If sapConn.connection.logon(0, False) <> True Then 'Try Logon
  MsgBox "Cannot Log on to SAP"
  getSAPProjectHours = False
  Else
   
  sapConn.connection.Logoff
  End If
  Set sapConn = Nothing

End Sub

[former_member799352]

Hello,

I got success to log in SAP using Macro.

I need to set one if condition if SAP is already log on SAP do not give any error or start function directly.

here whenever i run this macro sometime SAP is log on so it's give me error "91 object variable or block variable not set" I need the macro that if SAP is already log on than my recorded GUI script Directly RUN.

Here is VBA Code for reference:-

Below BOLD part may be having some coding error.

Option Explicit

#If VBA7 Then

Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

#Else

Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

#End If

Const Titre As String = "SAP Logon 760" 'Changer au besoin

Public SapGuiAuto As Object

Public AppliSAP As Object

Public Connection As Object

Public Session As Object

Public TmpSession As Object

Dim worksheet_name, workbook_name As String

Public colLabelEvent As Collection 'Collection of labels for event handling

Public colLabels As Collection 'Collection of the date labels

Public bSecondDate As Boolean 'True if finding second date

Public sActiveDay As String 'Last day selected

Public lDays As Long 'Number of days in month

Public lFirstDay As Long 'Day selected, e.g. 19th

Public lStartPos As Long

Public lSelMonth As Long 'The selected month

Public lSelYear As Long 'The selected year

Public lSelMonth1 As Long 'Used to check if same date is selected twice

Public lSelYear1 As Long 'Used to check if same date is selected twice

Public datFirstDay As Date

Public datLastDay As Date

Public serialstart As String

Public serialend As String

Public start_date As String

Public end_date As String

Public option_selection As String

Public Breakdown_Report_selection As Integer

Public Daily_BD_report As Integer

Public Top10_report As Integer

Function logonSAP() As Boolean

Dim user_id, pass, FileDateTime As String

Dim serialstart, serialend As String

Dim KillFile As String

#If VBA7 Then

Dim lhWnd As LongPtr

#Else

Dim lhWnd As Long

#End If

Dim Temps As Double

On Error GoTo Erreur

'Check if the Logon window is open - otherwise open it

Temps = Timer

lhWnd = FindWindow(vbNullString, Titre)

If lhWnd = 0 Then

Call Shell("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbMinimizedFocus)

Do

lhWnd = FindWindow(vbNullString, Titre)

If Timer - Temps > 10 Then '10 seconds delay to open the window otherwise we leave

MsgBox "Can not open SAP Logon window", vbExclamation, ""

Exit Function

End If

Loop Until lhWnd > 0

End If

If AppliSAP Is Nothing Then

Set SapGuiAuto = GetObject("SAPGUI")

Set AppliSAP = SapGuiAuto.GetScriptingEngine

End If

'Opening a PRD - SSO session

'If SAP is already open, we are looking for a free session

If AppliSAP.Connections.Count > 0 Then

For Each Connection In AppliSAP.Children

If Not Connection.DisabledByServer And Left(Connection.Description, 3) = "PRD" Then

For Each TmpSession In Connection.Children

If TmpSession.Busy = False Then

Set Session = TmpSession

logonSAP = True

Exit Function

End If

Next

End If

Next

Else

'If SAP is closed

Set Connection = AppliSAP.OpenConnection("AiKYA 2.0 Production") 'Change as needed

'Set Connection = AppliSAP.openconnection(Range("G13"))

Temps = Timer

Do

Set Session = Connection.Sessions(0)

If Timer - Temps > 10 Then

MsgBox "Can not create a session", vbExclamation, ""

Exit Function

End If

Loop While Session Is Nothing

End If

Session.findById("wnd[0]").iconify

If Dir("D:\humocell.html") <> "" Then

Kill "D:\humocell.html"

End If

Worksheets("Working File").Select

Range("G6").ClearContents

user_id = Range("G29")

pass = Range("G30")

' FileDateTime = ("Mod_Production_Report_Cell" & Format(Now(), "DD-MM-YYYY HH MM AMPM") & ".html")

FileDateTime = ("humocell.html")

Range("G6").Value = FileDateTime

Session.findById("wnd[0]").iconify

Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = user_id

Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = pass

Session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

Session.findById("wnd[0]").sendVKey 0

Session.findById("wnd[0]").sendVKey 0

If Session Is Nothing Then

MsgBox "Can not find a free session", vbExclamation, "?!?"

Set TmpSession = Nothing

Set Connection = Nothing

Set AppliSAP = Nothing

Set SapGuiAuto = Nothing

Exit Function

Else

logonSAP = True

End If

'=========================================================================================================================================================

'Session.findById("wnd[0]").iconify

Worksheets("R").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Session.findById("wnd[0]/tbar[0]/okcd").Text = "humo"

Session.findById("wnd[0]").sendVKey 0

Session.findById("wnd[0]/usr/tabsTABSTRIP_ORDER_CRITERIA/tabpTEXT-230/ssub%_SUBSCREEN_ORDER_CRITERIA:RHU_HELP:2010/btn%_SELEXIDV_%_APP_%-VALU_PUSH").press

Session.findById("wnd[1]").sendVKey 24

Session.findById("wnd[1]").sendVKey 8

Session.findById("wnd[0]/usr/tabsTABSTRIP_ORDER_CRITERIA/tabpTEXT-230/ssub%_SUBSCREEN_ORDER_CRITERIA:RHU_HELP:2010/radLGRID").Select

Session.findById("wnd[0]/usr/tabsTABSTRIP_ORDER_CRITERIA/tabpTEXT-230/ssub%_SUBSCREEN_ORDER_CRITERIA:RHU_HELP:2010/radLGRID").SetFocus

Session.findById("wnd[0]").sendVKey 8

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").pressToolbarContextButton "&MB_VARIANT"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectContextMenuItem "&LOAD"

Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").currentCellColumn = "TEXT"

Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").firstVisibleRow = 0

Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").selectedRows = "0"

Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").clickCurrentCell

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").setCurrentCell 5, "CMLQTY_A"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectContextMenuItem "&HTML"

Session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 4

Session.findById("wnd[1]").sendVKey 12

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectContextMenuItem "&HTML"

Session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "humocell.htm"

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

Session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0

Session.findById("wnd[1]").sendVKey 4

Session.findById("wnd[2]/usr/ctxtDY_PATH").Text = "D:\"

Session.findById("wnd[2]/usr/ctxtDY_PATH").SetFocus

Session.findById("wnd[2]/usr/ctxtDY_PATH").caretPosition = 3

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

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

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").setCurrentCell -1, "EXIDV"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").firstVisibleRow = 37

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectColumn "EXIDV"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectColumn "CML_PACKGOOD"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectColumn "CMLQTY_A"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectColumn "LGORT"

Session.findById("wnd[0]/usr/cntlCONTAINER_2000/shellcont/shell").selectColumn "CML_CHARG"

Session.findById("wnd[0]").sendVKey 12

Session.findById("wnd[0]").sendVKey 12

Set TmpSession = Nothing

Set Connection = Nothing

Set AppliSAP = Nothing

Set SapGuiAuto = Nothing

Erreur:

If Err.Number <> 0 Then

MsgBox Err.Number & vbCrLf & Err.Description

End

End If

'Stop 'for debugging - F8 goes to the next line

'Resume 'F8 goes to the faulty line

Worksheets("Working File").Select

Range("O6").ClearContents

Range("O6").Value = Format(Now())

End Function

[holger_khn]

Hello.

Sub macro2()
Dim sapConn As Object
Set objshell = CreateObject("WScript.Shell")
Set objapp = objshell.Exec(Environ("PROGRAMFILES") & "\SAP\FrontEnd\SAPgui\saplogon.exe")
Set sapConn = CreateObject("SAP.Functions") 'Create ActiveX object
sapConn.Connection.ApplicationServer = Environ("PROGRAMFILES") & "\SAP\FrontEnd\SAPgui\saplogon.exe"
sapConn.Connection.client = "100" ' only read access
sapConn.Connection.user = Environ("USERNAME")
sapConn.Connection.Password = "PASSWORD"
sapConn.Connection.System = ".VCM Global Production"
sapConn.Connection.Language = "EN"
If sapConn.Connection.logon(0, False) <> True Then 'Try Logon
MsgBox "Cannot Log on to SAP"
getSAPProjectHours = False
Else: sapConn.Connection.Logoff
End If
Set sapConn = Nothing
End Sub

I have just changed mainly 'ENVIRON("USER")' as this not exist.

Best regards,

Holger

[Former Member]

Cant log on..

I still get the same 2 windoes, one asking for my log on and the other one is SAP. (See attached image)

[Former Member]

When I click on the left table, which contain my user name and password..it also said

No log in SAPSMG.ini

here the content of SAPSMG.INI

#

# ADP - HR Connect

#

EQ1=eumsg02

EP1=eumsg03

[Former Member]

any clue? O_O....cause I am lost :S