Open excel with SAP Credentials

[Former Member]

I am working on an excel application that will enable changes in SAP PI/XI. When the user opens the excel file, the file will prompt for a SAP UserID and Password. The user will typein their SAP User ID and Password and these credentials should be validated against sap (maybe using SAP Logon Control???). If ok the excel file will open else it will says invalid user id pass.

Please let me know if this is doable.

Thanks

[Former Member · Accepted solution]

Any suggestions please.

[stefan_schnell · Accepted solution]

Hello Vicky,

try this:

Public rfcLogon As SAPLogonCtrl.SAPLogonControl
Public rfcConnection As SAPLogonCtrl.Connection

Function CheckUser() As Boolean

  Dim res As Boolean

  res = False

  Set rfcLogon = CreateObject("SAP.Logoncontrol.1")
  Set rfcConnection = rfcLogon.NewConnection

  rfcConnection.ApplicationServer = "NSP"
  rfcConnection.System = "NSP"
  rfcConnection.SystemNumber = 1
  rfcConnection.User = InputBox("Benutzer:")
  rfcConnection.Password = InputBox("Passwort:")
  rfcConnection.Client = "001"
  rfcConnection.Language = "EN"

  setRfcConnection = rfcConnection.Logon(0, True)
  If setRfcConnection Then
    res = True
    rfcConnection.Logoff
  End If

  Set rfcLogon = Nothing
  Set rfcConnection = Nothing

  CheckUser = res

End Function

Sub Test()
  If CheckUser Then
    MsgBox "Okay"
  End If
End Sub

Let us know the result.

Cheers

Stefan

[Former Member · Accepted solution]

I copy pasted the code in the excel vba editor...saved and closed it.

Upon reopening it did not prompt for any user id and password.

[stefan_schnell · Accepted solution]

Helllo Vicky,

start the sub Test with the debugger of VBA. But before add the reference to wdtlog.ocx in your VBA project.

Cheers

Stefan

[Former Member · Accepted solution]

I do see  wdtlogU.ocx but not wdtlog.ocx in my list of SAP gui files

[stefan_schnell · Accepted solution]

Hello Vicky,

wdtlog.ocx is on Windows 64 bit in the directory Program Files (x86)\Common Files\SAP Shared and on Windows 32 bit in the directory Program Files\Common Files\SAP Shared.

Cheers

Stefan

[Former Member · Accepted solution]

Thanks I got it.

Now though the program is working fine in debug mode, how do we ensure that this function is called when the user opens this excel workbook each time. If the user passes this SAP logon test only then the file should open else it should give a promt invalid sap credentials and close.

Thanks for all your help.

[stefan_schnell · Accepted solution]

Hello Vicky,

try this:

Sub Test()
  If CheckUser Then
    MsgBox "Ok"
  Else
    MsgBox "Nok"
    Excel.ActiveWorkbook.Close
  End If
End Sub

Private Sub Workbook_Open()
  Test
End Sub

The event Workbook_Open calls the sub Test, at the moment if Excel opens the workbook, and if CheckUser is not okay, it closes the active Excel workbook.

Cheers

Stefan

[Former Member · Accepted solution]

No. Still the excel does not prompt me for a user ID password when I open the excel workbook.

Public rfcLogon As SAPLogonCtrl.SAPLogonControl
Public rfcConnection As SAPLogonCtrl.Connection

Function CheckUser() As Boolean

  Dim res As Boolean

  res = False

  Set rfcLogon = CreateObject("SAP.Logoncontrol.1")
  Set rfcConnection = rfcLogon.NewConnection

  rfcConnection.ApplicationServer = "aaaa"
  rfcConnection.System = "aaa"
  rfcConnection.SystemNumber = "00"
  rfcConnection.User = InputBox("SAP USER:")
  rfcConnection.Password = InputBox("SAP PASSWORD:")
  rfcConnection.Client = "000"
  rfcConnection.Language = "EN"

  setRfcConnection = rfcConnection.Logon(0, True)
  If setRfcConnection Then
    res = True
    rfcConnection.Logoff
  End If

  Set rfcLogon = Nothing
  Set rfcConnection = Nothing

  CheckUser = res

End Function

Sub Test()
  If CheckUser Then
    MsgBox "Ok"
  Else
    MsgBox "Nok"
    Excel.ActiveWorkbook.Close
  End If
End Sub

Private Sub Workbook_Open()
  Test
End Sub

[Former Member · Accepted solution]

Its' working now. I had made a small error on my end.

one question though...once the user is validated does it logoff the user immediately or only when the excel is closed.

also is there a way that the password when typed in the input box shows as all "*" instead of plain text.

Thanks

[stefan_schnell · Accepted solution]

Hello Vicky,

the logoff is immediately inside the function CheckUser. If you need the connection for a longer time, put the Logoff method in an own sub routine. The rfcConnection variable is declared global.

Here is a solution how to mask your password input via Windows API with VBA. Also you can use your own user form or change the line

setRfcConnection = rfcConnection.Logon(0, True)

to

setRfcConnection = rfcConnection.Logon(0, False)

to disable the silent mode and use the own SAP form.

Cheers

Stefan

[Former Member · Accepted solution]

Function CheckUser() As Boolean

  Dim res As Boolean

  res = False
  Set rfcLogon = CreateObject("SAP.Logoncontrol.1")
  Set rfcConnection = rfcLogon.NewConnection

  rfcConnection.ApplicationServer = "abc"
  rfcConnection.System = "abc"
  rfcConnection.SystemNumber = "00"
  rfcConnection.Client = "000"
  rfcConnection.Language = "EN"
  setRfcConnection = rfcConnection.Logon(0, False)
  sPassword = rfcConnection.Password
  MsgBox ("Found " & sPassword & " Pass")
 
  If setRfcConnection Then
    res = True
    rfcConnection.Logoff
  End If

  Set rfcLogon = Nothing
  Set rfcConnection = Nothing

  CheckUser = res

End Function

I am trying to read the value of rfcConnection.Password once it is typed in the SAP user form. However it comes back as garbled value instead of string value.

How can I read the value as string, cause I need to pass this value to some other function in the vba code. pls advice.

[Former Member · Accepted solution]

I am closing this thread and starting a new thread. http://scn.sap.com/thread/3541763