Tutorial on using VBA in Excel to Connect to SAP RFC - TurboFuture - Technology
Updated date:

Tutorial on using VBA in Excel to Connect to SAP RFC

silent-connection-with-saplogon-with-rfcsdk-example-rfc-excel-vba

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.

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Comments

Kevin Languedoc (author) from Canada on July 07, 2020:

Hi

I no longer have access to a SAP system. Sorry.

Peter on July 07, 2020:

Great post, can get this working on non SNC connection, Would like to see how this is done on SNC

Jonas on December 01, 2017:

In this point, '429' error:

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

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

Could you help me?

singer0000 on April 10, 2017:

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

Kevin Languedoc (author) from Canada on January 31, 2017:

Hi Madhu

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

Kevin Languedoc (author) from Canada on January 31, 2017:

Hi Setu,

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

Setu on December 19, 2016:

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

Madhu on November 30, 2016:

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

Ravindra on June 26, 2014:

It helped me today

Kevin Languedoc (author) from Canada on April 20, 2014:

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

Marco Rossi on March 13, 2014:

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

Kevin Languedoc (author) from Canada on November 12, 2013:

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

Kevin Languedoc (author) from Canada on November 12, 2013:

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

AmjadM from Karachi on October 12, 2013:

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.

Timo van Esch on September 27, 2013:

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

Kevin Languedoc (author) from Canada on June 14, 2013:

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.

AmjadM from Karachi on May 24, 2013:

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)

donizeth on May 22, 2013:

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

Apichart on May 22, 2013:

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

Thank you.

Apichart on May 22, 2013:

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 ?

Kevin Languedoc (author) from Canada on April 11, 2013:

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

AmjadM,

nice to hear from you

AmjadM from Karachi on April 05, 2013:

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

USAMax on April 01, 2013:

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

Kevin Languedoc (author) from Canada on March 14, 2013:

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

AmjadM from Karachi on March 11, 2013:

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.

AmjadM from Karachi on March 06, 2013:

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

AmjadM from Karachi on March 06, 2013:

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

Kevin Languedoc (author) from Canada on March 05, 2013:

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

AmjadM from Karachi on March 05, 2013:

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

AmjadM from Karachi on March 05, 2013:

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.

Kevin Languedoc (author) from Canada on March 04, 2013:

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

AmjadM from Karachi on February 27, 2013:

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.

Kevin Languedoc (author) from Canada on February 27, 2013:

amjadM,

Not sure I understand your question...

AmjadM from Karachi on February 26, 2013:

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

Kevin Languedoc (author) from Canada on January 08, 2013:

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 from Karachi on January 07, 2013:

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?

AmjadM from Karachi on January 07, 2013:

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?

Kevin Languedoc (author) from Canada on January 05, 2013:

Great.

AmjadM from Karachi on January 04, 2013:

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

Cheers.

Kevin Languedoc (author) from Canada on January 04, 2013:

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 from Karachi on January 04, 2013:

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.

AmjadM from Karachi on January 03, 2013:

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.

Kevin Languedoc (author) from Canada on January 03, 2013:

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 from Karachi on January 03, 2013:

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

AmjadM from Karachi on January 02, 2013:

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

Kevin Languedoc (author) from Canada on December 29, 2012:

yes exactly

AmjadM from Karachi on December 24, 2012:

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

AmjadM from Karachi on December 20, 2012:

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.

Kevin Languedoc (author) from Canada on December 20, 2012:

AmjadM

Here is the code for the RFC

https://klanguedoc.hubpages.com/hub/Create-SAP-Fun...

AmjadM from Karachi on December 11, 2012:

Thank you, I willl be obliged.

Kevin Languedoc (author) from Canada on December 11, 2012:

Ok I am write it for you

AmjadM from Karachi on December 09, 2012:

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.

Kevin Languedoc (author) from Canada on December 08, 2012:

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

AmjadM from Karachi on December 04, 2012:

Klanguedoc,

I want value of

Table KNVV

Field KVGR1

For a given customer ID

Is this possible through bapi_salesorder_getstatus?

Kevin Languedoc (author) from Canada on December 04, 2012:

For the SalesOrder you can use the bapi_salesorder_getstatus.

AmjadM from Karachi on December 04, 2012:

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

Kevin Languedoc (author) from Canada on November 22, 2012:

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 from Karachi on November 21, 2012:

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

Kevin Languedoc (author) from Canada on November 15, 2012:

ok, thanks

AmjadM from Karachi on October 21, 2012:

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

Kevin Languedoc (author) from Canada on October 21, 2012:

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 from Karachi on October 21, 2012:

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"

Kevin Languedoc (author) from Canada on October 21, 2012:

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 from Karachi on October 19, 2012:

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.

Kevin Languedoc (author) from Canada on October 12, 2012:

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 from Karachi on October 12, 2012:

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.

AmjadM from Karachi on October 08, 2012:

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

Kevin Languedoc (author) from Canada on October 07, 2012:

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 from Karachi on October 04, 2012:

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)

Kevin Languedoc (author) from Canada on June 24, 2012:

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

AmjadM from Karachi on June 23, 2012:

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.