SAP接口编程RFC系列07:通用的数据库表读取

上一篇讲的是调用 RFC_READ_TABLE 查看 SAP table 的数据。为了方便查看数据&＃xff0c;我们可以写一个通用的表查看程序。使用起来比 SAP SE11 或 SE16N 方便点。

本篇没有关于 RFC 调用新的知识点。主要说明函数调用后&＃xff0c;VBA 如何处理这些数据并在 Excel 中显示。不熟悉 VBA 的读者可以参考。由于 VBA 本身数据结构的限制&＃xff0c;处理过程还是蛮啰嗦的。后续用C#调用的代码会方便很多。

不多说&＃xff0c;上代码&＃xff1a;

Option ExplicitPublic Sub test()Call LogonCall ReadTable("T030", Sheet1)Call Logoff
End Sub&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;
&＃39;读取tableName的数据&＃xff0c;写入inSheet这个工作表
&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;&＃39;
Private Sub ReadTable(tableName As String, inSheet As Worksheet)Dim functions As SAPFunctionsSet functions &＃61; New SAPFunctionsDim fm As SAPFunctionsOCX.Function&＃39; RFC_READ_TABLE的三个table型参数Dim optionsTable As SAPTableFactoryCtrl.TableDim dataTable As SAPTableFactoryCtrl.TableDim fieldsTable As SAPTableFactoryCtrl.TableDim delimeter As Stringdelimeter &＃61; "~" &＃39;长度只能为1If sapConnection Is Nothing Then Exit SubSet functions.Connection &＃61; sapConnectionIf sapConnection.IsConnected &＃61; tloRfcConnected Then&＃39;FM加到functions collectionSet fm &＃61; functions.Add("RFC_READ_TABLE")&＃39;------------------------&＃39;填充Import parameters&＃39;------------------------&＃39;QUERY_TABLE是要查找的表名fm.Exports("QUERY_TABLE").Value &＃61; tableName &＃39;Table name&＃39;DELIMITER是输出时字段的分割符fm.Exports("DELIMITER").Value &＃61; delimeterSet optionsTable &＃61; fm.Tables("OPTIONS") &＃39;OPTIONS是筛选条件Set fieldsTable &＃61; fm.Tables("FIELDS") &＃39;FIELDS表示要输出的列Set dataTable &＃61; fm.Tables("DATA") &＃39;DATA为输出的数据fm.Call&＃39;如果有Exception,说明有错误产生If fm.Exception <> "" ThenDebug.Print fm.ExceptionExit SubEnd If&＃39; 存储fields信息的数组Dim fields() As Variantfields &＃61; ItabToArray(fieldsTable)&＃39; 存储data信息的数组Dim data() As Variantdata &＃61; ItabToArray(dataTable)&＃39; 将data分割Dim splittedData() As VariantsplittedData &＃61; splitData(data, delimeter)&＃39; 为了Excel显示需要&＃xff0c;将数据加上"&＃39;", Excel显示为字符型Dim r As LongDim c As LongFor r &＃61; 1 To UBound(splittedData, 1)For c &＃61; 1 To UBound(splittedData, 2)splittedData(r, c) &＃61; "&＃39;" &＃43; splittedData(r, c)NextNext&＃39; 将field name, field text和data整合到一个工作表显示Call WriteData(fields, splittedData, Sheet1)End If
End Sub&＃39; 将itab转换成数组
Private Function ItabToArray(itab As SAPTableFactoryCtrl.Table) As VariantDim arr() As Variantarr &＃61; itab.dataItabToArray &＃61; arr
End FunctionPrivate Function splitData(data() As Variant, delimeter As String) As VariantDim dataSplitted() As Variant &＃39;返回值Dim rowcount As Longrowcount &＃61; UBound(data, 1)&＃39; 列数需要计算Dim testcol As Varianttestcol &＃61; Split(data(1, 1), delimeter) &＃39;根据第一个数据来确定列数Dim colcount As Longcolcount &＃61; UBound(testcol) &＃43; 1ReDim dataSplitted(1 To rowcount, 1 To colcount)Dim line As VariantDim r As LongDim c As LongFor r &＃61; 1 To rowcountline &＃61; Split(data(r, 1), delimeter) &＃39; line 从0开始For c &＃61; 1 To colcountdataSplitted(r, c) &＃61; line(c - 1)NextNextsplitData &＃61; dataSplitted
End FunctionPrivate Sub WriteData(fields() As Variant, data() As Variant, inSheet As Worksheet)&＃39; Clear firstinSheet.Cells.ClearContentsDim fieldname() As VariantDim fieldtext() As VariantDim rowcount As Integerrowcount &＃61; UBound(fields, 1)ReDim fieldname(1 To rowcount)ReDim fieldtext(1 To rowcount)Dim r As IntegerFor r &＃61; 1 To UBound(fields, 1)fieldname(r) &＃61; fields(r, 1) &＃39; 第一列为fieldnamefieldtext(r) &＃61; fields(r, 5) &＃39; 第五列为fieldtextNext&＃39; fieldname和fieldtext写入工作表&＃39; 第一行fieldnameDim fieldNameRange As RangeSet fieldNameRange &＃61; inSheet.Range("A1")fieldNameRange.Resize(1, UBound(fieldname)).Value &＃61; fieldname&＃39; 第二行fieldtextDim fieldTextRange As RangeSet fieldTextRange &＃61; inSheet.Range("A2")fieldTextRange.Resize(1, UBound(fieldname)).Value &＃61; fieldtext&＃39; 从第三行开始&＃xff0c;将splitted data写入工作表Dim dataRange As RangeSet dataRange &＃61; inSheet.Range("A3")dataRange.Resize(UBound(data, 1), UBound(data, 2)).Value &＃61; data
End Sub