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

 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.

'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