on 08-15-2016 5:44 PM
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.
Igor, Boa tarde. Da uma olha neste artigo talvez te ajude.
http://scn.sap.com/docs/DOC-70006
Um Abraço
Artur
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.