[COLSEN]

Sub GetCLientes()

'Declaration

Dim objBAPIControl As Object 'Function Control (Collective object)
Dim sapConnection As Object 'Connection object
Dim Total

' Assign.
Set objBAPIControl = CreateObject("SAP.Functions")
Set sapConnection = objBAPIControl.Connection

'Logon with initial values Credenciales de conexcion

sapConnection.Client = "111"
sapConnection.User = "DESADM"
sapConnection.Password = "xxxxx"
sapConnection.SystemNumber = 0
sapConnection.System = "DES"
sapConnection.HostName = "serverxxx"
sapConnection.Language = "ES"

If sapConnection.logon(1, True) <> True Then
MsgBox "No connection to R/3!"
Exit Sub
End If

'Prepare output to the EXCEL worksheet

Worksheets(2).Select
Cells.Clear

Set objTableSAP = objBAPIControl.Add("ZCLIENTES")

'call the 1st BAPI to retrieve the list of clients'
returnFunc = objTableSAP.Call

If returnFunc = True Then
Dim objTable As Object
Set objTable = objTableSAP.Tables("ZCLIENTES")
Total = objTable.RowCount

ActiveSheet.Cells(1, 1) = "Cantidad :" & Total

For i = 1 To Total

ActiveSheet.Cells(2 + i, 1) = objTable.Cell(i, 1)
ActiveSheet.Cells(2 + i, 2) = objTable.Cell(i, 2)
ActiveSheet.Cells(2 + i, 3) = objTable.Cell(i, 3)
ActiveSheet.Cells(2 + i, 4) = objTable.Cell(i, 4)
ActiveSheet.Cells(2 + i, 5) = objTable.Cell(i, 5)
ActiveSheet.Cells(2 + i, 6) = objTable.Cell(i, 6)
ActiveSheet.Cells(2 + i, 7) = objTable.Cell(i, 7)

Next i

End If

Columns("A:Z").EntireColumn.AutoFit

'Close connection to R/3 !
'
objBAPIControl.Connection.logoff
'
'Release the objects to free storage space
'
Set sapConnection = Nothing
Set functionCtrl = Nothing
MsgBox "Program terminated!", 0, "Exit"
End Sub