The SAGA : MS Excel, Macros, RFC & Data Fetching

This Blog is all about the users of SAP those who need some of the data to be hosted on some legacy system or some where other system.

For eg. Some company displays their employees birthday list for tommorow on company’s web page or a portal.
e.g. Employee’s In & Out Punch Data report for the top level executives. etc.

This whole requirement is explained using one simple demo RFC and with MS Excel 2007.

Exactly speaking using this you can have data exported in excel sheet and same is the method if you want it to have in your local web application or web portal.

As Excel is very old and popular tool among the end users and most of the IT programmer are well know with macros programming this is a task of say 30 mins.

1. Get the RFC created in SAP R/3 for the data you require.

 FUNCTION Y_TEST_RFC_ON_DOT_NET.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(V_ABKRS) TYPE  ABKRS OPTIONAL
*"  TABLES
*"      T_PA0002 STRUCTURE  YMHR_S0002
*"----------------------------------------------------------------------
TABLES: PA0002, PA0001.

SELECT DISTINCT
       A~PERNR
       A~VORNA
       A~NACHN
       A~GBDAT
*  UP TO 10 ROWS
 FROM  PA0002 AS A
 INNER JOIN PA0001 ON
            PA0001~PERNR = A~PERNR AND
            PA0001~ABKRS = V_ABKRS
 INTO  CORRESPONDING FIELDS OF TABLE T_PA0002
  WHERE A~ENDDA = '99991231'.

ENDFUNCTION.

2. Create one Excel file and insert the control say here it is Buttion1 as shown in below:

3. Right Click button and you will find the option called “Assign Macro” in the context menu as shown in below figure.

4. By default if you have named button as Button1 it will prompt you for Button1_Click, Clik OK as clearly depicted by below image.

5. Go to Developer tab of office 2007 and click on Visual basic button as pointed in the image below:

6. You will see the Visual basic code window as shown in figure below you have write the code shown next to the image.

'Subroutine for event click of button Getdata
Sub Button1_Click()
 Call rfc_sample
End Sub

Public Sub rfc_sample()
Dim SAPConn As Object
Dim SAPFunc As Object

' Statement creating SAP functions object to perform further operations.
Set SAPConn = CreateObject("SAP.Functions")

' Setting connection properties for the Connecion object.
With SAPConn.Connection
.ApplicationServer = "xxx.xxx.xxx.xxx" ' IP of the R/3 App server
.System = "XX" ' System ID ofthe instance,usually 0
.Client = "XXX" ' opt. Clientnumber to logon to
.Language = "EN" ' opt. Your loginlanguage
.User = "UserName" ' opt. Your userid
.Password = "Password" ' opt. Your password
End With

Dim return_table As Object

If SAPConn.Connection.Logon(0, True) <> True Then
MsgBox "Log On Failed"
Else
Set SAPFunc = SAPConn.Add("Y_TEST_RFC_ON_DOT_NET")
' Import Parameters for the RFC Function to be called.
SAPFunc.Exports("V_ABKRS") = "P5"
' Table Parameters for the RFC function to be called.
Set return_table = SAPFunc.Tables("T_PA0002")
' Calling RFC Function module for fetching data.
If SAPFunc.Call = True Then
Call DisplayData(return_table)
' If SUccess = "0" Then
' MsgBox "Data has been updated successfully", vbInformation
' Else
' MsgBox "Some error occured while updating", vbCritical
' End If
Else
MsgBox "Failed Function " + SAPFunc.Exception
End If
End If
End Sub

' Subroutine for the rendering data into excel sheet
' This program excepts one parameter of type object.
Sub DisplayData(table As Object)
Dim Rows As Integer
Dim Columns As Integer
Rows = table.RowCount
Columns = table.ColumnCount
Dim i As Integer, j As Integer

Dim CurrRow As Integer
CurrRow = 1
For i = 1 To Columns
Sheet3.Cells(CurrRow, i) = table.Columns(i).Name
Next
CurrRow = CurrRow + 1
On Error Resume Next
For j = 1 To Rows
For i = 1 To Columns
Sheet3.Cells(CurrRow, i) = table.Value(j, i)
Next
CurrRow = CurrRow + 1
Next
On Error GoTo 0
MsgBox Rows ' Displays number of rows fetched

End Sub

7. Now Testing time, Click on the button as depicted by figure below:

8. As we have coded the message box for the number of records fetched it will be displayed as show in figure below:

9. Programmed to the copy in the Sheet3 you will have to traverse to sheet 3 and check the desired output.

10. Output….

Hope you would have got the idea to impress your end users with this and also for you it might be good learning. If any queries write it to me.

4 Comments

You must be Logged on to comment or reply to a post.

Simon Kemp December 28, 2009 at 1:56 pm

Thanks for your blog. I was just wondering if the client needed to have something like the SAP GUI installed so that the SAP.* objects are available to use in VB?

like (0)
1. Former Member Post authorDecember 28, 2009 at 8:32 pm
  
  Hi Simon,
  
  Yes, On Client side it is required as the VB library get updated on installation of SAP GUI, so that objects can be used.
  
  Regards,
  Gaurav Patwari
  
  like (0)
Former Member December 30, 2009 at 7:53 pm

Hi Gauravpa,

Thank you for sharing such a kind of information on this blog….
Just a little curious to know about what all could be the areas or scope of RFC’s with other technologies & their interoperability.
If you could throw some light on this context.

like (0)
1. Former Member Post authorDecember 30, 2009 at 8:59 pm
  
  Hi Seema,
  
  The interoperability with other technologies such Java, .Net and other can be possible the same way. Java you need to have the JCO Library and same way the library on .Net gets updated on SAP GUI Installation at the .Net client side. Its most convenient option that way.
  
  Regards,
  Gaurav Patwari
  
  like (0)