Cell PhonesComputersConsumer ElectronicsGraphic Design & Video EditingHome Theater & AudioIndustrial TechnologyInternet

Silent Connection with SAPLOGON with RFC SDK Example

Updated on November 8, 2016

SAP RFC (Remote Function Call) allows an external program to connect to the SAP system to either read data or write data to the SAP system. The RFC SDK is available when you install the SAP GUI (Frontend) and specify a custom or full installation. The RFC SDK is a very versatile interface tool; it has a COM interface allowing it to be used with VBA (Visual Basic).

This tutorial will walk you through the process of connecting to a SAP system without user intervention, also known as, silent connection. I will use Excel for this example to return a list of sales items that were sold.

Open the Visual Basic Editor in Excel. You might need to enable the Developer Tab in Excel if it is not visible. Search the documentation or check out Customize the ribbon under the Options.

These are the steps that I will perform:

  1. Add a reference to SAP
  2. Create Script
  3. Retrieve Data

Add a reference to SAP


Under Tools | References in the VBA Editor menu, scroll to you find the SAP objects

  • Select SAP Data Provider (sapdattap.ocx)
  • Select SAPGUIRessources (sapguirm.ocx)

Create Script


Under the Modules folder in the VBA Editor in the Project Navigator create a new module (Right click -> new module). I called my module SAP_data but you can give the module any name you want. In the new module create a new Sub, like SAP (see Figure 1).

Enter the code like in Figure 1. You have objects for the worksbook and worksheet; an object for the BAPI Control (oBapiCtrl) and the SAP Login Control (oBapiLogon). Also create an object variable for sales (oSalesOrder) and the sales items (oItems).

Next as is standard practice, you will need to set (initialize) your variables. Once the objects are created, set the logon parameters as in the screenshot. Using the Silent connection requires more information can a user providing the details. In particular, you will need the Application Server address, user and password, the language and finally the system number otherwise you will get an error.

Figure 1: Logon Code for Silient Connection
Figure 1: Logon Code for Silient Connection

Logon Procedure


You can attempt to logon by testing for a successful logon. Like the following code snippet, otherwise you can exit the routine gracefully.

If oBapiCtrl.Connection.Logon(0, True) <> True Then

MsgBox "not connected", vbInformation, "SAP Logon"

Exit Sub

End If

If the system can’t logon, simply exit the sub or as in the example, notify the end user that we couldn’t log onto the system. Otherwise, setup the Worksheet to receive data from SAP. In my example I will get sales orders from the SAP system.

Retrieving Data


In the following code snippet, I test to see if the BAPI Control is connected before setting the oSalesOrder object to avoid throwing an error. The rest is pretty much self-explanatory, you simply map the object properties to the cell address.

Figure 2: Retrieve SAP Business Object and retrieve associated data.
Figure 2: Retrieve SAP Business Object and retrieve associated data.

The last property that I retrieve is the number of items in the order

oSheet.Cells(5, 5).Value = oSalesOrder.items.Count

I will use this value to loop through the items in the order and retrieve the order’s details, as the following code snippet demonstrates:

….

For Each oItem In oSalesOrder.items

oSheet.Cells(iIndex, 1).Value = oItem.SalesDocumentno

oSheet.Cells(iIndex, 2).Value = oItem.itemNo

….

The next section contains the complete code that you can copy and paste into your Excel or VB project. Remember to change the logon values to those appropriate for your company or test environment.

Complete Code


Sub sap()
    Dim oBook As Workbook
    Dim oSheet As Worksheet
    Dim oBapiCtrl As Object
    Dim oBapiLogon As Object
    Dim oSalesOrder As Object
    Dim oCustomer As Object
    Dim oProduct As Object
    Dim oItem As Object
    Dim iIndex As Integer
    
    
    Set oBook = Application.ActiveWorkbook
    Set oSheet = oBook.Worksheets(1)
    
    Set oBapiCtrl = CreateObject("sap.bapi.1")
    Set oBapiLogon = CreateObject("sap.logoncontrol.1")

    oBapiCtrl.Connection = oBapiLogon.NewConnection
    oBapiCtrl.Connection.ApplicationServer = "000.000.000.000"
    oBapiCtrl.Connection.System = "test"
    oBapiCtrl.Connection.Client = "0000"
    oBapiCtrl.Connection.User = "someuser"
    oBapiCtrl.Connection.Password = "xxxxxxxxxx"
    oBapiCtrl.Connection.Language = "EN"
    oBapiCtrl.Connection.SystemNumber = "00"
    
   If oBapiCtrl.Connection.Logon(0, True) <> True Then
    MsgBox "not connected", vbInformation, "SAP Logon"
    Exit Sub
   End If
    
   If oBapiCtrl.Connection.IsConnected Then
    Set oSalesOrder = oBapiCtrl.GetSAPObject("SalesOrder", "0000000000")
    oSheet.Cells(4, 1).Value = "Sales Document"
    oSheet.Cells(4, 2).Value = "Net Value"
    oSheet.Cells(4, 3).Value = "Customer Number"
    oSheet.Cells(4, 4).Value = "Document Date"
    oSheet.Cells(4, 5).Value = "Number of Items"

    oSheet.Cells(5, 1).Value = oSalesOrder.salesdocument
    oSheet.Cells(5, 2).Value = oSalesOrder.netvalue
    oSheet.Cells(5, 3).Value = oSalesOrder.orderingparty.customerno
    oSheet.Cells(5, 4).Value = oSalesOrder.documentdate
    oSheet.Cells(5, 5).Value = oSalesOrder.items.Count
   End If
    
    iIndex = 8

â Loop through the order item details
        oSheet.Cells(7, 1).Value = "Order No"
        oSheet.Cells(7, 2).Value = "Item No"
        oSheet.Cells(7, 3).Value = "Product Code"
        oSheet.Cells(7, 4).Value = "Product Description"
        oSheet.Cells(7, 5).Value = "Selling Quantity"
        oSheet.Cells(7, 6).Value = "Selling UoM"
        oSheet.Cells(7, 7).Value = "Product Description"
    
    
    For Each oItem In oSalesOrder.items
        oSheet.Cells(iIndex, 1).Value = oItem.SalesDocumentno
        oSheet.Cells(iIndex, 2).Value = oItem.itemNo
        oSheet.Cells(iIndex, 3).Value = oItem.material.material
        oSheet.Cells(iIndex, 4).Value = oItem.material.materialdescription
        oSheet.Cells(iIndex, 5).Value = oItem.targetquantity
        oSheet.Cells(iIndex, 6).Value = oItem.targetquantityunit
        oSheet.Cells(iIndex, 7).Value = oItem.Description
        iIndex = iIndex + 1
    Next
    
    oSheet.Columns.AutoFit
    Set oBapiLogon = Nothing
    Set oBapiCtrl = Nothing
End Sub

Caveat: In is critical to get acquainted with the BAPI Explorer, Transaction: BAPI, in SAP to find the Business Objects, the import parameters and fields and methods. Also you will find the corresponding BAPIs and you can run tests to check out what the function is returning as values.

I hope the article was helpful and remember to get to know the BAPI Explorer. The answers are in there, except you will need to look for them.

Comments

    0 of 8192 characters used
    Post Comment

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      I am pleased to acknowledge that this article resolved my queries and I finally developed a tool which allows users to get data from SAP system using BAPI.

      I am really grateful to the author for presenting such a nice stuff.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Thanks for the kind works. I am really glad I was able to help.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      With the help of klanduedoc's knowledge which he shared above, I managed to develop a reporting system which uses the silent connection to SAP downloads sale orders headers & lineitems in two separate table in access database and then it generates sales & targets reports on daily basis. The users are happy.

      One thing I am looking for. That is, a way (preferably a BAPI) to which I give date as a parameter and it should return to me a list of all sale orders for that particular day.

      Why Do I need That?

      Because I intend to use that list to monitor the changes which frequently occur in our scenario. For example a sale order which was originally entered for customer x last month... Now the same sale order is changed, it now belongs to customer Y. Another example is: A sale order in which 20 items of a product were sold last month, now the same saleorder is showing 35 items...

      looking forward to the guideline (source code - hints)

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Thanks AmjadM,

      If you need a BAPI or a Remote RFC, you will need to write one or use the BAPIs like:

      BAPISDORDER_GETDETAILEDLIST.

      or

      BAPI_GET_SALES_ORDER_ITEM

      For sales orders it might be easier to use a couple of standard BAPIs since these do several checks and validates the data properly.

      Check out the different BAPis under the Sales Order object in BAPI explorer in SAP.

      Let me know how it works out for you

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Thanks klanguedoc

      I used one of the BAPIs which you mentioned i.e BAPISDORDER_GETDETAILEDLIST... and I have yet to explore the other one BAPI_GET_SALES_ORDER_ITEM...

      I will let your know soon how it worked out for me.

      Regards

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hi klanguedoc,

      I Need a BAPI to which I pass 'date' as a parameter and it should return to me a list of all sale orders for that particular day....

      BAPI_GET_SALES_ORDER_ITEM requires a Sale Document Number rather tha doc date.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Hi AmjadM

      I use this one however the date is the document date, ie the date when the order is entered.

      BAPI_SALESORDER_GETLIST

      I also use

      BAPI_SALESORDER_GETSTATUS

      to get order details once I have a list of orders

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hi klanguedoc

      Can you help me write Excel/vba equilent of the following ABAB Code ....

      BAPI-SALES ORDER GETLIST

      REPORT ZSALESORDER_GETLIST.

      TABLES : bapi1007,

      bapiorders.

      DATA : salesorder_itab LIKE bapiorders OCCURS 0 WITH HEADER LINE.

      DATA : cust LIKE bapi1007-customer VALUE '0000325120',

      sorg LIKE bapiorders-sales_org VALUE '1020'.

      CALL FUNCTION 'BAPI_SALESORDER_GETLIST'

      EXPORTING

      customer_number = cust

      sales_organization = sorg

      *MATERIAL =

      *DOCUMENT_DATE =

      *DOCUMENT_DATE_TO =

      *PURCHASE_ORDER =

      *TRANSACTION_GROUP = 0

      *PURCHASE_ORDER_NUMBER =

      *IMPORTING

      *return = it_return

      TABLES

      sales_orders = salesorder_itab.

      loop at salesorder_itab.

      write : salesorder_itab-sd_doc.

      endloop.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Sorry, I can't reproduce abap code in Excel/VBA. All you can hope to do is call or use the abap functions (RFC & BAPIs) in external code like VBA.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      OK! Fine..

      I am calling BAPI_SALESORDER_GETLIST... It returns a list of sale orders of a given date. now I want to transfer them into a free table/ ADODB recordset for further analysis.

      The section of code where I am stuckup is somewhat like this:

      Set objSalesDoc = objBAPI_SalesOrder.Tables("SALES_DOCUMENTS") 'sales order number

      objSalesDoc.Rows.Add

      objSalesDoc(1, 1) = Format(Ord, "0000000000")

      Set objBapiView = objBAPI_SalesOrder.Exports("I_BAPI_VIEW")

      objBapiView("Header") = "X"

      Set ESOHeader = objBAPI_SalesOrder.Tables("ORDER_HEADERS_OUT") 'sales order header data

      ESOHeader.FreeTable

      objBapiView("Item") = "X"

      This section of code works well for the other BAPI which you once mentioned (BAPISDORDER_GETDETAILEDLIST) .

      The question is what adjustment is required in the following lines:

      1) Set objSalesDoc = objBAPI_SalesOrder.Tables("SALES_DOCUMENTS")

      2)

      Set objBapiView = objBAPI_SalesOrder.Exports("I_BAPI_VIEW")

      objBapiView("Header") = "X"

      Set ESOHeader = objBAPI_SalesOrder.Tables("ORDER_HEADERS_OUT") 'sales order header data

      ESOHeader.FreeTable

      objBapiView("Item") = "X"

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Why not export the data into an Excel worksheet, which provides much more flexibility for analysis and less code, or save to an Access or SQL server database. This is what I do, since there is much less overhead to deal with.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      One Advantage in doing my way is that My application can run off it's own unattendedly by any users... you can schedule the application to run at your desired/ given time...

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      ok, thanks

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Thank you very much Klanguedoc...

      Can anybody share with me a sample vba code showing how one passes parameters to (SD, MM, FI Related) BAPI and extracts data onto Excel-Sheet or Access-Table. This is my experience that whenever I comeacross a specimen VBA code, I learn new things...

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Set oSalesOrder = oBapiCtrl.Add("BAPI_SALESORDER_GETSTATUS")

      oSalesOrder.exports("SALESDOCUMENT").Value = Me.orderNbr.Text

      Set Row = oSalesOrder.Tables("Statusinfo")

      For Each Data In Row.Rows

      aValue = Data("DLV_QTY")

      Next

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hello Experts,

      I need a Report/list of the customers for whom a particular text is not maintained in customer master -sales data.

      There are many Z text id are configured and are maintained in Sales data- extras- text. In addition, business also need some key fields from the customer master such as name, city, delv plant, payment terms, sales off, sales grp etc.

      I could not get any standard table where the text is maintained with the customer numbers. table STXH can give the customer numbers for a particular text id type but what is maintained against the given text id is not fetched from the table.

      So we need the valuable inputs to realize the requirement.

      As an proposal, we are aiming to develop custom Z Report with the FM READ_TEXT to get the text information with the customer no ( object type KNVV and text id ). But for the additional information, what should be the actual logic for fetching the data. say if this FM is used, what should be the sequence of the logic and ideal selection parameters.

      In addition, we aim to use BAPI BAPI_CUSTOMER_GETDETAIL2 to fetch some of the customer ifnormation but many fields are not found in this . can we use some other bapi which can provide the complete general/sales data of customer ( delv plant, sales off, sales grp, custo grp, incoterms, payment terms etc)

      Please suggest accordingly.

      Many Thanks

      AmjadM

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      For the SalesOrder you can use the bapi_salesorder_getstatus.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Klanguedoc,

      I want value of

      Table KNVV

      Field KVGR1

      For a given customer ID

      Is this possible through bapi_salesorder_getstatus?

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      No this bapi requires a order number. Why don't write your own rfc?

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Yes! This is where I need help. I have been using ready-made Bapi's... never got a chance to write an RFC. I have got access permissions for se38, but there it requires a key when you click on create button. The key which was alotted to me in June-2012 is no-longer valid...SAP-admin is currently looking into this.Meanwhile if you please guide me how to create an rfc to get KVGR1 FROM KNVV TABLE for a given customer number.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Ok I am write it for you

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Thank you, I willl be obliged.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      AmjadM

      Here is the code for the RFC

      javascript:hpLoadLink('article',3488847, '', '')...

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      klanguedoc

      Thanks for your support, I have asked my ABAPer to create the RFC for me and transport the same to production. As soon as this is done I hope I will write the report routine in VBA .

      Thanks a lot for your guidelines.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Congrats! Klanguedoc...

      Z_SD_SALESINFO_NEW has been created by our ABAPER, transported to PRD and is functioning fine. It takes custnumber (CUSTOMER_NBR) as input and returns the corresponding CUSTOMER_GRP as export parmeter (value of KVGR1). Now I am proceeding to VBA coding any tip if you can share for VBA Code...

      If I see the VBA Code which you provided, Shall I do something like this:

      If oBapiCtrl.Connection.IsConnected Then Set oSalesOrder = oBapiCtrl.GetSAPObject("Z_SD_SALESINFO_NEW", "0000600030")

      oSheet.Cells(5, 1).Value = Z_SD_SALESINFO_NEW.CUSTOMER_GRP

      End If

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      yes exactly

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hi KlangueDoc...

      Runtime error '8799' :

      The Type Information from the Business Object type

      Z_SD_SALESINFO_NEW could not be retrieved from Business Object Repository

      Object type Z_SD_SALES is not defined

      KlangueDoc ! the above error pops up when the program reach the following command:

      Set oCustGrp = oBAPICtrl.GetSAPObject("Z_SD_SALESINFO_NEW", Format(oSalesOrder.orderingparty.customerno, "0000000000"))

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hi KlangueDoc...

      Please Let me rephrase my query:

      Runtime error '8799' :

      The Type Information from the Business Object type

      Z_SD_SALESINFO_NEW could not be retrieved from Business Object Repository

      Object type Z_SD_SALES is not defined

      KlangueDoc ! the above error pops up when the program reach the following command:

      Set oCustGrp = oBAPICtrl.GetSAPObject("Z_SD_SALESINFO_NEW", Format(oSalesOrder.orderingparty.customerno, "0000000000"))

      One More Question:

      Instead of setting oBAPICtrl.GetSAPObject as mentioned above, what

      if I set up an RFC by Set RfcCallTransaction = Functions.Add("Z_SD_SALESINFO_NEW")

      What is the difference in the both SET Commands

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Hi AmjadM,

      First did you set remote access attribute? This error sounds like the RFC is not visible or it hasn't been transported. Try using se37 in SAP to test output.

      The outcome for your second question should be the same, however I always use the first version, however I have seen many programmers use the second.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Thank you very much KlangueDoc.

      I have reconfirmed from our ABAPER .. He has shown me that the RFC is enabled and SE37 correctly returns the value (in PRD).

      Can you test the same at your end. I would be grateful if you write a test_VBA Code for me, I badly need this to work. Sorry for inconveniences I must have caused to you.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hi KlangueDoc,

      I figured out the thing which I missed.

      I drew a anology to The RFC setup which is working is :

      Set objSalesDoc = objBAPI_SalesOrder.Tables("SALES_DOCUMENTS")

      where objBAPI_SalesOrder has been set as follows:

      Set objBAPI_SalesOrder= sapConn.Add("BAPISDORDER_GETDETAILEDLIST")

      But the issue is if I do this for Customer group:

      Set objCustGrp = objBAPI_Z_SD_SALESINFO_NEW.Tables("CUSTOMER_GRP")

      where the object objBAPI_Z_SD_SALESINFO_NEW has been successfully set as follows:

      Set objBAPI_Z_SD_SALESINFO_NEW= sapConn.Add("Z_SD_SALESINFO_NEW")

      The following command returns an error 'Object required':

      Set objCustGrp = objBAPI_Z_SD_SALESINFO_NEW.Tables("CUSTOMER_GRP")

      it returns an error saying Object required. Obvioulsy, as per my understanding our newly created RFC does not return tables.

      Any advise on this.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      AmjadM,

      You are correct, the RFC does not have any tables or structures. It returns a field, so you need to do this objBAPI_Z_SD_SALESINFO_NEW.Customer_Grp since you are only returning a field.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Thanks KlangueDoc, I am now able to retrieve data from SAP Tables, I have got the grip now...Wonderful!

      Cheers.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Great.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      KlangueDoc,

      Please refer to the last line of your article:

      Quote:

      and remember to get to know the BAPI Explorer. The answers are in there, except you will need to look for them.

      End Quote:

      Is there any BAPI which takes FinishedGood code as an input, and returns the storage-location wise stocks-figure for a given date?

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hi KlangueDoc,

      I got a clue but I am not really sure if I can handle it alone.

      Jon Willoz wrote:(http://scn.sap.com/thread/856979)

      There is a BAPI that can accomplish what you want. It is just in a place you wouldn't expect. BAPI_MATERIAL_STOCK_REQ_LIST will give you the output you would get from transaction MD04, but another part of the BAPI output is business object MrpStockDetail. It is returned according to structure BAPI_MRP_STOCK_DETAIL, which has all kinds of goodies like unrestricted stock, blocked stock, stock in quality inspection, etc...

      I look forwards to your comments as to how should I go about this. Have you ever tried this or something simmilar?

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Hi AmjadM,

      I haven't worked with these BAPIs but the process is the same as the other RFC. Identity the input fields and output fields then write a query to get the needed information.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Hi klanguedoc,

      There is a new requirement in our department to develop an FM which takes SalesOrder document dates as input ...(Date_From, Date_To) and returns only the document numbers for the given period.

      An FM with the following source code was created. It runs well when we execute it through SE37.

      Here is the ABAP source code.

      FUNCTION Z_SD_RETURNSALEDOC2.

      *"----------------------------------------------------------------------

      *"*"Local Interface:

      *" IMPORTING

      *" VALUE(FROM_DATE) TYPE FKDAT

      *" VALUE(TO_DATE) TYPE FKDAT

      *" EXPORTING

      *" VALUE(BILL_DOC) TYPE VBELN

      *" TABLES

      *" I_VBAK STRUCTURE VBAK OPTIONAL

      *" EXCEPTIONS

      *" NO_VALUE

      *"----------------------------------------------------------------------

      Break AMMAD_ABAP.

      select VBELN AUART INTO CORRESPONDING FIELDS OF TABLE I_VBAK from VBAK

      WHERE AUDAT GE FROM_DATE AND AUDAT LE TO_DATE AND AUART IN ('ZLOS','ZEXP') .

      IF BILL_DOC EQ ''.

      *RAISE NO_VALUE.

      ENDIF.

      ENDFUNCTION.

      The issue is that our target data is not returned in I_VBAK STRUCTURE. under I_VBAK two tables are returned one is blank, i.e Zero records, the other has correct number of records. It is returned in some other table which we can browse in SE37 but in order to get that data through VBA we need the table name.

      The code which I am writing is something like this:

      Set FunctionCtrl = CreateObject("SAP.Functions")

      Set SapConnection = FunctionCtrl.Connection

      SapConnection.ApplicationServer = Sheets("Check Tables").Range("A4")

      SapConnection.SystemNumber = "00"

      SapConnection.System = "Pro"

      SapConnection.Client = Sheets("Check Tables").Range("A3")

      SapConnection.Language = "EN"

      SapConnection.User = Sheets("Check Tables").Range("A1")

      SapConnection.Password = Sheets("Check Tables").Range("A2")

      If not SapConnection.Logon(0, True) = True Then 'Logon mit Dialog

      Set SapConnection = Nothing

      DoCmd.Hourglass False

      MsgBox "No connection to SAP R/3 !"

      'SAPLOGIN = False

      'SAPLOG = False

      'Exit Function

      End If

      'SAPLOG = True

      Set func1 = FunctionCtrl.Add("Z_SD_RETURNSALEDOC2")

      ' Export-Paramter definieren

      func1.exports("FROM_DATE") = Sheets("Parameters").Range("B6")

      func1.exports("TO_DATE") = Sheets("Parameters").Range("B7")

      DoEvents

      If Not func1.call Then

      If not func1.exception = "" Then

      'MsgBox "Communication Error with RFC " & func1.exception

      Sheets("Error_log").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0) = func1.exception

      Sheets("Error_log").Range("A" & Cells.Rows.Count).End(xlUp).Offset(0, 1) = Sheets("Ordersupdate").Range("D" & Cells.Rows.Count).End(xlUp)

      Sheets("Error_log").Range("A" & Cells.Rows.Count).End(xlUp).Offset(0, 2) = Sheets("Ordersupdate").Range("A1")

      Sheets("Error_log").Range("A" & Cells.Rows.Count).End(xlUp).Offset(0, 3) = Sheets("Ordersupdate").Range("J" & Cells.Rows.Count).End(xlUp)

      Sheets("Error_log").Range("A" & Cells.Rows.Count).End(xlUp).Offset(0, 4) = Sheets("Ordersupdate").Range("A2")

      End If

      'DoCmd.Hourglass False

      SAPLOGOUT

      'Exit Function

      Else

      Set ESOItems = func1.Tables("I_VBAK")

      ESOItems.FreeTable

      Endif

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      amjadM,

      Not sure I understand your question...

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      I am sorry ... May I try again?

      We wanted a Functional Module to which we pass on a range of dates (Date_From and Date_to). The Functional Module subsequently would return list of Sales Documents with Doc dates falling within the given range.

      That FM (Z_SD_RETURNSALEDOC2) has been created by our ABAPER. The source code is already given. It's runs fine when we execute it thru SE37. But the question is how to get the same result using VBA.

      In VBA I successfully have the system passon the parameters, but when the following commands are executed:

      et ESOItems = func1.Tables("I_VBAK")

      ESOItems.FreeTable

      The system shows empty table.

      This is where I need your help.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      is the value in Sheets("Parameters").Range("B6") and B7 actually dates. Try converting using cdat

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Which Date format SAP recognizes?

      mmddyyyy

      ddmmyyyy

      or

      mm.dd.yyyy

      dd.mm.yyyy

      I tried all but no luck. I had a look on Debug/AddWatch, It seems it that parameters are being passed but the recordsets are not correctly returned, SAP somehow returns empty data fields.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Can I send u output display of SE37 SAP (screen shot) so that the problem can easily be conveyed to you..

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      yes you can send the screenshot to kevinlanguedoc[at]gmail.com SAP uses mm/dd/yyyy date format. Example 02/28/2013

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Thank you..The email has been sent...

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      I have re-sent my email after including additional screen shot which I hope would be useful.

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Thank you Klanguedoc,

      I read again your previous post and revised my VBA code as follows:

      Set FunctionCtrl = CreateObject("SAP.Functions")

      Set func1 = FunctionCtrl.Add("Z_SD_RETURNSALEDOC2")

      func1.exports("FROM_DATE") = DateValue(Sheets("Parameters").Range("B5"))

      func1.exports("TO_DATE") = DateValue(Sheets("Parameters").Range("B6"))

      'func1.Call

      This returned correct number of records..

      Thank you very much.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      AmjadM,

      I am very glad you have been able to work this out. I get many request to debug or write people's code however I have too many projects and family responsibilities to do this.

      kevin

    • profile image

      USAMax 4 years ago

      Unfortunately, because of the age of this article, I cannot find any SAP objects within References in Office 2010. It all sounds wonderful in theory but without the SAP object it fails once I get to the line:

      Set oBapiCtrl = CreateObject("sap.bapi.1")

      Microsoft keeps changing things and the struggle goes on...

    • AmjadM profile image

      AmjadM 4 years ago from Karachi

      Klanguedoc,

      Good news here is that by using BAPI's in VBA, I have recently developed a custom solution for MM Users for stock movements report. The Standard reports were very very slow and huge time (some times taking more than 10 hours!) were consumed for a simple report... I don't know why it was like that? But using BAPI's (VBA) now we get the same information in a few minutes. Thanks to all who shared great things... and please keep sharing ... I feel I have discovered such an ocean, the deeper I go, the better I feel.

      Thank you very much..

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      USAMax, I am using Excel/VBA 2010 with SAP RFC. what version of SAPgui are you using.

      AmjadM,

      nice to hear from you

    • profile image

      Apichart 3 years ago

      Hello klanguedoc ,

      I did follow your code but unfortunately It always get into not connect statement.

      I think that I did wrong on credential information or network configuration.

      May I ask you that do I need to specify Connection.System, Connection.Client and Connection.SystemNumber ?

    • profile image

      Apichart 3 years ago

      Apologize for my rashness, I have realized that I have to omit Connection.System

      Thank you.

    • donizeth profile image

      donizeth 3 years ago

      I'm trying to adapt your code to use as shown below to be able to connect and run a silent scripting sap, but is giving error from the line:

      oBapiCtrl.findById("wnd[0]").Maximize

      Could anyone help me?

      My code:

      Dim oBapiCtrl As Object

      Dim oBapiLogon As Object

      Set oBapiCtrl = CreateObject("sap.bapi.1")

      Set oBapiLogon = CreateObject("sap.logoncontrol.1")

      oBapiCtrl.connection = oBapiLogon.NewConnection

      oBapiCtrl.connection.ApplicationServer = "xxx.xxx.x.xxx"

      oBapiCtrl.connection.System = "xxx"

      oBapiCtrl.connection.Client = "xxx"

      oBapiCtrl.connection.User = "xxxx"

      oBapiCtrl.connection.Password = "xxxxxx"

      oBapiCtrl.connection.Language = "EN"

      oBapiCtrl.connection.SystemNumber = "00"

      If oBapiCtrl.connection.Logon(0, True) dif True Then

      MsgBox "not connected", vbInformation, "SAP Logon"

      Exit Sub

      End If

      If oBapiCtrl.connection.IsConnected Then

      oBapiCtrl.findById("wnd[0]").Maximize

      oBapiCtrl.findById("wnd[0]/tbar[0]/okcd").Text = "/nf-51"

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

      oBapiCtrl.findById("wnd[0]/usr/ctxtBKPF-BLDAT").Text = "150513"

      oBapiCtrl.findById("wnd[0]/usr/txtBKPF-XBLNR").Text = "819"

      oBapiCtrl.findById("wnd[0]/tbar[0]/btn[11]").press

      oBapiCtrl.findById("wnd[0]/tbar[0]/btn[11]").press

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWBS").Text = "31"

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWKO").Text = "100924"

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWKO").SetFocus

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWKO").caretPosition = 6

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

      oBapiCtrl.findById("wnd[0]/usr/txtBSEG-WRBTR").Text = "2000"

      oBapiCtrl.findById("wnd[0]/usr/ctxtBSEG-BUPLA").Text = "0004"

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWBS").Text = "21"

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWKO").Text = "100924"

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWKO").SetFocus

      oBapiCtrl.findById("wnd[0]/usr/ctxtRF05A-NEWKO").caretPosition = 6

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

      oBapiCtrl.findById("wnd[0]/usr/txtBSEG-WRBTR").Text = "2000"

      oBapiCtrl.findById("wnd[0]/usr/ctxtBSEG-BUPLA").Text = "0004"

      oBapiCtrl.findById("wnd[0]/usr/ctxtBSEG-BUPLA").SetFocus

      oBapiCtrl.findById("wnd[0]/usr/ctxtBSEG-BUPLA").caretPosition = 4

      oBapiCtrl.findById("wnd[0]/tbar[0]/btn[11]").press

      oBapiCtrl.findById("wnd[0]/tbar[0]/btn[3]").press

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

      End If

      Set oBapiLogon = Nothing

      Set oBapiCtrl = Nothing

    • AmjadM profile image

      AmjadM 3 years ago from Karachi

      if you are trying to execute JavaScript function with VBA please do go through: http://stackoverflow.com/questions/3247391/click-b...

      I hope you will figure out what's missing in your work...I am not sure...my guess is you need a reference as the article says:

      But first you will need to include Microsoft HTML Object Library under References (Tools--References)

    • klanguedoc profile image
      Author

      Kevin Languedoc 3 years ago from Canada

      donizeth,

      Why are you trying to use the t-code to access the bapi? Why no just use the bapi? If you are connecting then you already have the necessary references.

      AmjadM,

      Yes work is crazy. I am often to tired at night other than just relax with my family. I don't undertand how your comment relates the SAP silent logon.

    • profile image

      Timo van Esch 3 years ago

      Hi Klanguedoc,

      Your script seems the opening the team I joined this week needs to start coding for real. This way of connecting seems much more elegant than the version used here:

      Dim SAPguiAPP As SAPFEWSELib.GuiApplication

      Dim Connection As SAPFEWSELib.GuiConnection

      Dim Session As SAPFEWSELib.GuiSession

      Dim SapGuiAuto As Object

      If SAPguiAPP Is Nothing Then

      Set SapGuiAuto = GetObject("SAPGUI")

      Set SAPguiAPP = SapGuiAuto.GetScriptingEngine

      End If

      If Connection Is Nothing Then

      Set Connection = SAPguiAPP.Children(0)

      End If

      If Session Is Nothing Then

      Set Session = Connection.Children(0) ' And the problem is resolved at this line

      End If

      It is being used with the macro recorder, session-blabla:

      Session.findById("wnd[0]/tbar[0]/okcd").Text = "/N" & TrString

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

      Session.findById("wnd[0]").Maximize

      Session.findById("wnd[0]").sendVKey 17

      Session.findById("wnd[1]/usr/txtV-LOW").Text = VarString

      Session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 8

      Session.findById("wnd[1]").sendVKey 8

      Session.findById("wnd[0]/usr/ctxtR_" & TrSAP & "-LOW").Text = FirstDate

      Session.findById("wnd[0]/usr/ctxtR_" & TrSAP & "-HIGH").Text = LastDate

      Session.findById("wnd[0]/usr/ctxtP_DISVAR").SetFocus

      Session.findById("wnd[0]/usr/ctxtP_DISVAR").caretPosition = 5

      Session.findById("wnd[0]").sendVKey 11

      Session.findById("wnd[0]").sendVKey 11

      Session.findById("wnd[1]/usr/btnBUTTON_1").press

      I am completely new at SAP, but I do not trust macro recordings.

      How could I convert those macro session settings to decent VBA code?

      Thanks in Advance & with kind regards,

      Timo

    • AmjadM profile image

      AmjadM 3 years ago from Karachi

      Hi Timo,

      Nice to see your comments here.

      First of all, you have to clear your mind from doubts which you feel about usefulness of macro recordings...Be it Excel, Be it SAP, macro-recording gives you an opportunity of flying start.

      Honestly speaking, Klaguedoc's hubpages also have been very helpful for me.

      I can provide you with some useful samples of VBAs.. contact me at pakbev(dot)com and my ID is Amjad.Mahmood ... I don't mind in paste-ing the VBA code here but that may be boring for experts, I believe.

    • klanguedoc profile image
      Author

      Kevin Languedoc 3 years ago from Canada

      Hi Timo

      Sorry for the delay. I've been pretty on projects....

      Let me review your code and hopefully I can guide you down the right path.

      Macros are ok for internal Excel stuff but when you want to access external objects like SAP, it is best to use your own code.

      Take a look at my code, you need to create an object for the SAPLogon first. then create another object for the RFCs or BAPIs that you want to use as in my code. For BAPIs or RFCs, you need to acquaint yourself with the BAPI Explorer in SAP. Use the BAPI t code.

      Kevin

    • klanguedoc profile image
      Author

      Kevin Languedoc 3 years ago from Canada

      Btw I am working on a book on SAP interfaces for Visual Basic and its variants (vbs, vba)

    • profile image

      Marco Rossi 3 years ago

      In example is shown how show details for a specific sales order. How I do for list all sales orders?

    • klanguedoc profile image
      Author

      Kevin Languedoc 3 years ago from Canada

      Marco

      I didn't try this but either create the object without the sales order

      Set oSalesOrder = oBapiCtrl.GetSAPObject("SalesOrder")

      or

      specify a numbered range

      for order = 0000000001 to 0100000000

      Set oSalesOrder = oBapiCtrl.GetSAPObject("SalesOrder", cstr(order))

    • profile image

      Ravindra 2 years ago

      It helped me today

    • profile image

      Madhu 4 months ago

      I have copied the code and getting an error "Run time error '429' Activex can't create the object.

      Error at line: Set oBapiCtrl = CreateObject("sap.bapi.1")

      Please help

    • profile image

      Setu 4 months ago

      Hello,

      I am using Winshuttle for most of thee automation, i came across one requirement where i need to run rfc enabled bapi code from excel using vba, however i can not logon using below code as my organization use EP logon to SAP. Please suggest any as any modification i can do to below code to work?

      Thank you.

      Sub SAPLOGON()

      Dim obSAPFnCntrl As Object

      Dim obSAPConn As Object

      Set obSAPFnCntrl = CreateObject("SAP.Functions")

      Set obSAPConn = obSAPFnCntrl.Connection

      With obSAPConn

      .ApplicationServer = "ServerName"

      .System = "D10"

      .SystemNumber = "00"

      .User = "UName"

      .Password = "Pass"

      .Language = "EN"

      .Client = "800"

      End With

      If obSAPConn.Logon(0, False) = True Then

      MsgBox "R/3 connection succesfull"

      Exit Sub

      End If

      End Sub

    • klanguedoc profile image
      Author

      Kevin Languedoc 2 months ago from Canada

      Hi Setu,

      I am not aware of EP SAP logon. I don't work with SAP anymore.

    • klanguedoc profile image
      Author

      Kevin Languedoc 2 months ago from Canada

      Hi Madhu

      Be careful of the sdk version. This code is based on version 6.20 of the rfcsdk which corresponds to the Sapgui

    • profile image

      singer0000 2 weeks ago

      Hi Kevin, I would like to thank you first for your efforts. I have tried your code but it seems I can't find anywhere the refrences you mentioned, any idea why?

      I code run different RFC successfully like the below,

      ' Example calling BAPI RFC_GET_TABLE_ENTRIES

      ' SAP BAPI Scripting Exercise

      ' This file is provided as is. Please use and modify it for your own use.

      ' Check out my playlist of SAP GUI and SAP Functions related videos:

      ' https://www.youtube.com/watch?v=oPPhA14Pm-8&li...

      '

      ' Cheers, Csongor

      Option Explicit

      Public Functions As SAPFunctionsOCX.SAPFunctions

      Private LogonControl As SAPLogonCtrl.SAPLogonControl

      Private objConnection As SAPLogonCtrl.Connection

      Dim Func As SAPFunctionsOCX.Function

      Public eNUMBER_OF_ENTRIES As SAPFunctionsOCX.Parameter

      Public tENTRIES As SAPTableFactoryCtrl.Table

      Public TableFactory As SAPTableFactory

      Private LogCount As Integer

      Public shScript, shLog As Worksheet

      Sub TableEntriesRFC()

      Dim i As Integer

      Dim retcd As Boolean

      Dim SilentLogon As Boolean

      Set shScript = Worksheets("Script")

      ResetLog

      Set LogonControl = CreateObject("SAP.LogonControl.1")

      Set Functions = CreateObject("SAP.Functions")

      Set TableFactory = CreateObject("SAP.TableFactory.1")

      Set objConnection = LogonControl.NewConnection

      SilentLogon = False

      'Use the below block to hardcode system connection and connect automatically

      'objConnection.Client = ""

      'objConnection.ApplicationServer = ""

      'objConnection.Language = ""

      'objConnection.User = ""

      objConnection.Password = "******"

      'objConnection.System = ""

      'objConnection.SystemID = ""

      'objConnection.SystemNumber = ""

      'objConnection.UseSAPLogonIni = False

      'SilentLogon = True

      'End of autologon block

      'Set the hourglass pointer

      'Application.Cursor = xlWait

      AddLog "Logon", "Logging into SAP...", vbBlack

      ' Logging into SAP

      If objConnection.Logon(0, SilentLogon) Then

      'Logon was successful

      AddLog "Logon", "Successfully logged into SAP", vbBlack

      'Create an object to call the RFC FM

      Functions.Connection = objConnection

      AddLog "RFC", "Preparing the interface", vbBlack

      'Actual FM is added here

      Set Func = Functions.Add("RFC_GET_TABLE_ENTRIES")

      'Set Func = Functions.Add("RFC_READ_TABLE")

      'Populate the importing parameters

      Func.Exports("TABLE_NAME").Value = Range("TableName").Value

      'Func.Exports("query_table").Value = Range("TableName").Value

      'Declare the exporting parameters and tables

      Set eNUMBER_OF_ENTRIES = Func.Imports("NUMBER_OF_ENTRIES")

      Set tENTRIES = Func.Tables("ENTRIES")

      'Executing the FM

      AddLog "RFC", "Executing call", vbBlack

      Func.Call

      AddLog "RFC", "Number of entries: " + CStr(eNUMBER_OF_ENTRIES), vbBlack

      'Extract the results to Excels to D10 and down

      For i = 1 To tENTRIES.RowCount

      Cells(9 + i, 4).Value = tENTRIES(i, 1)

      Next

      Else

      'Execute this block if logon fails

      Dim msg As String

      msg = "Failed to login to SAP. Verify credentials or access."

      AddLog "Logon", msg, vbRed

      Application.Cursor = xlDefault

      MsgBox msg

      GoTo Cleanup

      End If

      Cleanup:

      'Update the timestamp

      Range("Timestamp").Value = Now()

      'Close the connection to SAP

      objConnection.Logoff

      AddLog "Logout", "Logged out of SAP", vbBlack

      'Set the pointer back to default cursor

      Application.Cursor = xlDefault

      MsgBox "Script completed"

      End Sub

      Sub ResetLog()

      'This function deleted the current log and resets some values

      Dim LastRow As Integer

      Set shLog = Worksheets("Log")

      LogCount = 0

      shLog.Select

      'Find the last row in the sheet

      LastRow = shLog.UsedRange.Rows(shLog.UsedRange.Rows.Count).Row

      Rows("3:" + CStr(LastRow)).Select

      Selection.Delete Shift:=xlUp

      Range("A3").Select

      shScript.Select

      End Sub

      Sub AddLog(id As String, message As String, color As Integer)

      'New line is added to the log

      shLog.Cells(LogCount + 3, 1).Font.color = color

      shLog.Cells(LogCount + 3, 2).Font.color = color

      shLog.Cells(LogCount + 3, 1) = id

      shLog.Cells(LogCount + 3, 2) = message

      'Add borders to the new cells

      shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlDiagonalDown).LineStyle = xlNone

      shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlDiagonalUp).LineStyle = xlNone

      With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeLeft)

      .LineStyle = xlContinuous

      .ColorIndex = 0

      .TintAndShade = 0

      .Weight = xlThin

      End With

      With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeTop)

      .LineStyle = xlContinuous

      .ColorIndex = 0

      .TintAndShade = 0

      .Weight = xlThin

      End With

      With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeBottom)

      .LineStyle = xlContinuous

      .ColorIndex = 0

      .TintAndShade = 0

      .Weight = xlThin

      End With

      With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeRight)

      .LineStyle = xlContinuous

      .ColorIndex = 0

      .TintAndShade = 0

      .Weight = xlThin

      End With

      With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlInsideVertical)

      .LineStyle = xlContinuous

      .ColorIndex = 0

      .TintAndShade = 0

      .Weight = xlThin

      End With

      With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlInsideHorizontal)

      .LineStyle = xlContinuous

      .ColorIndex = 0

      .TintAndShade = 0

      .Weight = xlThin

      End With

      LogCount = LogCount + 1

      End Sub

    Click to Rate This Article