Question Re: Excel (64-Bit) VBA code to connect to SAP to execute a remote function call (RFC) in Technology Q&A

Excel (64-Bit) VBA code to connect to SAP to execute a remote function call (RFC)

former_member817179 — Sun, 14 Aug 2022 09:28:42 GMT

So I'm trying to access SAP inside excel to execute a remote function call.

My machine runs 64-bit excel and from my research, most returns seem to be describing 32-bit library code. I'm looking for those excel users who may have experience in this matter.
I have found the four *.ocx control files and added them as references but are they 32-bit or 64-bit? (How can you tell?)

I'm trying code bits on the web but I can't seem to get anything to work. So yeah, if there's a user who is actively engaged in RFCs on a 64-bit machine, I'd love to hear from you.

ocx libraries (32-bit? 64-bit?)
c:\program files (x86)\sap\frontend\sapgui\wdtaocx.ocx (SAP Table Factory)
c:\program files (x86)\sap\frontend\sapgui\wdobapi.ocx (SAP BAPI Control)
C:\Program Files (x86)\Common Files\SAP Shared\wdtlog.ocx (SAP Logon Control)
c:\program files (x86)\sap\frontend\sapgui\wdtfuncs.ocx (SAP Remote Function Call Control)

Re: Excel (64-Bit) VBA code to connect to SAP to execute a remote function call (RFC)

Ulrich_Schmidt1 — Tue, 18 Oct 2022 10:55:27 GMT

Hi John,

I also already replied to your other question about the "class not registered" error. So yes, I would definitely recommend using the .NET Connector for your project: https://support.sap.com/nco

It has several advantages:

current .NET technology (while the OCX controls are already +20 years old)
no need to mess with COM registration and a dozen .ocx files and dependent .dlls
no extra work involved when dealing with Unicode (NCo handles codepage conversions automatically)
there is definitely a 64bit version available on the download site
easier to get started with, because there is lots of documentation and samples

Best Regards, Ulrich

Re: Excel (64-Bit) VBA code to connect to SAP to execute a remote function call (RFC)

mike_rupp — Sun, 14 Jan 2024 19:54:23 GMT

We have quite a few workbook templates that use VBA to interface with SAP RFCs, and really didn't want to have to rewrite all of the code. We;re now transitioning from Excel 32 bit to 64 bit, and have also struggled with this issue and finally achieved success.

Answering the question in the original post, the ocx files found in folder C:\program files (x86)\sap\frontend\sapgui are 32 bit. To get the 64 bit ocx files you need to install SAP GUI 7.70 or higher and be sure to click the installation check box for the optional "64Bit RFC Controls". When complete you should see a set of identically named ocx files in folder C:\program files\sap\frontend\sapgui.

You may also want to explicitly add these in the VBA "Tools > References" list.

Next, we come to the issue of executing the SAP logon from within VBA and whether or not you make use of SNC Single Sign On. If not, then you just need to be sure to provide all required logon parameters (Client, Language, User, Password, System, Host Name). In our case, we do use Single Sign On, so we withhold (Password), and in some cases also (System & Host Name) which then allows the user to select a system from a pop-up.

However, if you do want Single Sign On, then you need one additional DLL file.
You can find the appropriate DLL for your system in SAP Note 2115486.
In your Environment Variables you may have 1,2, or all 3 of the following (SNC_LIB, SNC_LIB_32, SNC_LIB_64).
If you don't have SNC_LIB_64 then add it and point it to your new 64 bit DLL.

Finally, here is some test code:

Sub Non_Unicode_Logon()
'-Variables-------------------------------------------------------
  Dim SAPFunc, Connection, SAPConnection, PingFunc, retPing
  Dim exceptPing
'-Get SAP.Functions-----------------------------------------------
  Set SAPFunc = CreateObject("SAP.Functions")
  If Not IsObject(SAPFunc) Then
     MsgBox "CreateObject(SAP.Functions) failed", vbOKOnly, "Error"
     Exit Sub
  End If
'-Get SAP.LogonControl connection---------------------------------
  Set Connection = SAPFunc.Connection()
  If Not IsObject(Connection) Then
     MsgBox "SAPFunc.Connection failed", vbOKOnly, "Error"
     Exit Sub
  End If
'-Set connection parameters---------------------------------------
  Connection.client = "301"
  Connection.Language = "EN"
  Connection.User = "Your_Name"
 'Comment Out for SNC Single Sign On
    Connection.Password = "Your_SAP_Password"
    Connection.System = "PRD"
    Connection.HostName = "xyzprd"
    Connection.SystemNumber = 0
'-Connect to SAP system----------------------------------------------
  SAPConnection = Connection.Logon(0, vbFalse)
  If SAPConnection = Flase Then
     MsgBox "Connection.Logon failed", vbOKOnly, "Error"
     Exit Sub
  Else
'----Call ABAP function module RFC_PING ---------------------------
     Set PingFunc = SAPFunc.Add("RFC_PING")
     If IsObject(PingFunc) Then
        retPing = PingFunc.Call()
        If retPing = False Then
           exceptPing = PingFunc.Exception()
           MsgBox CStr(exceptPing), vbOKOnly, "Result"
        Else
           MsgBox CStr(retPing), vbOKOnly, "Result"
        End If
     End If
  End If
'-Logoff--------------------------------------------------------
  SAPConnection = Connection.Logoff()
End Sub

Re: Excel (64-Bit) VBA code to connect to SAP to execute a remote function call (RFC)

John_H — Sun, 28 Jan 2024 17:26:47 GMT

Mike, please excuse the delay in responding to what is a very thorough response (much appreciated). I looked high and low for programming documentation for VBA RFC calls. For example, say I want to write to a particular field under a particular T-code (table?) - where would I go to find out how to do that?