Se gostou deste conteúdo, deixe sua colaboração!

6 de jan de 2014

Como interfacear o Excel e o SAP - Excel and SAP interface




Tenho usado muitas maneiras diferentes de trabalho para conectar informações no SAP: MS-Query, Tabelas Dinâmicas, Funções VBA usando ADO, ... E todos esses métodos trazem algum resultado.

Exporto tudo que preciso do SAP para um banco de dados SQL Server (ou mesmo MS Access), e uso um código adaptado para esconder os nossos segredos, adaptando o código por fora no MS Excel, por isso é possível que hajam alguns erros de digitação.

Option Explicit
Global MY_RFC_TABLE_01 As Object      'Tabela SAP, tal como definido no RFC
Global MY_RFC_TABLE_02 As Object      'Tabela SAP, tal como definido no RFC

' A função RFC que uso é definida assim: 
' As parameters I give a material-code number for a bill-of-materials (structured) to SAP
' Then the RFC fills MY_RFC_TABLE_01 with the structured BOM, one line per material
' Or I give as parameter a list of material-code numbers to SAP
' Then the RFC fills MY_RFC_TABLE_02 with the material info, one line per material

Public Sub ConnectSap (SAP As Object)
    Dim ConnString As String
    Dim ConnectSap As String

    Set SAP = CreateObject("SAP.Functions")

    SAP.Connection.System = "ZZZ"     'SAP Server ID
    SAP.Connection.SystemNumber = 0     'SAP System Number
    SAP.Connection.Messageserver = "SAPCLUSTER"  'SAP Message Server Name (for loadbalancing SAP-clusters)
    SAP.Connection.GroupName = "SAPGROUP"   'SAP Group Name    (for loadbalancing SAP-clusters)
    SAP.Connection.client = "400"     'SAP Client ID
    SAP.Connection.user = "USERNAME"    'SAP Username
    SAP.Connection.Password = "PASSWORD"   'SAP Password
    SAP.Connection.language = "EN"     'SAP Language

    Let ConnString = SAP.Connection.logon (0, True)

    If ConnString <> True Then
        Let ConnectSap = "Connection failed!"
    Else
        Let ConnectSap = "Connection successful."
    End If

    If ConnectSap = "Connection failed!" Then
        Call MsgBox(ConnectSap, vbOKOnly, "SAP Connection")
        End
    End If
End Sub

Public Sub DisconnectSap (SAP As Object)
    SAP.Connection.LOGOFF
    'Call MsgBox("Connection closed.", vbOKOnly, "SAP Connection")
End Sub

Public Sub FillFlatList (ByRef pFlatList As Worksheet, _
                                               Optional ByVal RowNumber As Long = 1, _
                                               Optional ByVal ColumnNumber As Long = 1)
 'This Sub fills the SAP-table with the necessary parameter values before calling the RFC
    Dim TABLEROW As Long
    TABLEROW = 1
    While pFlatList.Cells(RowNumber, ColumnNumber).Value <> "" And pFlatList.Cells(RowNumber, ColumnNumber).Value <> "END1"
        MY_RFC_TABLE_02.Rows.Add  'adds a new row in the SAP-table
        MY_RFC_TABLE_02.Value(TABLEROW, "MATNR") = pFlatList.Cells(RowNumber, ColumnNumber).Value  'assigns value to the materialnumber parameter
        TABLEROW = TABLEROW + 1   'increments SAP-table rowcounter
        RowNumber = RowNumber + 1  'increments Excel-sheet rowcounter
    Wend
End Sub

Public Sub GetSapFlatList (ByRef TargetSheet As Worksheet, 
                                          Optional ByVal StartRow As Long = 1, _
                                          Optional ByVal StartColumn As Long = 1)
    Dim SAP As Object           'SAP Connection object
    Dim RFC As Object           'RFC Function object
    Dim ROW As Object           'ROW object in SAP-table
    Dim Result As Boolean

    'Open the connection to SAP
    Call ConnectSap(SAP)
    Set RFC = SAP.Add("MY_RFC_FUNCTION")
    Set MY_RFC_TABLE_02 = RFC.tables("MY_RFC_TABLE_02")

    'Call fill_selection_table for parameters for flat list
    Call FillFlatList(TargetSheet, StartRow, StartColumn)
    Let Result = RFC.Call  'after this line, SAP filled the MY_RFC_TABLE_02 table with the results

    If Result = False Then
        MsgBox RFC.EXCEPTION
        Call DisconnectSap(SAP)
        Exit Sub
    End If

    'Close the SAP connection
    Call DisconnectSap(SAP)

    'Extract filled SAP tables to Excel worksheet
    For Each ROW In MY_RFC_TABLE_02.Rows
        With TargetSheet
            .Cells(StartRow, StartColumn + 0).Value = ROW("MATNR")           'Material Number as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 1).Value = ROW("DATAFIELD1")   'Material Datafield 1 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 2).Value = ROW("DATAFIELD2")   'Material Datafield 2 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 3).Value = ROW("DATAFIELD3")   'Material Datafield 3 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 4).Value = ROW("DATAFIELD4")   'Material Datafield 4 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 5).Value = ROW("DATAFIELD5")    'Material Datafield 5 as defined in SAP-RFC
        End With

        Let StartRow = StartRow + 1
    Next
End Sub

Public Sub GetSapStructBom (ByVal Topcode As Variant, _
                                                              ByRef TargetSheet As Worksheet, _
                                                              Optional ByVal StartRow As Long = 1, _
                                                              Optional ByVal StartColumn As Long = 1)
    Dim SAP As Object                  'SAP Connection object
    Dim RFC As Object                  'RFC Function object
    Dim RFCPARAM As Object      'RFC Function parameter (Sapcode)
    Dim ROW As Object                'ROW object in SAP-table
    Dim Result As Boolean            'Result of the Remote Function Call

    'Open the SAP connection
    Call ConnectSap(SAP)
    Set RFC = SAP.Add("MY_RFC_FUNCTION")
    Set MY_RFC_TABLE_01 = RFC.tables("MY_RFC_TABLE_01")

    'Setting the input parameter for the RFC
    Set RFCPARAM = RFC.exports("MATNR")   
    Let RFCPARAM.Value = Topcode     'sets the parameter for SAP

    'Call the Remote Function
    Let Result = RFC.Call

    'Check for Call result
    If Result = False Then
        MsgBox RFC.EXCEPTION
        Call DisconnectSap(SAP)

        Exit Sub
    End If

    'Close the SAP connection
    Call DisconnectSap(SAP)
    'Fill sheet with SAP data

    For Each ROW In MY_RFC_TABLE_01.Rows
        With TargetSheet
            .Cells(StartRow, StartColumn + 0).Value = ROW("MATNR")         'Material Number as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 1).Value = ROW("DATAFIELD1") 'Material Datafield 1 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 2).Value = ROW("DATAFIELD2") 'Material Datafield 2 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 3).Value = ROW("DATAFIELD3") 'Material Datafield 3 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 4).Value = ROW("DATAFIELD4") 'Material Datafield 4 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 5).Value = ROW("DATAFIELD5") 'Material Datafield 5 as defined in SAP-RFC
        End With

        Let StartRow = StartRow + 1
    Next
End Sub

'Examples of function calls to retrieve SAP-info. These functions handle the connection to SAP
'themselves, via the provided functions ConnectSap and DisconnectSap. You can also do this yourself
'by calling them directly, but it is easier to let the first function handle it, that way you
'can transparantly call the function without worrying about the SAP-connection.

Public Sub Test()
 'uses ActiveSheet.Cells(2, 3).Value as parameter for the RFC and
 'returns the results to the Activesheet, starting on row 6, column 1
 Call GetSapStructBom (ActiveSheet.Cells(2, 3).Value, ActiveSheet, 6, 1)

 'uses the values in the Activesheet as parameters (more precisely, the values in
 'column 1, starting at row 6) and returns the results to the same location
 Call  (ActiveSheet, 6, 1)
End Sub

Não sei se é bom mencionar nesta discussão, mas caso você não possa usar o TCODE "SQVI", a única exigência será saber a tabela na qual a informação está, o que é bastante fácil de encontrar. Não existe uma verdadeira razão pela qual você não possa ter acesso a SQVI





Tags: Como, Excel, SAP, transação, TCode, SQVI, XK99, GetSapStructBom, ConnectSap, DisconnectSa, FillFlatList, GetSapFlatList
Related Posts Plugin for WordPress, Blogger...
diHITT - Notícias