Question Re: Open excel with SAP Credentials in Additional Q&A

Open excel with SAP Credentials

Former Member — Fri, 11 Apr 2014 20:52:16 GMT

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

Re: Open excel with SAP Credentials

Former Member — Mon, 14 Apr 2014 13:29:12 GMT

Any suggestions please.

Re: Open excel with SAP Credentials

Stefan-Schnell — Mon, 14 Apr 2014 14:36:24 GMT

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

Re: Open excel with SAP Credentials

Former Member — Mon, 14 Apr 2014 17:31:18 GMT

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.

Re: Open excel with SAP Credentials

Stefan-Schnell — Mon, 14 Apr 2014 18:48:17 GMT

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

Re: Open excel with SAP Credentials

Former Member — Mon, 14 Apr 2014 20:21:46 GMT

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

Re: Open excel with SAP Credentials

Stefan-Schnell — Tue, 15 Apr 2014 04:50:37 GMT

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

Re: Open excel with SAP Credentials

Former Member — Tue, 15 Apr 2014 10:57:13 GMT

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.

Re: Open excel with SAP Credentials

Stefan-Schnell — Tue, 15 Apr 2014 11:19:40 GMT

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

Re: Open excel with SAP Credentials

Former Member — Tue, 15 Apr 2014 13:04:27 GMT

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

Re: Open excel with SAP Credentials

Former Member — Tue, 15 Apr 2014 15:40:07 GMT

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

Re: Open excel with SAP Credentials

Stefan-Schnell — Wed, 16 Apr 2014 04:36:05 GMT

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)

setRfcConnection = rfcConnection.Logon(0, False)

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

Cheers

Stefan

Re: Open excel with SAP Credentials

Former Member — Mon, 21 Apr 2014 14:37:20 GMT

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.

Re: Open excel with SAP Credentials

Former Member — Tue, 22 Apr 2014 13:21:25 GMT

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