Question Re: Instantiating a RFC BAPI using VBA (Excel) in Technology Q&A

Instantiating a RFC BAPI using VBA (Excel)

former_member817179 — Fri, 16 Sep 2022 19:42:21 GMT

I have been able to successfully log onto SAP from inside excel and have a BAPI I'm trying to call. This BAPI takes a single parameter. But I'm trying to figure out how to instantiate the BAPI (using 64-bit controls). Honestly, just looking for the next step after logging on (which I've been able to do using SAPLogonCtrl.

Current Code that is working and the three attempts I've made all failing (commented out all the failed code) 🙂

Dim sapLogon As SAPLogonCtrl.SAPLogonControl
Dim sapConn As SAPLogonCtrl.Connection
Set sapLogon = New SAPLogonControl
Set sapConn = sapLogon.NewConnection
With sapConn
 .System = "ED1"
 .ApplicationServer = "{serverName}"
 .SystemNumber = "{serverNumber}" '
 .Client = "{clientNumber}"
 .User = "user"
 .Password = "userPasword"
 .Language = "EN"
End With
Call sapConn.Logon(0, True)
If sapConn.IsConnected <> tloRfcConnected Then
 MsgBox "connection failed. " & sapConn.IsConnected
 Exit Sub
Else
 MsgBox "Connection Successful!!! - Code " & sapConn.IsConnected '<- this line runs!!!!
End If
'NOTE: I CAN GET TO HERE JUST FINE WITH THE LOGON 
'FIRST ATTEMPT (FAIL)
'Dim RFCFunc As New SAPFunctionsOCX.Function
'Dim SAPFunCtl As New SAPFunctions
'
'Set RFCFunc = SAPFunCtl.Add("Z_BAPI_ALM_ORDER_MAINTAIN_01")
'SECOND ATTEMPT (FAIL)
'Dim functions As Object
'Dim obSapBapi As Object
'
'Set functions = sap.functions(sapConn)
'Set obSapBapi = functions.Add("Z_BAPI_ALM_ORDER_MAINTAIN_01")
'THIRD ATTEMPT (FAIL)
'Dim oSAP As Object
'Dim objGetList As Object
'Set oSAP = sapConn.RfcCOMInstance
'Set objGetList = oSAP.Add("Z_BAPI_ALM_ORDER_MAINTAIN_01")
End Sub

Re: Instantiating a RFC BAPI using VBA (Excel)

Sandra_Rossi — Sat, 17 Sep 2022 07:38:33 GMT

Please explain the symptoms of "attempt (fail)".

Re: Instantiating a RFC BAPI using VBA (Excel)

former_member817179 — Sat, 17 Sep 2022 18:39:50 GMT

Thanks Sandra, very new to this board and still feeling my way around it.

Basically I'm stuck on figuring out these 64-bit VBA libraries (see list below). All the research I've done have come up with either 32-bit solutions or are answers that I've having difficulty deciphering/reproducing.

In the code above, I can instantiate the "sapConn" connection object - but how exactly do I pass that over to an RFC call? I have four VBA libraries referenced in a test macro-enabled spreadsheet (SAP Function Call Control, SAP Table Factory, SAP BAPI Control, SAP Logon Control). My goal here is to pass a parameter to the RFC Function "Z_BAPI_ALM_ORDER_MAINTAIN_01" and I'm not sure of the procedure (or which libraries) to do that.

Re: Instantiating a RFC BAPI using VBA (Excel)

Sandra_Rossi — Sun, 18 Sep 2022 06:48:21 GMT

I thought you were asking about how to solve an error, but you are looking for documentation or examples.

Whatever it's 32 or 64 bits, it's the same VBA code. Just try what you have found for 32 bits, it will work.

Just to confirm, with simple parameters, and Early Binding (set the references in VBA editor):

Dim RFCFunc As SAPFunctionsOCX.Function
Dim SAPFunCtl As SAPFunctions

Set SAPFunCtl = New SAPFunctions

Set RFCFunc = SAPFunCtl.Add("Z_BAPI_ALM_ORDER_MAINTAIN_01")

RFCFunc.Exports("PARAM") = "value"

returnFunc = RFCFunc.Call

die_exception = RFCFunc.Exception

If returnFunc = False Then
    MsgBox "Exception " + die_exception + " when accessing function in SAP ! "
Else
    return = RFCFunc.Imports("RETURN").Value
End If

NB: alas I don't know any official documentation.

Re: Instantiating a RFC BAPI using VBA (Excel)

former_member817179 — Sun, 18 Sep 2022 13:00:04 GMT

Good day Sandra,

Thanks again for your reply. I have done additional testing and am throwing an 'SAP Remote Function Call' error at the location noted in the code. If you have any thoughts on why this might be happening, I'm all ears but I imagine it could be many things?

Public Sub LogonAndRunRFCTest()

Dim sapLogon As SAPLogonCtrl.SAPLogonControl
Dim sapConn As SAPLogonCtrl.Connection

Set sapLogon = New SAPLogonControl
Set sapConn = sapLogon.NewConnection

'Actual arguments provided in testing
With sapConn
    .System = "{System}"
    .ApplicationServer = "{ApplicationServer}"
    .SystemNumber = "{SystemNumber}"             '
    .Client = "{Client}"
    .User = "{User}"
    .Password = "{Password}"
    .Language = "EN"
End With

Call sapConn.Logon(0, True)

'Check if the connection is successful
If sapConn.IsConnected <> tloRfcConnected Then
    MsgBox "connection failed. " & sapConn.IsConnected
    Exit Sub
Else
    MsgBox "Connection Successful!!! - Code " & sapConn.IsConnected '<--- I get this message!
End If

'After successful connection
Dim SAPFunCtl As New SAPFunctionsOCX.SAPFunctions
Dim RFCFunc As New SAPFunctionsOCX.Function
Dim returnFunc As New SAPFunctionsOCX.Function
Dim die_exception As New SAPFunctionsOCX.Function
Dim returnMsg As New SAPFunctionsOCX.Function

SAPFunCtl.Connection = sapConn
MsgBox "Got to this message without issue"  '<---- I get this message!

RFCFunc = SAPFunCtl.Add("Z_BAPI_ALM_ORDER_MAINTAIN_01")  '<--- Exception Thrown {Run-Time Error '1001' SAP Remote Function Call}

'Send Parameter (this is untested as I am throwing an exception in the preceeding line)
RFCFunc.Exports("NUMBER") = "4134440"
returnFunc = RFCFunc.Call

die_exception = RFCFunc.Exception

If returnFunc = False Then
    MsgBox "Exception " + die_exception + " when accessing function in SAP ! "
Else
    returnMsg = RFCFunc.Imports("RETURN").Value
End If

End Sub

Re: Instantiating a RFC BAPI using VBA (Excel)

former_member817179 — Mon, 19 Sep 2022 18:32:43 GMT

Update: 2022-09-419 2:18 PM (EST)

Good day Sandra, so a bit of an update. The exception {Run-Time Error '1001' SAP Remote Function Call} was occurring because I did not have proper RFC authorizations on the SAP back-end. When that was changed and the code rerun, an exception was thrown on the same line but now reads {Class not registered}. I do not think I have the code correct below (perhaps?) particularly around the later half of the code?

Dim sapLogon As SAPLogonCtrl.SAPLogonControl
Dim sapConn As SAPLogonCtrl.Connection

Set sapLogon = New SAPLogonControl
Set sapConn = sapLogon.NewConnection

'Actual arguments provided in testing
With sapConn
    .System = "{SYSTEM}"
    .ApplicationServer = "{ApplicationServer}"
    .SystemNumber = "{SystemNumber}"             '
    .Client = "{Client}"
    .User = "{User}"
    .Password = "{password}"
    .Language = "EN"
End With

Call sapConn.Logon(0, True)

'Check if the connection is successful
If sapConn.IsConnected <> tloRfcConnected Then
    MsgBox "connection failed. " & sapConn.IsConnected
    Exit Sub
Else
    MsgBox "Connection Successful!!! - Code " & sapConn.IsConnected '<--- I get this message!
End If

'After successful connection
Dim SAPFunCtl As New SAPFunctionsOCX.SAPFunctions
Dim RFCFunc As New SAPFunctionsOCX.Function
Dim returnFunc As New SAPFunctionsOCX.Function
Dim die_exception As New SAPFunctionsOCX.Function
Dim returnMsg As New SAPFunctionsOCX.Function

SAPFunCtl.Connection = sapConn
MsgBox "Got to this message without issue"  '<---- I get this message!

'The original exception - {Run-Time Error '1001' SAP Remote Function Call} - is not thrown
RFCFunc = SAPFunCtl.Add("Z_BAPI_ALM_ORDER_MAINTAIN_01")  '<--- New Exception Thrown {Run-Time Error '-2147221164' Class not registered}

'Send Parameter (this is untested as I am throwing an exception in the preceeding line)
RFCFunc.Exports("NUMBER") = "4134440"
returnFunc = RFCFunc.Call

die_exception = RFCFunc.Exception

If returnFunc = False Then
    MsgBox "Exception " + die_exception + " when accessing function in SAP ! "
Else
    returnMsg = RFCFunc.Imports("RETURN").Value
End If

End Sub

Re: Instantiating a RFC BAPI using VBA (Excel)

Ulrich_Schmidt1 — Tue, 18 Oct 2022 08:54:39 GMT

Hello John,

"Class not registered" sounds like a problem on Windows COM level. I am not familiar with the RFC controls/OCX used here, but I think I remember there are three or four ocx libraries. Perhaps one of them has not yet been registered as COM library?

In any case, let me also recommend a different solution: instead of the old OCX controls, have you also thought about using the .NET Connector (NCo)? It can also be used for making RFC calls from Excel or VB scripts and it has two advantages: it's up-to-date technology, and there is lots of documentation and samples. (Samples are mainly in C#, but there is also a short tutorial in VB.NET)

See https://support.sap.com/nco
Current version 3.0 is supported until Dec. 2023, but a new version 3.1 is going to be released in a couple of weeks.

Best Regards, Ulrich