cancel
Showing results for 
Search instead for 
Did you mean: 

Integração SAP e Excel (VBA)

Former Member
0 Kudos

Boa tarde.

Estou com um problema, utilizo o SAP como controle principal e utilizo o Excel como controle secundário, apenas interno. Dependo de uma ordem para extrair os outros valores, utilizando a transação IW38.

O trabalho atualmente é feito buscando a ordem no SAP pelo IW38 e copiando e colando os outros atributos necessários nessa planilha de controle que possuo no Excel , o que torna a atividade muito trabalhosa. Minha ideia é ao entrar com o valor da ordem na planilha do Excel e automaticamente ela me trazer os outros valores que necessito.

Seria apenas a título de controle, sem modificar nada.

Possuo esse código, não sei o que falta implementar.

Private Sub GetUserList()

'

'Declaration

'

Dim Destination_System As Integer

Dim objBAPIControl As Object 'Function Control (Collective object)

Dim sapConnection As Object 'Connection object

Set objBAPIControl = CreateObject("SAP.Functions")

Set sapConnection = objBAPIControl.Connection

'

'Logon with initial values

'ActiveSheet is the sheet calling the Macro / Cells(x, y) refers to the row, column in the spreadsheet

'

Destination_System = ActiveSheet.Cells(11, 2).Value + 2 'Add 2 to retrieve the column that contains the definiton

sapConnection.client = ActiveSheet.Cells(3, Destination_System).Value

sapConnection.user = ActiveSheet.Cells(4, Destination_System).Value

sapConnection.Language = ActiveSheet.Cells(7, Destination_System).Value

sapConnection.hostname = ActiveSheet.Cells(6, Destination_System).Value

sapConnection.Password = ActiveSheet.Cells(5, Destination_System).Value

sapConnection.SystemNumber = ActiveSheet.Cells(9, Destination_System).Value

sapConnection.System = ActiveSheet.Cells(8, Destination_System).Value

sapConnection.Destination = ActiveSheet.Cells(8, Destination_System).Value

If sapConnection.logon(1, True) <> True Then

MsgBox "No connection to R/3!"

Exit Sub 'End program

End If

Set objUserList = objBAPIControl.Add("BAPI_USER_GETLIST")

Set objUserDetail = objBAPIControl.Add("BAPI_USER_GET_DETAIL")

'Prepare output to the EXCEL worksheet

'

Worksheets(2).Select

Cells.Clear

Range("A1").Font.Italic = True

Range("A2:E2").Font.Bold = True

ActiveSheet.Cells(2, 1) = "User Name"

ActiveSheet.Cells(2, 2) = "Wrong Logon"

ActiveSheet.Cells(2, 3) = "Local Lock"

ActiveSheet.Cells(2, 4) = "Global Lock"

ActiveSheet.Cells(2, 5) = "No UserPwd"

'Define the import parameters for BAPI_USER_GETLIST

'

objUserList.exports("MAX_ROWS") = "99999"

objUserList.exports("WITH_USERNAME") = ""

'call the 1st BAPI to retrieve the list of users: BAPI_USER_GETLIST

'

returnFunc = objUserList.Call

If returnFunc = True Then

Dim objTable As Object

Set objTable = objUserList.Tables("USERLIST")

ActiveSheet.Cells(1, 1) = "User count :" & objTable.RowCount

For i = 1 To objTable.RowCount

'Alternate row colors

'

If i Mod 2 = 0 Then

For j = 1 To 5

ActiveSheet.Cells(2 + i, j).Interior.Color = RGB(165, 162, 165)

Next j

Else

For j = 1 To 5

ActiveSheet.Cells(2 + i, j).Interior.Color = RGB(214, 211, 206)

Next j

End If

ActiveSheet.Cells(2 + i, 1) = objTable.Cell(i, 1)

'Define the import parameters for BAPI_USER_GET_DETAIL

'

objUserDetail.exports("USERNAME") = objTable.Cell(i, 1)

'Cal the 2nd BAPI to retrieve the details for each user

'

returnFunc = objUserDetail.Call

If returnFunc = True Then

Dim isLocked As Object

Set isLocked = objUserDetail.imports("ISLOCKED")

If isLocked.Value("WRNG_LOGON") = "L" Then

ActiveSheet.Cells(2 + i, 2).Font.Color = RGB(255, 0, 0)

ActiveSheet.Cells(2 + i, 2) = "X"

End If

If isLocked.Value("LOCAL_LOCK") = "L" Then

ActiveSheet.Cells(2 + i, 3).Font.Color = RGB(255, 0, 0)

ActiveSheet.Cells(2 + i, 3) = "X"

End If

If isLocked.Value("GLOB_LOCK") = "L" Then

ActiveSheet.Cells(2 + i, 4).Font.Color = RGB(255, 0, 0)

ActiveSheet.Cells(2 + i, 4) = "X"

End If

If isLocked.Value("NO_USER_PW") = "L" Then

ActiveSheet.Cells(2 + i, 5).Font.Color = RGB(255, 0, 0)

ActiveSheet.Cells(2 + i, 5) = "X"

End If

Else

MsgBox "Error when accessing BAPI_USER_GET_DETAIL in R/3 ! "

Exit Sub

End If

Next i

Else

MsgBox "Error when accessing BAPI_USER_GETLIST in R/3 ! "

Exit Sub

End If

'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

Atenciosamente;

Igor de Lima.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Igor, Boa  tarde. Da uma olha neste artigo talvez te ajude.

http://scn.sap.com/docs/DOC-70006

Um Abraço

Artur