上一篇講的是調(diào)用RFC_READ_TABLE查看SAP table的數(shù)據(jù)根暑。為了方便查看數(shù)據(jù)，我們可以寫一個通用的表查看程序每强。使用起來比SAP SE11或SE16N方便點稿静。

本篇沒有關(guān)于RFC調(diào)用新的知識點秉沼。主要說明函數(shù)調(diào)用后桶雀，VBA如何處理這些數(shù)據(jù)并在Excel中顯示。不熟悉VBA的讀者可以參考唬复。由于VBA本身數(shù)據(jù)結(jié)構(gòu)的限制矗积，處理過程還是蠻啰嗦的。后續(xù)用C#調(diào)用的代碼會方便很多盅抚。

不多說漠魏，上代碼：

Option Explicit

Public Sub test()
    Call Logon
    Call ReadTable("T030", Sheet1)
    Call Logoff
End Sub

'--------------------------------------'
'讀取tableName的數(shù)據(jù)，寫入inSheet這個工作表
'--------------------------------------'
Private Sub ReadTable(tableName As String, inSheet As Worksheet)
    Dim functions As SAPFunctions
    Set functions = New SAPFunctions
    Dim fm As SAPFunctionsOCX.Function

    ' RFC_READ_TABLE的三個table型參數(shù)'
    Dim optionsTable As SAPTableFactoryCtrl.Table
    Dim dataTable As SAPTableFactoryCtrl.Table
    Dim fieldsTable As SAPTableFactoryCtrl.Table

    Dim delimeter As String
    delimeter = "~" '長度只能為1'

    If sapConnection Is Nothing Then Exit Sub
    Set functions.Connection = sapConnection

    If sapConnection.IsConnected = tloRfcConnected Then
        'FM加到functions collection'
        Set fm = functions.Add("RFC_READ_TABLE")

        '------------------------'
        '填充Import parameters
        '------------------------'
        'QUERY_TABLE是要查找的表名
        fm.Exports("QUERY_TABLE").Value = tableName 'Table name

        'DELIMITER是輸出時字段的分割符'
        fm.Exports("DELIMITER").Value = delimeter

        Set optionsTable = fm.Tables("OPTIONS")  'OPTIONS是篩選條件'
        Set fieldsTable = fm.Tables("FIELDS")    'FIELDS表示要輸出的列'
        Set dataTable = fm.Tables("DATA")        'DATA為輸出的數(shù)據(jù)'

        fm.Call

        '如果有Exception,說明有錯誤產(chǎn)生'
        If fm.Exception <> "" Then
            Debug.Print fm.Exception
            Exit Sub
        End If

        ' 存儲fields信息的數(shù)組'
        Dim fields() As Variant
        fields = ItabToArray(fieldsTable)

        ' 存儲data信息的數(shù)組'
        Dim data() As Variant
        data = ItabToArray(dataTable)

        ' 將data分割'
        Dim splittedData() As Variant
        splittedData = splitData(data, delimeter)

        ' 為了Excel顯示需要妄均，將數(shù)據(jù)加上單引號, Excel顯示為字符型'
        Dim r As Long
        Dim c As Long
        For r = 1 To UBound(splittedData, 1)
            For c = 1 To UBound(splittedData, 2)
                splittedData(r, c) = "'" + splittedData(r, c)
            Next
        Next

        ' 將field name, field text和data輸出到工作表顯示 '
        Call WriteData(fields, splittedData, Sheet1)
    End If
End Sub


' 將itab轉(zhuǎn)換成數(shù)組'
Private Function ItabToArray(itab As SAPTableFactoryCtrl.Table) As Variant
    Dim arr() As Variant
    arr = itab.data

    ItabToArray = arr
End Function


Private Function splitData(data() As Variant, delimeter As String) As Variant
    Dim dataSplitted() As Variant '返回值'

    Dim rowcount As Long
    rowcount = UBound(data, 1)

    ' 列數(shù)需要計算'
    Dim testcol As Variant
    testcol = Split(data(1, 1), delimeter) '根據(jù)第一個數(shù)據(jù)來確定列數(shù)'

    Dim colcount As Long
    colcount = UBound(testcol) + 1
    ReDim dataSplitted(1 To rowcount, 1 To colcount)

    Dim line As Variant
    Dim r As Long
    Dim c As Long
    For r = 1 To rowcount
        line = Split(data(r, 1), delimeter) ' line 從0開始'
        For c = 1 To colcount
            dataSplitted(r, c) = line(c - 1)
        Next
    Next

    splitData = dataSplitted
End Function


Private Sub WriteData(fields() As Variant, data() As Variant, inSheet As Worksheet)
    ' Clear first'
    inSheet.Cells.ClearContents

    Dim fieldname() As Variant
    Dim fieldtext() As Variant

    Dim rowcount As Integer
    rowcount = UBound(fields, 1)
    ReDim fieldname(1 To rowcount)
    ReDim fieldtext(1 To rowcount)

    Dim r As Integer
    For r = 1 To UBound(fields, 1)
        fieldname(r) = fields(r, 1) ' 第一列為fieldname'
        fieldtext(r) = fields(r, 5) ' 第五列為fieldtext'
    Next

    ' fieldname和fieldtext寫入工作表'
    ' 第一行fieldname'
    Dim fieldNameRange As Range
    Set fieldNameRange = inSheet.Range("A1")
    fieldNameRange.Resize(1, UBound(fieldname)).Value = fieldname

    ' 第二行fieldtext'
    Dim fieldTextRange As Range
    Set fieldTextRange = inSheet.Range("A2")
    fieldTextRange.Resize(1, UBound(fieldname)).Value = fieldtext

    ' 從第三行開始柱锹，將splitted data寫入工作表'
    Dim dataRange As Range
    Set dataRange = inSheet.Range("A3")
    dataRange.Resize(UBound(data, 1), UBound(data, 2)).Value = data
End Sub