Currently Being Moderated

SAP Connectivity with MS Excel

Data from SAP to MS Office Applications

1.jpg

         

Using the connection SAP with MS Office, users spreads data from SAP application to right within Microsoft Office desktop applications, including Excel, Outlook, Word and Power point.

With the SAP-MS Office connectivity, end-users can interact with SAP transactions directly within their Excel spreadsheet, their Outlook e-mail screen, and their Word documents and on their Power point presentations.

Here we have a simple application to connect SAP with MS-office tool’s Excel.
But before that……Why? How? And Use!

 

Why Data from SAP to MS Office Applications

2.jpg

In spite of large moneys in SAP, most business users continue to be unsatisfied with it because data from SAP is not easily accessible in their favorite desktop productivity tools -- Microsoft Excel, Microsoft Outlook and Microsoft Word. Since Microsoft Office Applications are not connected to SAP, users are forced to

  • Use error-prone cut-copy-paste to populate their Excel spreadsheets.
  • Switch screens from Outlook to gather data and take decisions as they respond to e-mails.
  • Manually update data from Excel Spreadsheets to enterprise applications.


  Benefits of Data from SAP to Microsoft Office Applications
SAP-MS Office Connectivity extends data from SAP to Microsoft Excel, Microsoft Outlook and Microsoft Word using a SOA based    Information Delivery Server and Microsoft Office Add-ins, and provides the following benefits
 

  • Eliminates cut-and-paste based data collecting mechanism from SAP to Microsoft Excel.
  • Enables users to get real-time data from SAP within Excel with a single-click Refresh.
  • Enables users to query and update SAP transactions from within Outlook screens.
  • Allows the users to update SAP from within Excel, Outlook and Word.
  • Avoids any data-integrity issues with copying and reduces compliance issues.


  Who Benefits  

  • Production and Logistics Managers who want to create Excel reports from latest SAP data.
  • Managers who want to track projects and status automatically as they receive status e-mails.
  • Analysts who want to combine data from SAP and other enterprise data stores for budgeting purposes.

 

Example:

3.jpg

Here is the main part comes up. We shall take simple scenario for SAP with MS Excel connectivity.  So let’s begin!!

Just before start, I would like to give little overview on what we are actually going to do. So, here are the scenarios!!

Excel File Scenarios

  • Create a MS excel file for user input.
  • Create user interface in excel sheet to input data for the customer master and output cells.
  • Apply VB code to make connection with SAP.
  • Read the input data for customer master from the excel file sheet.
  • Fetch data from SAP using connection code from SAP.
  • Display output within excel sheet itself.

SAP Scenarios

  • Create a function module in ABAP that is “Remote Enabled”.
  • Create IMPORT, EXPORT and TABLES parameters as per requirement from MS excel.
  • Write logic to display customer master details based on input from Excel file.
  • Activate the function module.

Well……………“A picture says thousand words”. So, let’s begin!

 
 

MS Excel – User Interface

  First of all, lets create MS excel file with customer master input. We shall create simple selection screen for user input.

4.jpg

Well……filling cell values are simple here but how to add buttons (Get Address and Reset Output) here? This was also new in my case since new version of MS office.


Just follow following steps for this:


Click MS office button and select “Excel Options” button from the menu.

 

5.jpg

You will see here a new tab named “Developer”

 

6.jpg

 

Click on “Insert” and then select “Button” from the form control. Later on you can double click on the button and set its properties (Caption, Color, etc.)

Congratulations! Our input screen is finished here. Let’s design our output screen now.

 

7.jpg

I have created my output screen just after the input screen. You can create anywhere in the excel file. Here terms ‘anywhere’ descries to other worksheet also.

The output screen is simple as you see. Nothing much to do here. I just simple colored the cells for better look.

Great! Looks like we finished user interface here. (Did we miss something...’Naah’ for now!)

 

 

SAP – Select Data

Since we are taking simple example to fetch detail from customer master, let’s create a function module for it.

 

 

Create a function module.

8.jpg

Enter function group and Short text.

 

9.jpg

Make sure your function module is “Remote Enabled”.

 

10.jpg


Now let’s create “Tables” parameters:

 

11.jpg



Here we have two “Tables” parameters

  • ET_KUNNR (Input) : Customer numbers pass in to the function module
  • ET_CUST_LIST (Output): Data will be fetched in function module and display on the excel sheet.
    • Note: We have created a Z structure (ZNM_CUST_LIST) for the output list. Here are the fields for the structure:

12.jpg


We are all set with input and output structures.

Now, let’s fetch the data. Here is the only coding part came up in SAP. Look at below screen.

 

13.jpg


Here, we are selecting all the customer details from the KNA1 (customer master) table from the input table (ET_KUNNR) from excel file and return to the table (ET_CUST_LIST).

Now we have all the records in the table ET_CUST_LIST.

So, again congratulation friends! You have completed most of the things. The remaining step now is to display the list in the excel file.

 

 

Let’s now connect the SAP and MS Excel.

Go to the ‘Developer’ tab and click on “Visual Basic” icon.

 

14.jpg


The VB editor will be opened and we are going to write the code for connection of excel and SAP with sending and receiving data.

Here is the variable declaration list.  These are the global variables. Let’s understand each of them.

 

15.jpg

 

 

 

Variable

Description

objBAPIControl

For creating object to access SAP functions.

objgetaddress

To make BAPI function call via objBAPIControl.

 

vLastRow

Last row of output list

vRows

Total number of records returned from the SAP

 

vcount_add

Variable for increment records

Index_add

Variable for next record (Index)

 

objaddress

Object for SAP table (for customer master output)

objkunnr

Object for SAP table (for customer master input)

Here are the properties for the “Get Address” button.

 

16.jpg

 

Let’s code when “Get Address” button is clicked.

Setup the local variables

 

17.jpg

 


Variable

Description

LogonControl

Logon control is to make login in SAP.

R3Connection

Ro make connection to SAP R/3 using logon control.

retcd

Return Code

SilentLogon

If ‘True’ no popup will ask to enter SAP login details

 

Now let’s setup the connection with SAP R/3 using following code.

18.jpg

 

 

Let’s do SAP Login here.

 

19.jpg

 

Perfect!! We have made the connection with SAP R/3. Now we are able to send and receive the data from MS Excel to SAP R/3 and vice versa.

But how to send my customer details and receive? Hmmm…..we need to use internal tables those are created in SAP function module. (Do you remember?....NO?... checkout Tables parameters in  function ZNM_GET_CUSTOMER_DETAILS). So, let’s do this.

20.jpg

 

Here, ZNM_GET_CUSTOMER_DETAILS is our function module created in SAP.

ET_KUNNR: Customer details input details

ET_CUST_LIST: Customer output details.

21.jpg

We are reading here each cell from excel worksheet for input.

22.jpg

 

Here we have called the FM and passed the input details to process.

Result:

23.jpg

 

vcount_add returns total number of records from the SAP. And we have already set the loop to display records in the cells.

Here, R3Connection.Logoff is to sign off from your SAP account.

Here is the output screen:

 

24.jpg

 

 

Wow!! We have output result. Good job!! But what is the use of another button “Reset Output” here? Yes…good question.

The “Reset Output” button will clear all the data and messages from the screen. Look at below code:

 

25.jpg

 

 

-----------------------------------------------------------------------------------------------------------------------

Now we have completed the entire example here. N’ joy.

 

Here is the entire code:

Option Explicit

Dim objBAPIControl, objgetaddress As Object

Dim vLastRow, vRows As Integer

Dim vcount_add, index_add As Integer

Public objaddress, objkunnr As SAPTableFactoryCtrl.Table

 

Private Sub GetAddress_Click()

'Local variables

  Dim LogonControl As SAPLogonCtrl.SAPLogonControl

  Dim R3Connection As SAPLogonCtrl.Connection

  Dim retcd        As Boolean

  Dim SilentLogon  As Boolean

 

'Set Connection

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

  Set objBAPIControl = CreateObject("SAP.Functions")

  Set R3Connection = LogonControl.NewConnection

 

'SAP connection

  R3Connection.Client = "810"

  R3Connection.ApplicationServer = "10.226.33.14"

  R3Connection.Language = "EN"

  R3Connection.User = "238499"

  R3Connection.Password = "xxx"

  R3Connection.System = "EC6"

  R3Connection.SystemNumber = "00"

  R3Connection.UseSAPLogonIni = False

  SilentLogon = False

 

  retcd = R3Connection.Logon(0, SilentLogon)

  If retcd <> True Then MsgBox "Logon failed": Exit Sub

  objBAPIControl.Connection = R3Connection

 

'Assign the Parameters

  Set objgetaddress = objBAPIControl.Add("ZNM_GET_CUSTOMER_DETAILS")

  Set objkunnr = objgetaddress.Tables("ET_KUNNR")

  Set objaddress = objgetaddress.Tables("ET_CUST_LIST")

 

'Assign customer

  If ThisWorkbook.ActiveSheet.Cells(6, "B").Value <> "" Then

    objkunnr.Rows.Add

    objkunnr.Value(1, "SIGN") = ThisWorkbook.ActiveSheet.Cells(6, 2).Value

    objkunnr.Value(1, "OPTION") = ThisWorkbook.ActiveSheet.Cells(6, 3).Value

    objkunnr.Value(1, "LOW") = ThisWorkbook.ActiveSheet.Cells(6, 4).Value

    objkunnr.Value(1, "HIGH") = ThisWorkbook.ActiveSheet.Cells(6, 5).Value

  End If

 

  objgetaddress.call

 

  vcount_add = objaddress.Rows.Count

    For index_add = 1 To vcount_add

      vRows = 11 + index_add

      ActiveSheet.Cells(vRows, 2) = objaddress.Value(index_add, "KUNNR")

      ActiveSheet.Cells(vRows, 3) = objaddress.Value(index_add, "LAND1")

      ActiveSheet.Cells(vRows, 4) = objaddress.Value(index_add, "NAME1")

      ActiveSheet.Cells(vRows, 5) = objaddress.Value(index_add, "ORT01")

      ActiveSheet.Cells(vRows, 6) = objaddress.Value(index_add, "PSTLZ")

      ActiveSheet.Cells(vRows, 7) = objaddress.Value(index_add, "REGIO")

      ActiveSheet.Cells(vRows, 8) = objaddress.Value(index_add, "KTOKD")

      ActiveSheet.Cells(vRows, 9) = objaddress.Value(index_add, "TELF1")

      ActiveSheet.Cells(vRows, 10) = objaddress.Value(index_add, "TELFX")

    Next index_add

  'If address not exist then show error

  If vcount_add = "" Then

    ActiveSheet.Cells(10, 11) = "Invalid Input"

  Else

    ActiveSheet.Cells(10, 12) = "BAPI Call is successfull"

    ActiveSheet.Cells(11, 12) = vcount_add & " rows are returned"

  End If

 

  R3Connection.Logoff

End Sub

 

Private Sub ResetOutput_Click()

vLastRow = Cells(Rows.Count, "B").End(xlUp).Row

For vRows = 12 To vLastRow

 

ThisWorkbook.ActiveSheet.Cells(vRows, 2).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 3).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 4).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 5).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 6).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 7).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 8).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 9).Value = ""

ThisWorkbook.ActiveSheet.Cells(vRows, 10).Value = ""

Next vRows

ThisWorkbook.ActiveSheet.Cells(10, 12).Value = ""

ThisWorkbook.ActiveSheet.Cells(11, 12).Value = ""

End Sub

 

(Note: Button properties need to be set)

Comments

Delete Document

Are you sure you want to delete this document?