on 08-22-2014 2:56 AM
Hi All,
My requirement is to update some entries in SAP from Excel. This is my first time to do this requirement and to start with, how do i open connection to SAP from my excel with macro. I searched in the forum and saw some same requirement and copied their codes but it doesnt seem to work for me. THe code i copied is pasted below. I'm not sure if it has something to do with my excel version or gui version. Currently i am running in GUI 730 Final Release Patch Level 7. Excel is Office 2013 64 bit. Windows is also 64 Bit.
'Declaration
'
Dim Destination_System As Integer
Dim objBAPIControl As Object 'Function Control (Collective object)
Dim r3onnection As Object 'sapConnection As Object 'Connection object
'Set objBAPIControl = CreateObject("SAP.Functions")
'set connection
Set logoncontrol = CreateObject("SAP.LogonControl.1")
Set r3connection = objBAPIControl.Connection 'sapConnection = objBAPIControl.Connection
'Set objBAPIControl = CreateObject("SAP.Functions")
'Set r3connection = logoncontrol.NewConnection
r3connection.Client = "500"
r3connection.ApplicationServer = "XXX.XXX.X.XX"
r3connection.Language = "EN"
r3connection.User = "XX-XXXX"
r3connection.Password = "xxxxxxx"
r3connection.System = "S01"
r3connection.SystemNumber = "11"
r3connection.UseSAPLogonIni = False
silentlogon = False
retcd = r3connection.Logon(0, silentlogon)
If retcd <> True Then MsgBox "Logon Failed": Exit Sub
objBAPIControl.Connection = r3connection
**************************************************************
Thanks!
Hello.
First of all you need to be aware that open a session via VBA will be different than via SAPGUI. Some fields ond options are not available in this case.
So if you want full view and options you should adopt Excel VBA to an existing already open SAP session.
Or automatically open an SAP session via logon if no SAP Session of required System is open.
Sessions created by "SAP.Logoncontrol.1" are restricted in view and functions.
May you let us know why it´s important to create a session via script and not use existing sessions. Then we may would be able to find a solution for your requirements.
Best regards,
Holger
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Holger,
My requirement is to update a certain table in SAP from excel. So i need to connect the excel with macro with SAP then process the data in excel, pool it and update a customer table in SAP. It is my first time to work with excel interfaced to SAP so i do not really have a clear idea on how to do so. Based on what i read from the forum, i should first create a connection from my excel hence the above code. Is it the correct way though? Thanks for your help!
Hi Holgen,
I will just use a BAPI to update SAP.
My code below is able to open my SAP Gui and log a user in. Is it possible to do this in background? coz this code shows me the actual SAP screen.
On Error Resume Next
If Not IsObject(SAP_applic) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set SAP_applic = SapGuiAuto.GetScriptingEngine
End If
Set Connection = SAP_applic.Children(0)
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
'MsgBox session.info.sessionnumber
er = Err.Number
On Error GoTo 0
If er <> 0 Then
er = 0
On Error Resume Next
On Error GoTo 0
waitTill = Now() + TimeValue("00:00:01")
While Now() < waitTill
DoEvents
Wend
er = 0
On Error Resume Next
If Not IsObject(SAP_applic) Then
Set SapGuiAuto = GetObject("SAPGUI") 'Setting
Set SAP_applic = SapGuiAuto.GetScriptingEngine
Set Connection = SAP_applic.Children(0)
End If
er = Err.Number
On Error GoTo 0
If er <> 0 Then
tmp = MsgBox("SAP Logon not installed on your system" & vbCrLf & _
"", vbInformation)
End
End If
End If
If Not IsObject(Connection) Then
Set Connection = SAP_applic.openconnection("XXX")
Else
Set Connection = SAP_applic.openconnection(Connection.Description)
End If
Set session = Connection.Children(0)
session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "nnn"
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "EA-JOAN"
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "zzz"
session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
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
Hello Mary.
If you do not use SSO (Single Sign On) in your organization I recommend not use any Login functions automatically in VBA coding where Passwords are visible. Even protection of an VBA Project can easily bypassed by using an HEXEDITOR. This is very unsafe and mostly in all companies forbidden by IT Safety Policies.
If SSO is available in your organization I have an code snippet which can be used.
Instead of this I suggest to use below opportunity. In my example an function module is called which get the fieldinfo for an specific structure/table. In my example it´s structure DFIES.
You just Need to have an Excel workbook created with a sheet named TEST => then code should work fine.
A logon Screen appear where you can Login. The functionmodule is called via RFC Connection in Background.
Public Sub RFC_FIELDINFO()
Dim Func As Object
Dim sapConn As Object
Dim tblFIELDTAB
Dim tblFIXED_VALUES
Dim intRow%
Dim intCol%
'**************************************************************************
'* Sub : Call FM /ZOPTION/LIVE_DDIF_FIELDINFO *
'* Author : Holger Köhn *
'* Created : 23.08.2014 *
'* Changed : *
'**************************************************************************
ThisWorkbook.Sheets("TEST").Activate
Cells.Select
Selection.ClearContents
ThisWorkbook.Sheets("TEST").Range("A1").Select
'**************************************************************************
'* create RFC-Connection *
'**************************************************************************
'SAPLogonNachHinten
Set sapConn = CreateObject("SAP.Functions")
sapConn.Connection.RfcWithDialog = True
If sapConn.Connection.LogOn(1, False) <> True Then
MsgBox "Cannot Logon to SAP"
Exit Sub
End If
'ExcelWBNachVorn
'SAPLogonNachHinten
DoEvents
'**************************************************************************
'* run FM /ZOPTION/LIVE_DDIF_FIELDINFO *
'**************************************************************************
Set Func = sapConn.Add("/ZOPTION/LIVE_DDIF_FIELDINFO")
Func.Exports("TABNAME") = "DFIES"
Set tblFIELDTAB = Func.Tables("FIELDTAB")
If Func.Call = False Then
MsgBox Func.Exception
Exit Sub
Else
Application.ScreenUpdating = False
For intCol = 1 To tblFIELDTAB.ColumnCount
ThisWorkbook.Sheets("TEST").Cells(1, intCol).Value = tblFIELDTAB.columnname(intCol)
Next
If tblFIELDTAB.RowCount > 0 Then
For intRow = 1 To tblFIELDTAB.RowCount
For intCol = 1 To tblFIELDTAB.ColumnCount
ThisWorkbook.Sheets("TEST").Cells((intRow + 1), intCol).Value = tblFIELDTAB(intRow, intCol)
Next
Next
ThisWorkbook.Sheets("TEST").Activate
End If
Columns.AutoFit
ThisWorkbook.Sheets("TEST").Range("A1").Select
Application.ScreenUpdating = True
End If
'**************************************************************************
'* clear tblFIELDTAB *
'**************************************************************************
Do Until tblFIELDTAB.RowCount = 0
Call tblFIELDTAB.Rows.Remove(1)
Loop
Set sapConn = Nothing
Set Func = Nothing
Set tblFIELDTAB = Nothing
End Sub
Hi Holger,
Thanks for your code. I tried it but i got some errors. I am not sure if i am missing some component..
I copied your code in a macro in the excel then assign it into a button and this is the error i get.
when i run the macro from the vb designer, i still get the activex error as before.
Is there anything i should install or any add on i should check to remove this error?
Thanks!
Regards,
Joan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi ScriptMan,
I was able to login in my SAP using the third link from your reply. Thank you very much! Atleast now i know that even with excel 2013 (64bit) it can work. Coz i have read in a post that office 2013 64bit is not supported in gui 730.
But to continue my requirement; if i use other script to create connection in background not actually showing the sap screen, it says activex component cannot create object. error is encountered in the first line of code after the data declaration. Thanks again!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.