report

Silent Connection with SAPLOGON with RFC SDK Example

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.

More by this Author


Comments 64 comments

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

klanguedoc 4 years ago from Canada Author

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

klanguedoc 4 years ago from Canada Author

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

klanguedoc 4 years ago from Canada Author

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

klanguedoc 4 years ago from Canada Author

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

klanguedoc 4 years ago from Canada Author

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

klanguedoc 4 years ago from Canada Author

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

klanguedoc 4 years ago from Canada Author

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 3 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

klanguedoc 3 years ago from Canada Author

For the SalesOrder you can use the bapi_salesorder_getstatus.


AmjadM profile image

AmjadM 3 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

klanguedoc 3 years ago from Canada Author

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


AmjadM profile image

AmjadM 3 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

klanguedoc 3 years ago from Canada Author

Ok I am write it for you


AmjadM profile image

AmjadM 3 years ago from Karachi

Thank you, I willl be obliged.


klanguedoc profile image

klanguedoc 3 years ago from Canada Author


AmjadM profile image

AmjadM 3 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 3 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

klanguedoc 3 years ago from Canada Author

yes exactly


AmjadM profile image

AmjadM 3 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 3 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

klanguedoc 3 years ago from Canada Author

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 3 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 3 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

klanguedoc 3 years ago from Canada Author

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 3 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

klanguedoc 3 years ago from Canada Author

Great.


AmjadM profile image

AmjadM 3 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 3 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

klanguedoc 3 years ago from Canada Author

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 3 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

klanguedoc 3 years ago from Canada Author

amjadM,

Not sure I understand your question...


AmjadM profile image

AmjadM 3 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

klanguedoc 3 years ago from Canada Author

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


AmjadM profile image

AmjadM 3 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 3 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

klanguedoc 3 years ago from Canada Author

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 3 years ago from Karachi

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


AmjadM profile image

AmjadM 3 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 3 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

klanguedoc 3 years ago from Canada Author

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


USAMax 3 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 3 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

klanguedoc 3 years ago from Canada Author

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

AmjadM,

nice to hear from you


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 ?


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

klanguedoc 3 years ago from Canada Author

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.


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

klanguedoc 3 years ago from Canada Author

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

klanguedoc 3 years ago from Canada Author

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


Marco Rossi 2 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

klanguedoc 2 years ago from Canada Author

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))


Ravindra 2 years ago

It helped me today


Madhu 42 hours 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

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article

    Menu

    Explore