Skip to Content
author's profile photo Former Member
Former Member

Integração SAP e Excel (VBA)

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.

Sem título.jpg (184.3 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Aug 15, 2016 at 06:12 PM

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

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

    Um Abraço

    Artur

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.