Opening SAP system using excel macro

[Former Member]

Hi,

Kindly guide me how to open and access a SAP sytem using SAP logon pad using a macro in the excel.

I have created a macro in excel for rest of the process by recording it.

First time we log on to the SAP System and after work is completed we log out, but then need to be re-log in to the system for another work sequence using the macro in excel.

Kindly provide me the code

Regards,

Anup

[script_man · Accepted solution]

Hi Anup,

I am using in this case, the following code:

Dim sap As Object
Dim conn As Object
 
Set sap = CreateObject("SAP.Functions")
Set conn = sap.Connection
conn.System = "SYSTEM"
conn.client = "123"
conn.user = "USER"
conn.Password = "PW"
conn.Language = "DE"
 
If conn.logon(0, False) <> True Then
    MsgBox "Logon to the SAP system is not possible", vbOKOnly, "Comment"
Else
     . . . 
End if

Please do not forget: A link in a VBA Project with e.g. SAP: Remote Function Call: COM support 1.0 Type Library

Regards,

ScriptMan

[Former Member · Accepted solution]

Dear Scriptman,

Can you please tell me how to add SAP: Remote Function Call: COM support 1.0 Type Library in VBA projects?

Sachin

[script_man · Accepted solution]

Hi Sachin,

I thought that my VBA projects need a reference to SAP Remote Function Call explicitly. This is not the case with me.

But there are sources that say that you have to make a reference from the VBA project using Tools and references.

A library called librf32.dll must exist for example in c:\windows\system32.

Regards,

ScriptMan

[Former Member · Accepted solution]

Hello,

I need to right a Macro that utilizes a remote function call to SAP R/3. I have downloaded code samples but I donu2019t think that the object comes with the standard VBA library. I could not find the object in the VBA editor object browser where another user suggested it would be. .

Where can I install the Excel VBA SAP GUI? It looks like there is a Windows dll that you need to have installed; will this be compatible with Windows 7?

Thank you,

[script_man · Accepted solution]

Hi duadinam,

In a reply earlier, I had mistakenly spoken of librf32.dll. This library is called correctly as follows: librfc32.dll

It is located on the install CD for SAP GUI or it is to find e.g. in c:\wndows\system32\.

In Excel, you can link this library manually as SAP: Remote Function Call: support COM 1.0 Type Library

Regards,

ScriptMan

[Former Member · Accepted solution]

hi,

i have to create a mcro to delete purchase ordders from SAP.we get a file of the list of PUrchase order and the corresponding line numbers.so using me22n we open the purchase order and then click on the line number press delete and save it..is there any way to make macros since it eats up my 8 hrs of working.

[former_member583234 · Accepted solution]

I'm Trying to make a scripting for mass upload so what I did means I gone to sap record play back and I recoreded the steps and i tried play option it is running as the same material , same plant and same plant specific status here i need to connect my Excel but i have no idea i searched in internet also but till now i unable to find the solution...

Recorded scripts is given below,

If Not IsObject(application) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

Set connection = application.Children(0)

End If

If Not IsObject(session) Then

Set session = connection.Children(0)

End If

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject application, "on"

End If

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").text = "/nzpb2"

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

session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").text = "910402635318"

session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 12

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

session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW").getAbsoluteRow(5).selected = true

session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW/txtMSICHTAUSW-DYTXT[0,5]").setFocus

session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW/txtMSICHTAUSW-DYTXT[0,5]").caretPosition = 0

session.findById("wnd[1]").sendVKey 0

session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").text = "SG03"

session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4

session.findById("wnd[1]").sendVKey 0

session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTA").text = "ZG"

session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTD").text = "18.06.2019"

session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTA").setFocus

session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTA").caretPosition = 2

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

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

session.findById("wnd[1]/usr/btnSPOP-OPTION1").press

[former_member583234 · Accepted solution]

Please help on this

[zollerma]

Helo,

thank you very much

[former_member537379]

hi:

  Every body, i met an emergent proplem and want to get any help, did anyone can give me some suggestion how i can get all macros already existed in a certain excel tempplate when using sap doi technology, i have already tried class:

[Former Member]

Hi,

i have to create a macro that could delete the Purchase orders from SAp.we get the file in excel where we have the purchase order number and under that teh item number.so we got to SAP using me22n then put the purchase order and then select the item number press delete and then save it..could you pls help me in making macros..its really urgent

[Former Member]

Hi guys.. Hope you can help me out.

I am want the salesorder details to be displayed in the excel using the BAPI activex control in MS Access.

I connected to SAP and got some code from internet to get the orderdetails. The problem is I need the

item quantity and purchase order number also. Here's some code in vba which gives idea what I want to display.

**************************************************************code*********************************************************************

Set oSalesOrder = oBAPICtrl.GetSAPObject("SalesOrder", sapSoNumber)

Indexv = 4

For Each oItem In oSalesOrder.items

wsTemplate.Range(cols(3) & Indexv).Value = oItem.material.material

'wsTemplate.Range(cols(4) & Indexv).Value = oItem.orderedqty 'I need the item quantity here..?

wsTemplate.Range(cols(1) & Indexv).Value = sapSoNumber

'wsTemplate.Range(cols(2) & Indexv).Value = oSalesOrder.ponumber ' I need the purchase order number here??

Indexv = (Indexv + 1)

Next

**********************************************************************************************************************************************

if this method doe snot work then can you give me the code using any different methods like getdetail with full code in vba.?

[Former Member]

Hi ScriptMan - just curious how to add such a link:

"Please do not forget: A link in a VBA Project with e.g. SAP: Remote Function Call: COM support 1.0 Type Library"

Thanks

Umur

[script_man]

Hi Umur,

I should have tested it in more detail. You are right, the link is redundant at this point.

Regards,

ScriptMan

[Former Member]

Thanks....

My query is now solved....

[Former Member]

Hi Thanks,

But the solution did not work.

To Add further information....

1. We provide access to users in the organisation.

2. At times there are a lot of changes to be made for a bunch of users.

3. For this we need to login to each of the users and change some settings and then log out.

4. After we log out, we need to re-login using logon pad (non-single sign on)

5. These have to be performed a number of times, hence cannot provide a user name and a password as provided in the coding provided.

6. Kindly provide us a way in which we can open the log on pad and open a particular system.

Thanks,

Anup Nair

[script_man]

Hi Anup,

You can also try the following.

For example:

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 = "CLIENT"
Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "USER"
Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "PASSWORD"
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

'Here follows the recorded VBScript without the first 14 lines.
. . .

Please note that after finish of script the SAP session is also terminated.

Regards,

ScriptMan

Edited by: ScriptMan on Jun 4, 2010 11:05 AM

[former_member537160]

in above script

after execution the excel application is closed automatic.

here is it possible that it will come back to the file with out closing excel from where the macro has been executed?

[script_man]

Hi samir,

suppose the script above is called script.vbs. If I run the below code in a VBA macro in Excel, the focus returns at the end of the SAP session back to Excel.

. . .
Set wshell = CreateObject("Wscript.Shell")
wshell.Run Chr(34) & PATH & "\script.vbs" & Chr(34), 1, 1
. . .

Regards,

ScriptMan

[former_member537160]

hi script man

the problem is i don't want to make any vbs file

so i have written this script inside the excel file's micro.

it is working, only things is after executing micro it close the excel application. it should not close.

even you can try the above micro named SAPMICRO

Sub sapmicro()

If Not IsObject(SAPguiApp) Then

Set SAPguiApp = CreateObject("Sapgui.ScriptingCtrl.1")

End If

If Not IsObject(Connection) Then

Set Connection = SAPguiApp.OpenConnection("dev", True)

End If

If Not IsObject(session) Then

Set session = Connection.Children(0)

End If

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject Application, "on"

End If

session.findById("wnd[0]").maximize

session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "009"

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

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

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

session.findById("wnd[0]/usr/pwdRSYST-BCODE").SetFocus

session.findById("wnd[0]/usr/pwdRSYST-BCODE").caretPosition = 8

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

If session.Children.Count > 1 Then

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

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

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

End If

session.findById("wnd[0]/tbar[0]/okcd").Text = "xd03"

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

session.findById("wnd[1]/usr/ctxtRF02D-KUNNR").Text = Excel.Cells(2, 2).Value

session.findById("wnd[1]").sendVKey 0

MsgBox "finished", vbOKOnly, "Comment"

End Sub

[script_man]

Hi samir,

how run you sapmicro ? In my case remains open Excel after running sapmicro.

Regards,

ScriptMan

[former_member537160]

i open the excel file where the micro sapmacro() is saved

i go to

tools-> macro-> play macro

select the macro sapmacro then click on RUN button.

if it run successful with out error in SAP then it close all excel application.

but if there is some error in SAP step then it will not close all excel.

i think in you macro there will be some error in sap that is way it want close.

try to give some simple transaction in sap then may you notice the same.

[script_man]

Hi samir,

Yes, you are right. But after I have completed the following three commands, this phenomenon was even over.

. . .
set session = Nothing
set connection = Nothing
set SapGuiApp = Nothing

Regards,

ScriptMan

[former_member537160]

thanks script man

finally i got this thanks for the same.

can you suggest me any good books or editors available for scripting and especial sapgui script.

[Former Member]

Hi sir,

Am unable to run the macro's using the above script has u given

Am Facing the issue i.e: compile error:Invalid outside procedure

Please help

Thank You

Karthick.T

[thomas_brutigam2]

normaly that means you have an Error in your VBA-Code ...

so plz show ur code ... we dont have any longdistance tools to look on your harddrive

[Former Member]

Hi,

this is my vba code

Dim sap As Object

Dim conn As Object

Set sap = CreateObject("SAP.Functions")

Set conn = sap.Connection

conn.System = "SYSTEM"

conn.client = "500"

conn.user = "ESCHN-OMT"

conn.Password = "SAP456"

conn.Language = "EN"

If conn.logon(0, False) True Then

MsgBox "Logon to the SAP system is not possible", vbOKOnly, "Comment"

Else

. . .

End If

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 = "CLIENT"

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

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

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

. . .

Set wshell = CreateObject("Wscript.Shell")

wshell.Run Chr(34) & Path & "\script.vbs" & Chr(34), 1, 1

Sub sapmicro()

If Not IsObject(SapGuiApp) Then

Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")

End If

If Not IsObject(Connection) Then

Set Connection = SapGuiApp.OpenConnection("dev", True)

End If

If Not IsObject(session) Then

Set session = Connection.Children(0)

End If

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject Application, "on"

End If

session.findById("wnd[0]").maximize

session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "ESCHN-OMT"

session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "ESCHN-OMT"

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

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

session.findById("wnd[0]/usr/pwdRSYST-BCODE").SetFocus

session.findById("wnd[0]/usr/pwdRSYST-BCODE").caretPosition = 6

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

If session.Children.Count > 1 Then

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

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

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

End If

session.findById("wnd[0]/tbar[0]/okcd").Text = "xd03"

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

session.findById("wnd[1]/usr/ctxtRF02D-KUNNR").Text = Excel.Cells(2, 2).Value

session.findById("wnd[1]").sendVKey 0

MsgBox "finished", vbOKOnly, "Comment"

End Sub

please check & resolve the issue

Regards,

Karthick.T

[thomas_brutigam2]

First of all,

please use the markups (next time) easy to read

The error responds to your first Codelines:

conn.System = "SYSTEM" 
conn.client = "500" 
conn.user = "ESCHN-OMT" 
conn.Password = "SAP456" 
conn.Language = "EN" 

You cant assign Values to a Variable in VB outside a Procedure or Function

Easy WOrkarround:

Sub T_login()
Dim sap As Object 
Dim conn As Object 
Set sap = CreateObject("SAP.Functions") 
Set conn = sap.Connection 
conn.System = "SYSTEM" 
conn.client = "500" 
conn.user = "ESCHN-OMT" 
conn.Password = "SAP456" 
conn.Language = "EN" 
If conn.logon(0, False) True Then 
MsgBox "Logon to the SAP system is not possible", vbOKOnly, "Comment" Else
End If 
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 = "CLIENT" 
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "USER" 
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "PASSWORD" 
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 . . . 

Set wshell = CreateObject("Wscript.Shell") 
wshell.Run Chr(34) & Path & "\script.vbs" & Chr(34), 1, 1 
End Sub

Edited by: Thomas Br on Aug 20, 2010 7:35 AM

[Former Member]

Hi sir,

Am facing the same issue as discussed above, really getting struggle on this,

after finishing the command, am going to analysis key & running the macro, but getting same compile error

i don't have more skill in VB, please help on this separately

Thanks & Regards,

Karthick.T

[Former Member]

Hi Thomas,

Iam Getting Error that : RunTime Error:424 Activex component can't create object, when i Run this Code....

Dim sap As Object

Dim conn As Object

Sub T_login()

Set sap = CreateObject("SAP.Functions")

Set conn = sap.Connection

conn.System = "SYSTEM"

conn.client = "900"

conn.user = "G2"

conn.Password = "12345"

conn.Language = "EN"

If conn.logon(0, False) Then

MsgBox "Logon to the SAP system is not possible", vbOKOnly, "Comment"

Else

End If

If Not IsObject(SapGuiApp) Then

Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.2")

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 = "CLIENT"

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

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

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

Set wshell = CreateObject("Wscript.Shell")

wshell.Run Chr(34) & Path & "\script.vbs" & Chr(34), 1, 1

End Sub

[Former Member]

Hi Thomas,

Iam Getting Error that : RunTime Error:424 Activex component can't create object, when i Run this Code....

Dim sap As Object

Dim conn As Object

Sub T_login()

Set sap = CreateObject("SAP.Functions")

Set conn = sap.Connection

conn.System = "SYSTEM"

conn.client = "900"

conn.user = "G2"

conn.Password = "12345"

conn.Language = "EN"

If conn.logon(0, False) Then

MsgBox "Logon to the SAP system is not possible", vbOKOnly, "Comment"

Else

End If

If Not IsObject(SapGuiApp) Then

Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.2")

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 = "CLIENT"

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

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

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

Set wshell = CreateObject("Wscript.Shell")

wshell.Run Chr(34) & Path & "\script.vbs" & Chr(34), 1, 1

End Sub

Can you able to help me,to slove this problem, Using Macro i have to Logon SAP.............

Regards

Karthick

[thomas_brutigam2]

Hi Vasant,

First of all it would be better to open up a new Thread ...

If you only "Doubleclick" the VBS you get a Msgbox of the WScript

There should appear a Dialogbox where the Line of the Error is told.

For eg: Error at Line 2, Col 5

Would be helpfull to have that Error-Description

[script_man]

HIi Vasanth,

I'm with Thomas agree. But before you do it, take a look at this command:

Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.2")

I think it's right as follows:

Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")

Regards,

ScriptMan

[Former Member]

Hi,

Thank you ScriptMan and Thomas, Now its Working fine,,,, Thank u alot....

Regards

Vasanth

[Former Member]

Hi Script,

am facing the below error while am running the macros

i.e error -

RFC_ERROR_PROGRAM

Message

Ent

Please help us to solve this

Regards

Karthick.T