cancel
Showing results for 
Search instead for 
Did you mean: 

Excel in Macro to open a connection in SAP

Former Member
0 Kudos

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!

Accepted Solutions (1)

Accepted Solutions (1)

holger_khn
Contributor
0 Kudos

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


Former Member
0 Kudos

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!

holger_khn
Contributor
0 Kudos

Hello.

Depence on the recorded script you should decide what Need to be done.

D you already have an recording of required update in SAP Transaction?

Can you upload this recording?

Former Member
0 Kudos

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

holger_khn
Contributor
0 Kudos


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

Former Member
0 Kudos

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

holger_khn
Contributor
0 Kudos

Hello.

Have read again and saw you use Office 64 Bit. That is root cause. 32 Bit ActiveX can not be used by 64 Bit Office. There is no solution available. It´s only work for 32 Bit Office.

Best regards

Holger

Answers (1)

Answers (1)

script_man
Active Contributor
0 Kudos

Hi Mary Joan,

Please see the links at:

or:

or:

Regards,

ScriptMan

Former Member
0 Kudos

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!