VBA自定义函数，快速输入ABAP代码

已于 2024-09-30 15:00:33 修改
阅读量397 收藏
点赞数 1
分类专栏： ABAP VBA 文章标签： 笔记
于 2024-09-29 11:52:53 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_43156252/article/details/142631276
版权

 结构、表 声明

DATA: gt_ztco0056 TYPE TABLE OF ztco0056,
      gs_last56   TYPE  ztco0056.

DATA: gt_luxlog TYPE TABLE OF ztfi0072luxlog, 
      gs_luxlog LIKE LINE OF gt_luxlog.

DATA ct_zsfi0072a TYPE TABLE OF zsfi0072a.
FIELD-SYMBOLS <fs_zsfi0072a> LIKE LINE OF ct_zsfi0072a.

结构声明：继承

TYPES: BEGIN OF ty_report.
         INCLUDE STRUCTURE zsfi0072c.
       TYPES:
                sel TYPE c,             "选择字段
              END OF ty_report.

DATA : gt_report TYPE TABLE OF ty_report.

结构声明：SAP_TypeDATA

 VBA定义函数SAP_TypeDATA

参数TypeName是声明的自定义名称

Function SAP_TypeDATA(Optional TypeName As String = "Data") As String
    Application.Volatile

    Dim arr2()
    
    ReDim arr2(6)
    arr2(0) = "TYPES: BEGIN OF ty_" & TypeName & ","
    arr2(1) = "END OF ty_" & TypeName & "."
    arr2(2) = ""
    arr2(3) = "DATA: gt_" & TypeName & " TYPE TABLE OF ty_" & TypeName & ","
    arr2(4) = "gs_" & TypeName & " TYPE ty_" & TypeName & ","
    arr2(5) = "lt_" & TypeName & " TYPE TABLE OF ty_" & TypeName & ","
    arr2(6) = "wa_" & TypeName & " TYPE ty_" & TypeName & "."

    SAP_TypeDATA = Join(arr2, Chr(10))
End Function

返回结果是ABAP代码

"TYPES: BEGIN OF ty_Data,
END OF ty_Data.

DATA: gt_Data TYPE TABLE OF ty_Data,
gs_Data TYPE ty_Data,
lt_Data TYPE TABLE OF ty_Data,
wa_Data TYPE ty_Data."

结构声明：SAP_TypesBeginOF

 VBA定义函数SAP_TypesBeginOF

Function SAP_TypesBeginOF(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '组件
        s2 = arr1(x, 2) '组件类型
        s3 = arr1(x, 3) '简短描述
        i = Len(s2)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '组件
        s2 = arr1(x, 2) '组件类型
        s3 = arr1(x, 3) '简短描述
        i = Len(s2)
        arr2(x) = s1 & " TYPE " & s2 & "," & Application.Rept(" ", j - i + 10) & """" & s3
    Next x
    
    SAP_TypesBeginOF = Join(arr2, Chr(10))
End Function

输入是RANGE表

字段数据元素简短描述
BUKRSBUKRS公司代码
ANLN1ANLN1主资产号
ANLN2ANLN2资产子编号 
ANLKLANLKL资产类
TXK20TXT20_ANKT资产分类名称

返回结果是ABAP代码

"BUKRS TYPE BUKRS,               ""公司代码
ANLN1 TYPE ANLN1,               ""主资产号
ANLN2 TYPE ANLN2,               ""资产子编号 
ANLKL TYPE ANLKL,               ""资产类
TXK20 TYPE TXT20_ANKT,          ""资产分类名称"

结构声明：SAP_TypesBeginOF2

  VBA定义函数SAP_TypesBeginOF2

Function SAP_TypesBeginOF2(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), v, arr2(), s1 As String, s2 As String, s3 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '来源  透明表
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        i = Len(s1) + Len(s2)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '来源  透明表
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        i = Len(s1) + Len(s2)
        arr2(x) = s2 & " TYPE " & s1 & "-" & s2 & "," & Application.Rept(" ", j - i + 10) & """" & s3
    Next x
    
    SAP_TypesBeginOF2 = Join(arr2, Chr(10))
End Function

输入是RANGE表

表名字段简短描述
ANLABUKRS公司代码
ANLAANLN1主资产号
ANLAANLN2资产子编号 
ANLAANLKL资产类
ANKTTXK20资产分类名称

返回结果是ABAP代码

"BUKRS TYPE ANLA-BUKRS,          ""公司代码
ANLN1 TYPE ANLA-ANLN1,          ""主资产号
ANLN2 TYPE ANLA-ANLN2,          ""资产子编号 
ANLKL TYPE ANLA-ANLKL,          ""资产类
TXK20 TYPE ANKT-TXK20,          ""资产分类名称"

结构声明：SAP_TypesBeginOF3

  VBA定义函数SAP_TypesBeginOF3

Function SAP_TypesBeginOF3(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String, s4 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '组件
        s2 = arr1(x, 2) '类型
        s3 = arr1(x, 3) '长度
        If s3 <> "" Then s3 = "(" & s3 & ")"
        s4 = arr1(x, 4) '简短描述
        i = Len(s2)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '组件
        s2 = arr1(x, 2) '类型
        s3 = arr1(x, 3) '长度
        If s3 <> "" Then s3 = "(" & s3 & ")"
        s4 = arr1(x, 4) '简短描述
        i = Len(s2)
        arr2(x) = s1 & s3 & " TYPE " & s2 & "," & Application.Rept(" ", j - i + 10) & """" & s4
    Next x
    
    SAP_TypesBeginOF3 = Join(arr2, Chr(10))
End Function

输入是RANGE表

字段数据类型长度简短描述
BUKRSC4公司代码
ANLN1C12主资产号
ANLN2C4资产子编号 
ANLKLC8资产类
TXK20C20资产分类名称

 返回结果是ABAP代码

"BUKRS(4) TYPE C,          ""公司代码
ANLN1(12) TYPE C,          ""主资产号
ANLN2(4) TYPE C,          ""资产子编号 
ANLKL(8) TYPE C,          ""资产类
TXK20(20) TYPE C,          ""资产分类名称"

SQL取值：SAP_SelectFrom

   VBA定义函数SAP_SelectFrom

Function SAP_SelectFrom(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String, s4 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '来源  透明表
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        i = Len(s1) + Len(s2)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '来源  透明表
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        i = Len(s1) + Len(s2)
        If x = UBound(arr1) Then
            s4 = " "
        Else
            s4 = ","
        End If
        arr2(x) = "      " & s1 & "~" & s2 & s4 & Application.Rept(" ", j - i + 10) & """" & s3
    Next x
    
    SAP_SelectFrom = Join(arr2, Chr(10))
End Function

 输入是RANGE表

表名字段简短描述
ANLABUKRS公司代码
ANLAANLN1主资产号
ANLAANLN2资产子编号 
ANLAANLKL资产类
ANKTTXK20资产分类名称

  返回结果是ABAP代码

"      ANLA~BUKRS,          ""公司代码
      ANLA~ANLN1,          ""主资产号
      ANLA~ANLN2,          ""资产子编号 
      ANLA~ANLKL,          ""资产类
      ANKT~TXK20           ""资产分类名称"

SQL取值：SAP_SelectFrom2

    VBA定义函数SAP_SelectFrom2

Function SAP_SelectFrom2(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, k As Integer, arr1(), arr2()
    Dim s1 As String, s2 As String, s3 As String, s4 As String, s5 As String, s6 As String
    
    arr1 = rg.Value
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '来源  透明表
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '声明组件/重命名组件
        i = Len(s1) + Len(s2) + Len(s3) + 4
        If i > j Then j = i
    Next x
    
    k = -1
    For x = 1 To UBound(arr1)
        If arr1(x, 1) <> "" Then
            s1 = arr1(x, 1) '来源  透明表
            s2 = arr1(x, 2) '组件
            s3 = arr1(x, 3) '声明组件/重命名组件
            s4 = arr1(x, 4) '简短描述
            
            If s2 = s3 Then
                s5 = ""
                i = Len(s1) + Len(s2)
            Else
                s5 = " AS " & s3
                i = Len(s1) + Len(s2) + Len(s3) + 4
            End If
            
            If x = UBound(arr1) Then
                s6 = " "
            Else
                s6 = ","
            End If
            
            k = k + 1
            ReDim Preserve arr2(k)
            arr2(k) = "      " & s1 & "~" & s2 & s5 & s6 & Application.Rept(" ", j - i + 10) & """" & s4
        End If
    Next x
    
    SAP_SelectFrom2 = Join(arr2, Chr(10))
End Function

 输入是RANGE表

表名字段重命名字段简短描述
ANLABUKRSVKORG公司代码/销售组织
ANLAANLN1ANLN1主资产号
ANLAANLN2ANLN2资产子编号 
ANLAANLKLANLKL资产类
ANKTTXK20TXK20资产分类名称

  返回结果是ABAP代码

"      ANLA~BUKRS AS VKORG,          ""公司代码/销售组织
      ANLA~ANLN1,                   ""主资产号
      ANLA~ANLN2,                   ""资产子编号 
      ANLA~ANLKL,                   ""资产类
      ANKT~TXK20                    ""资产分类名称"

Oracel SQL传值：SAP_VALUES

    VBA定义函数SAP_VALUES

Function SAP_VALUES(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区名
        s2 = arr1(x, 2) '字段名
        
        If x = UBound(arr1) Then
            s3 = " "
        Else
            s3 = ","
        End If
            
        arr2(x) = " :" & s1 & "-" & s2 & s3
    Next x
    
    SAP_VALUES = Join(arr2, Chr(10))
End Function

  输入是RANGE表

工作区字段
gs_dataBUKRS
gs_dataANLN1
gs_dataANLN2
gs_dataANLKL
gs_dataTXK20

   返回结果是ABAP代码

" :gs_data-BUKRS,
 :gs_data-ANLN1,
 :gs_data-ANLN2,
 :gs_data-ANLKL,
 :gs_data-TXK20 "

  这段代码要插入Oracel SQL的LOOP AT循环中，例子如下

        LOOP AT pt_zsfi0072a INTO DATA(ls_zsfi0072a).
          EXEC SQL .
            INSERT INTO ZTFI0072A
            ( MANDT,ZHTBH,ZHTBB,ZZLLX,ZSXM1 )
            VALUES
            (  :ls_zsfi0072a-MANDT,
               :ls_zsfi0072a-ZHTBH,
               :ls_zsfi0072a-ZHTBB,
               :ls_zsfi0072a-ZZLLX,
               :ls_zsfi0072a-ZSXM1
             )
          ENDEXEC .
        ENDLOOP .

赋值：SAP_CONCATENATE

  VBA定义函数SAP_CONCATENATE

Function SAP_CONCATENATE(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, k As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String
    
    arr1 = rg.Value
   
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        i = Len(s1) + Len(s2)
        If i > j Then j = i
    Next x
    
    k = -1
    
    k = k + 1
    ReDim Preserve arr2(k)
    arr2(k) = "CONCATENATE"
        
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        i = Len(s1) + Len(s2)
        
        k = k + 1
        ReDim Preserve arr2(k)
        arr2(k) = "      " & s1 & "-" & s2 & Application.Rept(" ", j - i + 10) & """" & s3
    Next x

    k = k + 1
    ReDim Preserve arr2(k)
    arr2(k) = "INTO lv_string." 'lv_string是个例子，要自行替换
    
    SAP_CONCATENATE = Join(arr2, Chr(10))
End Function

 输入是RANGE表

工作区字段简短描述
gs_dataBUKRS公司代码
gs_dataANLN1主资产号
gs_dataANLN2资产子编号 
gs_dataANLKL资产类
gs_dataTXK20资产分类名称

  返回结果是ABAP代码 

"CONCATENATE
      gs_data-BUKRS          ""公司代码
      gs_data-ANLN1          ""主资产号
      gs_data-ANLN2          ""资产子编号 
      gs_data-ANLKL          ""资产类
      gs_data-TXK20          ""资产分类名称
INTO lv_string."

赋值：MOVE-CORRESPONDING

 LOOP AT循环里，工作区添加到内表

MOVE-CORRESPONDING ls_anlav TO t_data.

 透明表赋值

MOVE-CORRESPONDING it_exp[] TO gt_ztfi0072a[].

赋值：SAP_Move  

VBA定义函数：SAP_Move  

Function SAP_Move(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String, s4 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        i = Len(s1) + Len(s2)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        i = Len(s1) + Len(s2)
        arr2(x) = s1 & "-" & s2 & " = ''." & Application.Rept(" ", j - i + 10) & """" & s3
    Next x
    
    SAP_Move = Join(arr2, Chr(10))
End Function

 输入是RANGE表

工作区字段简短描述
gs_dataBUKRS公司代码
gs_dataANLN1主资产号
gs_dataANLN2资产子编号 
gs_dataANLKL资产类
gs_dataTXK20资产分类名称

   返回结果是ABAP代码 

"gs_data-BUKRS = ''.          ""公司代码
gs_data-ANLN1 = ''.          ""主资产号
gs_data-ANLN2 = ''.          ""资产子编号 
gs_data-ANLKL = ''.          ""资产类
gs_data-TXK20 = ''.          ""资产分类名称"

赋值：SAP_Move2

 VBA定义函数：SAP_Move2

Function SAP_Move2(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), v, arr2(), s1 As String, s2 As String, s3 As String, s4 As String, s5 As String, s6 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        s4 = arr1(x, 4) '来源工作区
        s5 = arr1(x, 5) '来源工作区的字段
        i = Len(s1) + Len(s2) + Len(s4) + Len(s5)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        s4 = arr1(x, 4) '来源工作区
        s5 = arr1(x, 5) '来源工作区的字段
        If s4 = "" Then
            s6 = " '" & s5 & "' "
        Else
            s6 = s4 & "-" & s5
        End If
        i = Len(s1) + Len(s2) + Len(s4) + Len(s5)
       arr2(x) = "    " & s1 & "-" & s2 & " = " & s6 & "." & Application.Rept(" ", j - i + 10) & """" & s3
    Next x
    
    SAP_Move2 = Join(arr2, Chr(10))
End Function

 输入是RANGE表

工作区字段简短描述来源工作区来源工作区的字段
gs_dataBUKRS公司代码wa_dataBUKRS
gs_dataANLN1主资产号wa_dataANLN1
gs_dataANLN2资产子编号 wa_dataANLN2
gs_dataANLKL资产类wa_dataANLKL
gs_dataTXK20资产分类名称wa_dataTXK20

    返回结果是ABAP代码 

"    gs_data-BUKRS = wa_data-BUKRS.          ""公司代码
    gs_data-ANLN1 = wa_data-ANLN1.          ""主资产号
    gs_data-ANLN2 = wa_data-ANLN2.          ""资产子编号 
    gs_data-ANLKL = wa_data-ANLKL.          ""资产类
    gs_data-TXK20 = wa_data-TXK20.          ""资产分类名称"

赋值：SAP_Move3

  VBA定义函数：SAP_Move3

Function SAP_Move3(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, k As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String, s4 As String
    
    arr1 = rg.Value

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        s4 = arr1(x, 4) '等于 值

        i = Len(s1) + Len(s2) + Len(s4)
        If i > j Then j = i
    Next x
    
    k = -1
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '工作区
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        s4 = arr1(x, 4) '等于 值
        i = Len(s1) + Len(s2) + Len(s4)
        k = k + 1
        ReDim Preserve arr2(k)
        arr2(k) = "    " & s1 & "-" & s2 & " = '" & s4 & "'." & Application.Rept(" ", j - i + 10) & """" & s3
    Next x
    
    SAP_Move3 = Join(arr2, Chr(10))
End Function

  输入是RANGE表

工作区字段简短描述值
gs_dataBUKRS公司代码C121
gs_dataANLN1主资产号002606000020
gs_dataANLN2资产子编号 0000
gs_dataANLKL资产类00002606
gs_dataTXK20资产分类名称

    返回结果是ABAP代码 

"    gs_data-BUKRS = 'C121'.                  ""公司代码
    gs_data-ANLN1 = '002606000020'.          ""主资产号
    gs_data-ANLN2 = '0000'.                  ""资产子编号 
    gs_data-ANLKL = '00002606'.              ""资产类
    gs_data-TXK20 = ''.                      ""资产分类名称"

内表赋值：SAP_gtData

  VBA定义函数：SAP_gtData

Function SAP_gtData(rg As Range, Optional gt As String = "gt_data") As String
    Dim x As Integer, y As Integer, arr1(), v, arr2(), sr As String, k As Integer
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1) + 2)

    k = 1
    arr2(k) = gt & " = VALUE #("

    For x = 2 To UBound(arr1)
        k = k + 1
        sr = ""
        For y = 1 To UBound(arr1, 2)
            sr = sr & " " & arr1(1, y) & " = '" & arr1(x, y) & "' "
        Next y
        arr2(k) = "(" & sr & ")"
    Next x
    
    k = k + 1
    arr2(k) = ")."
    
    SAP_gtData = Join(arr2, Chr(10))
End Function

输入是RANGE表

BUKRSANLN1ANLN2ANLKLTXK20
C121002606000020000000002606

    返回结果是ABAP代码 

"gt_data = VALUE #(
( BUKRS = 'C121'  ANLN1 = '002606000020'  ANLN2 = '0000'  ANLKL = '00002606'  TXK20 = '' )
).
"

注意，EXCEL的日期与ABAP不同，要自行转换

而且，如果数据太多的话，函数返回值会错误，可以使用SUB方法

 注意下表头行的位置，第一行是中文，第二行是字段，第三行以后是值

Sub SAP_gtData_text()

    Dim FirstLine As Integer, InnerTable As String
    Dim rg As Range, x As Integer, y As Integer, arr1(), v, arr2(), sr As String, k As Integer
    
    FirstLine = 3
    InnerTable = "gt_data"
    
    Set rg = ActiveSheet.Cells(FirstLine, 1)
    If rg.Value = "" Then Exit Sub
    arr1 = rg.CurrentRegion.Value
    ReDim arr2(1 To UBound(arr1) + 2)

    Open ActiveWorkbook.Path & "\" & InnerTable & Format(Now(), "YYYYMMDDhhmmss") & ".txt" For Output As #1
                
    Print #1, InnerTable & " = VALUE #("

    For x = FirstLine + 1 To UBound(arr1)
        sr = ""
        For y = 1 To UBound(arr1, 2)
            sr = sr & " " & arr1(FirstLine, y) & " = '" & arr1(x, y) & "' "
        Next y
        Print #1, "(" & sr & ")"
    Next x
    
    Print #1, ")."
    Close #1
    
    Shell "explorer.exe " & ActiveWorkbook.Path, 1
End Sub

屏幕：SAP_SelectOptions

 VBA定义函数：SAP_SelectOptions

Function SAP_SelectOptions(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), arr2(), s1 As String, s2 As String, s3 As String, s4 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '来源  透明表
        s2 = arr1(x, 2) '组件
        i = Len(s1) + Len(s2)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '来源  透明表
        s2 = arr1(x, 2) '组件
        s3 = arr1(x, 3) '简短描述
        i = Len(s1) + Len(s2)
        If x = UBound(arr1) Then
            s4 = "."
        Else
            s4 = ","
        End If
        arr2(x) = "s_" & s2 & " FOR " & s1 & "-" & s2 & " MODIF ID f1 " & s4 & "   """ & s3
    Next x
    
    SAP_SelectOptions = Join(arr2, Chr(10))
End Function

输入是RANGE表

表名字段简短描述
ANLABUKRS公司代码
ANLAANLN1主资产号
ANLAANLN2资产子编号 
ANLAANLKL资产类
ANKTTXK20资产分类名称

     返回结果是ABAP代码 ，前面要自行加  SELECT-OPTIONS:

"s_BUKRS FOR ANLA-BUKRS MODIF ID f1 ,   ""公司代码
s_ANLN1 FOR ANLA-ANLN1 MODIF ID f1 ,   ""主资产号
s_ANLN2 FOR ANLA-ANLN2 MODIF ID f1 ,   ""资产子编号 
s_ANLKL FOR ANLA-ANLKL MODIF ID f1 ,   ""资产类
s_TXK20 FOR ANKT-TXK20 MODIF ID f1 .   ""资产分类名称"

屏幕默认*：SAP_SELECTOPTIONS_INITIAL

VBA定义函数：SAP_SELECTOPTIONS_INITIAL

Function SAP_SELECTOPTIONS_INITIAL(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, arr1(), v, arr2(), s1 As String, s2 As String, s3 As String, k As Integer
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1) * 4)

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '字段
        s2 = arr1(x, 2) '简短描述
        k = k + 1
        arr2(k) = "  IF s_" & s1 & " IS INITIAL . """ & s2
        k = k + 1
        arr2(k) = "  s_" & s1 & "(3) = 'ICP'. "
        k = k + 1
        arr2(k) = " s_" & s1 & "-low = '*' . "
        k = k + 1
        arr2(k) = " ENDIF."
    Next x
    
    SAP_SELECTOPTIONS_INITIAL = Join(arr2, Chr(10))
End Function

 输入是RANGE表

字段简短描述
BUKRS公司代码
ANLN1主资产号
ANLN2资产子编号 
ANLKL资产类
TXK20资产分类名称

返回结果是ABAP代码

"  IF s_BUKRS IS INITIAL . ""公司代码
  s_BUKRS(3) = 'ICP'. 
 s_BUKRS-low = '*' . 
 ENDIF.
  IF s_ANLN1 IS INITIAL . ""主资产号
  s_ANLN1(3) = 'ICP'. 
 s_ANLN1-low = '*' . 
 ENDIF.
  IF s_ANLN2 IS INITIAL . ""资产子编号 
  s_ANLN2(3) = 'ICP'. 
 s_ANLN2-low = '*' . 
 ENDIF.
  IF s_ANLKL IS INITIAL . ""资产类
  s_ANLKL(3) = 'ICP'. 
 s_ANLKL-low = '*' . 
 ENDIF.
  IF s_TXK20 IS INITIAL . ""资产分类名称
  s_TXK20(3) = 'ICP'. 
 s_TXK20-low = '*' . 
 ENDIF."

二分法取值：SAP_WithKey

 VBA定义函数：SAP_WithKey

Function SAP_WithKey(rg As Range) As String
    Application.Volatile

    Dim x As Integer, i As Integer, j As Integer, k As Integer, arr1(), v, arr2(), s1 As String, s2 As String, s3 As String, s4 As String, s5 As String, s6 As String
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1) + 1)

    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '组件
        s2 = arr1(x, 2) '简短描述
        s3 = arr1(x, 3) '等于来源
        s4 = arr1(x, 4) '等于来源的组件
        i = Len(s1) + Len(s3) + Len(s4)
        If i > j Then j = i
    Next x
    
    For x = 1 To UBound(arr1)
        s1 = arr1(x, 1) '组件
        s2 = arr1(x, 2) '简短描述
        s3 = arr1(x, 3) '等于来源
        s4 = arr1(x, 4) '等于来源的组件
        i = Len(s1) + Len(s3) + Len(s4)
        k = k + 1
       arr2(k) = s1 & " = " & s3 & "-" & s4 & Application.Rept(" ", j - i + 10) & """" & s2
    Next x
    
    k = k + 1
    arr2(k) = "BINARY SEARCH."
    
    SAP_WithKey = Join(arr2, Chr(10))
End Function

 输入是RANGE表

字段简短描述来源工作区来源工作区的字段
BUKRS公司代码wa_dataBUKRS
ANLN1主资产号wa_dataANLN1
ANLN2资产子编号 wa_dataANLN2
ANLKL资产类wa_dataANLKL
TXK20资产分类名称wa_dataTXK20

返回结果是ABAP代码

 前面要自行加：READ TABLE gt_data INTO gs_data WITH KEY

"BUKRS = wa_data-BUKRS          ""公司代码
ANLN1 = wa_data-ANLN1          ""主资产号
ANLN2 = wa_data-ANLN2          ""资产子编号 
ANLKL = wa_data-ANLKL          ""资产类
TXK20 = wa_data-TXK20          ""资产分类名称
BINARY SEARCH."

ALV的fieldcat格式: SAP_fieldcat

 VBA定义函数： SAP_fieldcat

'输入两列多行的数据，第一列是文本描述，第二列是t01开始
Function SAP_fieldcat(rg As Range, Optional INSIGN As String = "t") As String
    Application.Volatile

    Dim x As Integer, i As Integer, arr1(), v, arr2(), s1 As String, s2 As String, s3 As String, k As Integer
    
    arr1 = rg.Value
    ReDim arr2(1 To UBound(arr1))
    
    For x = 1 To UBound(arr1)
        If arr1(x, 1) <> "" Then
            s1 = UCase(arr1(x, 1))
            s2 = arr1(x, 2)
            If x = UBound(arr1) Then
                s3 = "."
            Else
                s3 = ","
            End If
            k = k + 1
         arr2(k) = "    '" & s1 & "' '" & s2 & "'(" & INSIGN & Rept0(x, 2) & ") '' '' '' ''" & s3
         End If
     Next x
    
    SAP_fieldcat = Join(arr2, Chr(10))
End Function

输入是RANGE表

字段简短描述
BUKRS公司代码
ANLN1主资产号
ANLN2资产子编号 
ANLKL资产类
TXK20资产分类名称

前面要加定义宏才能使用

*----------------------------------------------------------------------*
*                  定义宏
*----------------------------------------------------------------------*
  DEFINE macro_fieldcat.
    CLEAR ls_fieldcat.
    ls_fieldcat-fieldname = &1. "标题列对应的字段名
    ls_fieldcat-coltext = &2. "列标题 列标题，如果字段参考了DDIC，可不设置
    ls_fieldcat-fix_column = &3. "固定列 与KEY字段类似，固定列但颜色不变蓝，必须要在左边才起作用(如果设置固定列的左边包含非固定列，则该列不固定)
    ls_fieldcat-no_zero = &4. "是否隐藏输出为零的字段 当字段为数值为0时，不显示【如0，0.00，则会显示为空】当字段包含前导0时，隐藏前导0，【如0100，则会显示为100】
    "ls_fieldcat-cfieldname = &5. "currency unit field name值为当前输出内表中的货币单位字段的字段名称
    "ls_fieldcat-edit = &6. "准备输入 X：可编辑，space：不可编辑 该控制是在整列的级别上，如果想控制仅仅某一行可编辑，则需要通过style来控制
    ls_fieldcat-ref_field = &5.
    ls_fieldcat-ref_table = &6.

    IF ls_fieldcat-outputlen IS INITIAL. "当coltext不存在显示filedtext，如字段没域，只有手工描述的情况
      ls_fieldcat-outputlen = 40. "方便条件筛选
    ENDIF.

    APPEND ls_fieldcat TO lt_fieldcat.
  END-OF-DEFINITION.


  macro_fieldcat:

返回结果是ABAP代码 

一般还要自行在第三列加X,以便键值固定

"    'BUKRS' '公司代码'(t01) '' '' '' '',
    'ANLN1' '主资产号'(t02) '' '' '' '',
    'ANLN2' '资产子编号 '(t03) '' '' '' '',
    'ANLKL' '资产类'(t04) '' '' '' '',
    'TXK20' '资产分类名称'(t05) '' '' '' ''."

VBA编程：自定义函数 - 字符串转Hex数据
诊断协议那些事儿's blog
12-22 213
使用VBA编程将字符串转换成hex数据，并以逗号隔开数据
VBA直连SAP RFC 接口实例
秋成的博客
05-15 1163
VBA 调用 SAP API的RFC函数：RFC_READ_TABLE。
SAP ABAP DOI 调用 Excel 宏 案例详解 通过调用宏实现自动印章
SAP李斌，本人承诺所有原创文章均不使用任何AI生成技术，请大家放心阅读。
03-15 1846
通过调用宏实现自动印章等内容输出，分离 ABAP 实现与 Excel 实现！
SAP-ABAP:一篇教你所有ABAP相关文件处理的程序示例与场景
LinkStar的博客
08-21 2671
要使本章中的所有示例都能正常运行，必须满足以下要求： 桌面 Office 集成 (DOI) 和对象链接与嵌入 (OLE)： 本章中显示的大多数示例都需要安装 Microsoft Office 软件包。提示：以下是本篇文章正文内容，下面案例可供参考Github。
ABAP按照销售办事处或业务员将每月总销售额写入自建表
兔宝学习ABAP整理笔记
01-15 4933
按照销售办事处的表： 按照业务员的表： 将他们的业绩分别写入进去： REPORT Z_WUWEI_0011 TABLES:VBAK,VBAP,VBPA,LIPS,LIKP,MKPF,KNA1,VBUP. DATA TTAB1 LIKE ZSALE_WM3 OCCURS 0 WITH HEADER LINE. DATA TTAB3 LIK
使用EXCEL VBA调用SAP 的function：BAPI_GOODSMVT_GETITEMS
weixin_56024187的博客
07-11 520
但是如果开发把exports里面的入参做成和表类似的结构，就没有成功过了，不知道是不是exports不支持和tables一样的写法，毕竟我也没有找到标准function这么写的，只能靠猜测了。这里有个很有意思的地方，写成ch.value(y,sheets("items").cells(2,s))就报错，把其中sheets("items").cells(2,s)先赋给a，再写成ch.value(y,a)就不报错。“4.显示到EXCEL”的部分，这部分和与SAP的连接无关，主要是VBA的写法。
ABAP常用函数集锦 转
easyjosn的专栏
01-27 999
ABAP常用函数集锦 函数名 描述 SD_VBAP_READ_WITH_VBELN 根据销售订单读取表vbap中的信息 EDIT_LINES 把READ_TEXT返回的LINES中的行按照TDFORMAT=“*”重新组织 VIEW_MAINTENANCE_CALL 维护表视图 函数名 描述 DY_GET_FOCUS 获得屏幕焦点 DY_GET_SET_FIELD_VALUE 获得或者设置屏幕字段的
SAP HANA ABAP-通过接口生成SAP财务凭证远程函数
最新发布
liuaiguo75的博客
01-05 374
泛微OA系统对接SAP远程函数审批完成后，自动在SAP系统中生成财务凭证的RFC函数。前部分通过添加了函数调用日志的存储功能，便于接口调用的日志查询。四、函数源代码，预付款是通过BDC进行凭证生成的。
使用VBA连接Excel到本地SAP客户端
4. **ABAP (Advanced Business Application Programming)**: ABAP是SAP系统的主要开发语言，虽然本文没有详细涉及，但在实现Excel与SAP的连接中，可能涉及到ABAP开发以创建必要的接口或函数。 5. **SAP-MS Office ...
使用VBA连接SAP并导出Excel数据
"这篇文档是关于如何在SAP系统中使用Excel宏(macro)与SAP集成，将数据从SAP内部表导出到Excel模板，以便...不过，需要注意的是，这种方法可能需要对SAP ABAP编程和Excel VBA有一定的了解，以便有效地编写和调试宏。
【ABAP与Excel整合】：解决数据整合问题的专家策略
本文详细探讨了ABAP与Excel整合在企业应用中的重要性，系统介绍了ABAP基础和Excel数据接口技术，并深入分析了两者间的数据交互技术。文章通过实例讲述了实现数据导入导出的脚本编写和高级数据处理技巧，同时提供了...
ABAP DOI详解
01-13
DOI 是Desktop Office Integration的缩写，，是 SAP 提供的解决与 Office 集成的开发技术。早期 SAP 用于解决 Office 集成，使用的是 OLE 技术。OLE 有两个比较大的缺点，一是语法参照 VBA，在 ABAP 中使用各种 VBA 的方法 (method)，语法的友好性差；二是数据写入到 Excel 中，速度特别慢，慢得难以接受。 DOI 是 SAP 提供的 OLE 的替代品，用面向对象的方式实现。很好的解决了上面所说的两个问题。
EXCEL技术VBA一键获取SAP系统数据
weixin_42808203的博客
02-24 1万+
1.第一步 在SAP系统建立一个可远程调用的函数RFC 2.第二步 就是在EXCEL里面的操作了 找到开发工具 点击Visual Basic （切记EXCEL要启用宏 在信任中心里面） 3.第三步 在EXCEL里面写代码 格式差不多大同小异 Sub 点击刷新() '设置登录信息 Set R3 = CreateObject(“SAP.Functions”) R3.Connection.Syst...
VBA 操作SAP
热门推荐
yuwanfei的博客
04-24 1万+
一、吐槽 之前曾用autoit操作SAP，但那样的话，用户的界面被autoit控制，就不能随便乱动。因此想做一个静态连接SAP，然后读取，操作SAP的程序。为后期自动化做准备，找了很多资料，尝试了不少语言，最后选择了VBA。 在我看来SAP是一个数据库管理系统，记得20年前刚解除计算机，曾学了一点foxbase，做过一个图书馆管理系统。当然SAP很复杂，因为SAP本身有自己的ABAP语言（之...
VBA调用SAP GUI
weixin_34381666的博客
10-31 4070
2019独角兽企业重金招聘Python工程师标准>>> ...
使用VBA调用SAP RFC
lyb_yt的专栏
01-28 5950
使用VBA调用SAP RFC和JCO 常用的方法基本一致。 大致分为两步：登录和执行 1、设置登录信息 Set R3 = CreateObject(”SAP.Functions”) R3.Connection.System = “DEV” R3.Connection.ApplicationServer = “10.1.82.46″ R3.Connection.Client = “160
用vba连接sap
cspr33008的博客
05-12 2362
sample code： Dim iData As Integer Dim nField As Integer Dim nData As Integer Dim Result As Boolean ...
【转】ABAP在表VBAP和VBAK上增加新字段的标准BAPI
少年休闲海
07-14 1742
【转载收藏】 在表VBAP和VBAK上增加新字段的标准BAPI 及实现方式： If you have custom ZZ fields on VBAP and VBAK then you can update these fields using the standard BAPI to create or change the order and update these fields. A lot has been spoken about this but nowhere does it sp
深入解析ABAP标准及自定义函数
标准函数提供了一种快速实现常用功能的方法，而自定义函数则为满足特定业务逻辑提供了灵活性。《ABAP函数讲解系列》能够帮助开发者更深入地理解各种函数的用途，提高编程效率和代码质量。通过学习这些知识，开发者...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值