当前位置:   article > 正文

SAP接口编程之综合实例(一):资产负债表方案 (转载:http://blog.csdn.net/stone0823 http://blog.csdn.net/stone0823/artic_csdn sap北河

csdn sap北河

SAP 对法定的会计报表,比如资产负债表,损益表等,有多种实现方案。这里给出一个集成 Excel 的实现方案。实现方案的主要优点是用户的自由度比较大,可以灵活设置,而且基本实现了一次开发,不同项目只需要部署就能使用。

1. 资产负债表实现方法说明

  • 在 Excel 中,使用 VBA 调用 SAP RFC 方法,导入公司代码下会计科目和科目汇总表 (英文叫 Trial balance, 包括会计科目的期初余额、本期发生额和期末余额),自动保存到 Excel 工作表,导入的数据格式如下:

从SAP导入的数据格式

数据字段说明:

数据字段说明

  • 在 Excel 中自定义相应函数,实现数据显示。比如我们要实现的 “资产负债表” 格式如下:

资产负债表(部分)

以【货币资金】为例,D7 单元格的公式:=AccAmount(BS_Source!B:B,K7,BS_Source!J:J)AccAmount 是自定义的函数名,表示获取会计科目的期末余额。我们先不管这个函数参数是什么意思,总之是实现自动取数,文章后面会详细说明。

2. 数据通过 RFC 导入到 Excel

2.1 SAP 中自定义函数获取会计科目余额和发生额

SAP 提供的函数 BAPI_GL_GETGLACCPERIODBALANCES 只能提供单个科目的会计余额,我们要导入的是批量数据,所以需要自定义一个函数。写法可以多种,这里给出一个示例代码:

  1. FUNCTION zbapi_getglaccperiodbalances.
  2. *"----------------------------------------------------------------------
  3. *"*"局部接口:
  4. *" IMPORTING
  5. *" VALUE(COMPANYCODE) LIKE FAGLFLEXT-RBUKRS OPTIONAL
  6. *" VALUE(FISCALYEAR) LIKE FAGLFLEXT-RYEAR OPTIONAL
  7. *" VALUE(PERIOD) LIKE FAGLFLEXT-RPMAX OPTIONAL
  8. *" VALUE(CURRENCYTYPE) LIKE BAPI1028_5-CURR_TYPE OPTIONAL
  9. *" EXPORTING
  10. *" VALUE(BALANCE_CARRIED_FORWARD) LIKE BAPI1028_4-BALANCE
  11. *" VALUE(RETURN) LIKE BAPIRETURN STRUCTURE BAPIRETURN
  12. *" TABLES
  13. *" ACCOUNT_BALANCES STRUCTURE ZBALANCES OPTIONAL
  14. *" EXCEPTIONS
  15. *" INIT_DATA
  16. *"----------------------------------------------------------------------
  17. DATA: BEGIN OF lt_fag OCCURS 0,
  18. bukrs LIKE faglflext-rbukrs, "公司代码
  19. ryear LIKE faglflext-ryear, "会计年度
  20. racct LIKE faglflext-racct, "会计科目
  21. drcrk LIKE faglflext-drcrk, "借贷方
  22. hslvt LIKE faglflext-hslvt,
  23. hsl01 LIKE faglflext-hsl01,
  24. hsl02 LIKE faglflext-hsl02,
  25. hsl03 LIKE faglflext-hsl03,
  26. hsl04 LIKE faglflext-hsl04,
  27. hsl05 LIKE faglflext-hsl05,
  28. hsl06 LIKE faglflext-hsl06,
  29. hsl07 LIKE faglflext-hsl07,
  30. hsl08 LIKE faglflext-hsl08,
  31. hsl09 LIKE faglflext-hsl09,
  32. hsl10 LIKE faglflext-hsl10,
  33. hsl11 LIKE faglflext-hsl11,
  34. hsl12 LIKE faglflext-hsl12,
  35. hsl13 LIKE faglflext-hsl13,
  36. hsl14 LIKE faglflext-hsl14,
  37. hsl15 LIKE faglflext-hsl15,
  38. hsl16 LIKE faglflext-hsl16,
  39. END OF lt_fag,
  40. lt_ret LIKE zbalances OCCURS 0 WITH HEADER LINE.
  41. FIELD-SYMBOLS: <field>.
  42. DATA: lv_idx TYPE i,
  43. lv_times TYPE i,
  44. lv_int TYPE i,
  45. lv_rpmax(2) TYPE c,
  46. lv_fieldname TYPE string,
  47. lv_hslxx LIKE faglflext-hslvt,
  48. lv_waers LIKE t001-waers.
  49. * 公司本位币
  50. SELECT SINGLE waers
  51. INTO lv_waers
  52. FROM t001
  53. WHERE bukrs = companycode.
  54. SELECT rbukrs AS bukrs
  55. ryear
  56. racct
  57. drcrk
  58. hslvt
  59. hsl01
  60. hsl02
  61. hsl03
  62. hsl04
  63. hsl05
  64. hsl06
  65. hsl07
  66. hsl08
  67. hsl09
  68. hsl10
  69. hsl11
  70. hsl12
  71. hsl13
  72. hsl14
  73. hsl15
  74. hsl16
  75. INTO TABLE lt_fag
  76. FROM faglflext
  77. WHERE rbukrs = companycode
  78. and ryear = fiscalyear.
  79. SORT lt_fag BY bukrs ryear racct drcrk.
  80. LOOP AT lt_fag.
  81. AT NEW racct.
  82. CLEAR lt_ret.
  83. lt_ret-comp_code = lt_fag-bukrs.
  84. lt_ret-gl_account = lt_fag-racct.
  85. lt_ret-fisc_year = lt_fag-ryear.
  86. lt_ret-fis_period = period.
  87. lt_ret-currency = lv_waers.
  88. ENDAT.
  89. * 年初余额
  90. lt_ret-yr_begin_bal = lt_ret-yr_begin_bal + lt_fag-hslvt.
  91. IF period = 12.
  92. lv_times = 16.
  93. ELSE.
  94. lv_times = period.
  95. ENDIF.
  96. * 期初余额
  97. lt_ret-per_begin_bal = lt_ret-per_begin_bal + lt_fag-hslvt.
  98. DO lv_times TIMES.
  99. IF sy-index >= lv_times.
  100. EXIT.
  101. ENDIF.
  102. MOVE sy-index TO lv_rpmax.
  103. SHIFT lv_rpmax RIGHT DELETING TRAILING space.
  104. OVERLAY lv_rpmax WITH '00'.
  105. CONCATENATE 'HSL' lv_rpmax INTO lv_fieldname.
  106. ASSIGN COMPONENT lv_fieldname OF STRUCTURE lt_fag TO <field>.
  107. MOVE <field> TO lv_hslxx.
  108. lt_ret-per_begin_bal = lt_ret-per_begin_bal + lv_hslxx.
  109. ENDDO.
  110. IF period = 12.
  111. lv_times = 5.
  112. ELSE.
  113. lv_times = 1.
  114. ENDIF.
  115. * 期间发生额
  116. DO lv_times TIMES.
  117. lv_int = period + sy-index - 1.
  118. MOVE lv_int TO lv_rpmax.
  119. SHIFT lv_rpmax RIGHT DELETING TRAILING space.
  120. OVERLAY lv_rpmax WITH '00'.
  121. CONCATENATE 'HSL' lv_rpmax INTO lv_fieldname.
  122. ASSIGN COMPONENT lv_fieldname OF STRUCTURE lt_fag TO <field>.
  123. MOVE <field> TO lv_hslxx.
  124. IF lt_fag-drcrk = 'S'.
  125. * 期间借方
  126. lt_ret-debits_per = lt_ret-debits_per + lv_hslxx.
  127. ELSE.
  128. * 期间贷方
  129. lt_ret-credit_per = lt_ret-credit_per + lv_hslxx.
  130. ENDIF.
  131. ENDDO.
  132. AT END OF racct.
  133. APPEND lt_ret.
  134. ENDAT.
  135. ENDLOOP.
  136. LOOP AT lt_ret.
  137. lv_idx = sy-tabix.
  138. * 期间发生额(借方+贷方)
  139. lt_ret-period_amt = lt_ret-debits_per + lt_ret-credit_per.
  140. * 期末余额
  141. lt_ret-balance = lt_ret-per_begin_bal + lt_ret-period_amt.
  142. MODIFY lt_ret INDEX lv_idx.
  143. ENDLOOP.
  144. DELETE lt_ret WHERE yr_begin_bal = 0 AND per_begin_bal = 0 AND debits_per = 0 AND credit_per = 0.
  145. account_balances[] = lt_ret[].
  146. ENDFUNCTION.
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168

函数名: zbapi_getglaccperiodbalances。函数的参数包括公司代码,年度,期间,币别,根据参数所确定的条件,输出这些科目的期初余额,本期发生额,期末余额。代码简单,不多解释,也不是本文的重点。但顺便提一句,SAP 系统 ABAP 的编码就这么多,不像其他解决方案,可能需要大量 ABAP 编码。

2.2 RFC SAP_Connection模块

从模块的角度给出代码。Excel 首先是SAP_Connection模块,负责连接 SAP 系统,登陆 (Logon) 和注销 (Logoff) 。

  1. ' 模块说明:
  2. ' 负责SAP的连接,连接成功后,SAPConnection对象保存连接的信息
  3. Option Explicit
  4. Dim sapLogon As SAPLogonCtrl.SAPLogonControl
  5. Public SAPConnection As SAPLogonCtrl.Connection
  6. Public logonSuccessful As Boolean
  7. Public Sub logon()
  8. logonSuccessful = False
  9. Set sapLogon = New SAPLogonControl
  10. Set SAPConnection = sapLogon.NewConnection()
  11. Call SAPConnection.logon(0, False)
  12. If SAPConnection.IsConnected = tloRfcConnected Then
  13. logonSuccessful = True
  14. Exit Sub
  15. End If
  16. End Sub
  17. Public Sub Logoff()
  18. If SAPConnection Is Nothing Then
  19. Exit Sub
  20. End If
  21. SAPConnection.Logoff
  22. logonSuccessful = False
  23. Set SAPConnection = Nothing
  24. End Sub
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

2.3 RFC 调用 函数 zbapi_getglaccperiodbalances

调用 SAP zbapi_getglaccperiodbalances 函数,将数据写入工作表。 
首先在 VBA 中实现 ABAP内表数据(函数的表参数)写入工作表的通用实现。考虑到代码效率,数据先在内存中处理后,一次性写到工作表:

  1. ' 模块说明:
  2. ' SAP FM的表参数类似internal table, 将数据输出值EXCEL工作表
  3. Option Explicit
  4. '--------------------------------------------------
  5. ' 将Table对象写入worksheet, 整体拷贝,速度优化
  6. '--------------------------------------------------
  7. Public Sub WriteTable(itab As SAPTableFactoryCtrl.Table, sht As Worksheet)
  8. Dim col As Long ' column index
  9. Dim row As Long ' row index
  10. Dim headerRange As Variant '在Excel中根据itab的header大小,类型为Variant数组
  11. Dim itemsRange As Variant '在Excel中根据itab的行数和列数,类型为Variant数组
  12. ' 清除cells的内容
  13. sht.Cells.ClearContents
  14. If itab.RowCount = 0 Then
  15. Exit Sub
  16. End If
  17. '-------------------------------------------------
  18. ' 取消Excel的屏幕刷新和计算功能以加快速度
  19. '-------------------------------------------------
  20. Application.ScreenUpdating = False
  21. Application.Calculation = xlCalculationManual
  22. '------------------------------
  23. ' 将Table的Header写入Worksheet
  24. '------------------------------
  25. ' 根据内表的列数,使用Range创建一个数组
  26. Dim headerstarts As Range
  27. Dim headerends As Range
  28. Set headerstarts = sht.Cells(1, 1)
  29. Set headerends = sht.Cells(1, itab.ColumnCount)
  30. headerRange = sht.Range(headerstarts, headerends).Value
  31. ' 将内表列名写入数组
  32. For col = 1 To itab.ColumnCount
  33. headerRange(1, col) = itab.Columns(col).Name
  34. Next
  35. ' 从数组一次性写入Excel,这样效率较高
  36. sht.Range(headerstarts, headerends).Value = headerRange
  37. '-------------------------------
  38. ' 将Table的行项目写入Worksheet
  39. '-------------------------------
  40. ' 根据内表的大小,使用Range创建数组
  41. Dim itemStarts As Range
  42. Dim itemEnds As Range
  43. Set itemStarts = sht.Cells(2, 1)
  44. Set itemEnds = sht.Cells(itab.RowCount + 1, itab.ColumnCount)
  45. itemsRange = itab.Data
  46. ' 一次性将数组写入Worksheet
  47. sht.Range(itemStarts, itemEnds).Value = itemsRange
  48. '---------------------------------
  49. ' 恢复Excel的屏幕刷新和计算
  50. '---------------------------------
  51. Application.ScreenUpdating = True
  52. Application.Calculation = xlCalculationAutomatic
  53. Application.Calculate
  54. End Sub
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69

然后是调用 zbapi_getglaccperiodbalances 函数,代码在sap_get_acc_period_bal模块:

  1. ' 模块说明:
  2. ' 调用RFC获取公司代码会计科目的余额,写入工作表
  3. '
  4. ' v1.0, 2015-6-14 by Stone Wang
  5. ' V1.1, 2015-6-26 by Stone Wang
  6. Option Explicit
  7. Public Sub get_sap_acc_balances(cocd As String, fiscal_yr As String, period As String)
  8. ' 导入SAP科目余额,写入BS_Source工作表
  9. Dim sapFunctions As SAPFunctionsOCX.sapFunctions
  10. Dim sapFunction As Object
  11. Dim acBalanceTable As Object
  12. ' 写入的工作表
  13. Dim sht As Worksheet
  14. Set sht = BSSource
  15. If logonSuccessful = False Then Exit Sub
  16. Set sapFunctions = CreateObject("SAP.FUNCTIONS") ' Use function to call BAPI
  17. Set sapFunctions.Connection = SAPConnection
  18. On Error GoTo err1:
  19. Set sapFunction = sapFunctions.Add("ZBAPI_GETGLACCPERIODBALANCES")
  20. ' Function import parameters
  21. sapFunction.Exports("COMPANYCODE").Value = cocd 'Company code
  22. sapFunction.Exports("FISCALYEAR").Value = fiscal_yr 'Fiscal year
  23. sapFunction.Exports("PERIOD").Value = period
  24. sapFunction.Exports("CURRENCYTYPE").Value = "10" 'Currency type
  25. ' Function table parameters
  26. Set acBalanceTable = sapFunction.Tables("ACCOUNT_BALANCES")
  27. If sapFunction.Call = True Then
  28. If acBalanceTable.RowCount > 0 Then
  29. sht.Activate
  30. '将internal table写入工作表
  31. Call WriteTable(acBalanceTable, sht)
  32. Else
  33. sht.Activate
  34. sht.Cells.ClearContents
  35. sht.Range("A1").Value = "没有数据被导入,数据源可能为空!"
  36. End If
  37. End If
  38. Set acBalanceTable = Nothing
  39. Set sapFunctions = Nothing
  40. Set sapFunction = Nothing
  41. err1:
  42. If Err.Number = 1001 Then
  43. MsgBox "远程调用错误,请检查SAP中是否存在函数ZBAPI_GETGLACCPERIODBALANCES,是否允许远程调用等。", _
  44. vbExclamation
  45. Exit Sub
  46. End If
  47. End Sub
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59

另外,因为 Excel 自定义函数需要用到会计科目,从速度上考虑,把会计科目列表存放在一个工作表中,方便后续函数调用。会计科目列表调用 
SAP 的 BAPI 来实现,这里直接从刚才导入的会计科目余额中提取,代码放在 account_gl_list模块 中:

  1. ' 模块说明:
  2. ' 从COASheet工作表获取公司代码下所有的会计科目清单
  3. ' 在UDF中,需要在会计科目的范围中循环,根据公司代码的科目清单,目的是提高效率
  4. Option Explicit
  5. Public Sub Generate_GL_List()
  6. ' 根据BS_Source的科目,产生会计科目清单,剔除重复项(如果有的话)
  7. ' 数据存放在GLList工作表中
  8. Dim row_count As Integer
  9. row_count = BSSource.UsedRange.rows.count
  10. ' 如果没有数据,则不处理
  11. If row_count <= 1 Then Exit Sub
  12. With GLListSheet
  13. ' 清除之前的数据
  14. .Cells.ClearContents
  15. ' 数据拷贝
  16. .Range("A1:A" & row_count + 1).Value = BSSource.Range("B1:B" & row_count + 1).Value
  17. ' 消除重复项
  18. .Range("A:A").RemoveDuplicates
  19. ' 根据会计科目排序
  20. .Columns("A:A").Sort key1:=.Range("A:A"), order1:=xlAscending, Header:=xlYes
  21. End With
  22. End Sub
  23. Public Function get_gl_count() As Integer
  24. '------------------------------------------
  25. ' 获取会计科目的数量
  26. ' 会计科目根据BS_Source导入的数据加工而得
  27. '------------------------------------------
  28. Dim count As Integer
  29. '因为有表头,实际的会计科目数减一
  30. count = GLListSheet.UsedRange.rows.count - 1
  31. get_gl_count = count
  32. End Function
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

2.4 用户操作界面设计

使用一个Excel工作表作为录入界面,执行上面的会计科目余额导入程序。

用户界面

2.5 导入数据的代码

当用点击【更新数据源】按钮,执行下面的代码,导入会计科目余额,至此,跟 SAP 数据交互完成。

  1. ' 模块说明:
  2. ' 从SAP系统导入会计科目余额
  3. ' Version 1.0, 2015-6-14 by Stone Wang
  4. Option Explicit
  5. Public Sub import_sap_data()
  6. ' 校验(input validation)
  7. If Range("COMPANY_CODE").Value = "" Then
  8. MsgBox "请输入公司代码.", vbExclamation
  9. Exit Sub
  10. End If
  11. If Range("FISCAL_YEAR").Value = "" Then
  12. MsgBox "请输入会计年度.", vbExclamation
  13. Exit Sub
  14. End If
  15. If Range("PERIOD").Value = "" Then
  16. MsgBox "请输入会计期间.", vbExclamation
  17. Exit Sub
  18. End If
  19. ' 如果没有连接到SAP,连接SAP
  20. If logonSuccessful <> True Then
  21. Call logon
  22. End If
  23. ' 导入会计科目余额
  24. Dim cocd As String
  25. Dim fiscal_year As String
  26. Dim period As String
  27. cocd = CStr(Range("COMPANY_CODE").Value)
  28. fiscal_year = CStr(Range("FISCAL_YEAR").Value)
  29. period = CStr(Range("PERIOD").Value)
  30. Call get_sap_acc_balances(cocd, fiscal_year, period)
  31. ' 根据BS_Source产生科目清单
  32. ' 将会计科目清单写入GLList工作表
  33. Call Generate_GL_List
  34. Application.Calculate
  35. End Sub
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46

3. Excel 自定义函数

我打算自定义的函数叫做 AccAmount,是对 Excel SumIfs 函数的包装和功能增强。

SumIfs 函数的语法大体如下:

SumIfs(rangeA, criteria, rangeB) 
根据 criteria(条件),在 rangeA 中找到相同的数据,然后将 rangeB 中对应行的数据加总,并作为返回值。

增强的原因是使用 SumIfs 函数,只能处理一个科目,因为资产负债表每个数据项可能来自多个科目,所以需要对这个函数进行修改,使得能处理多个科目。

我对资产负债表的数据项数据来源,定义了一种范围表达的格式。在科目范围中,用【分号】表示分隔的科目,用【..】表示连接的科目。举例如下:

  1. - 1001000000..1099999999:表示 1001 开头的科目,即 1001000000
  2. 1099999999
  3. - 2241000000..2241999999;2703000000..2703999999:表示2241开头的科目以及2703开头的科目
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

有了这个定义格式,我们就实现 AccAmount() 函数:

AccAmount(rangeA, accCriteria, rangeB)
  • 1
  • 1

根据 accCriteria(会计科目条件)在 rangeA 中找到对应科目,然后将rangeB 中对应行的单元格数据加总,并将汇总的数据作为返回值。

3.1 科目范围分解为会计科目数组

刚才所述的会计科目范围,我们需要将其分解为单个的科目,存放在数组中,代码放在 account_split 模块之中:

  1. '模块说明:
  2. '会计科目的范围用分号表示不连续的范围,用..表示连续的范围
  3. '会计科目范围分解出具体的会计科目,先根据分号分割,再从连续的科目中分解
  4. '为了提高效率,循环的时候,读取公司代码的实际科目清单
  5. ' Verion 1.1:
  6. ' Account_resolution函数增加了对输入参数为空的判断
  7. Option Explicit
  8. Private Function split_account_by_semicolon(account_range As String) As String()
  9. '-------------------------------------------
  10. ' 将科目范围根据"分号"分解,然后放在数组中
  11. ' 数组中每一个item, 可能是单个科目,也可能是多个科目
  12. '-------------------------------------------
  13. Dim acc_array() As String
  14. acc_array = Split(account_range, ";") ' split函数返回的数组从0开始
  15. ' 逗号分割的每一个数组元素前后可能存在空格,将空格去掉
  16. Dim i As Integer
  17. For i = LBound(acc_array) To UBound(acc_array)
  18. acc_array(i) = Trim(CStr(acc_array(i)))
  19. Next
  20. split_account_by_semicolon = acc_array
  21. End Function
  22. Private Function split_continous_account(continous_acc As String) As String()
  23. '将会计科目根据..进行分解
  24. '..表示连续的科目范围
  25. '如果有.., 但未找到合适科目,返回#EmptyForXXX#
  26. 'acc_count: 会计科目数量
  27. Dim acc_count As Integer
  28. acc_count = get_gl_count()
  29. Dim acc_list() As String
  30. If acc_count >= 1 Then
  31. ReDim acc_list(1 To acc_count)
  32. Else
  33. ReDim acc_list(1 To 1)
  34. End If
  35. Dim dot_pos As Integer ' position of double dots 双点的位置
  36. dot_pos = InStr(continous_acc, "..") ' 查找 ".." ,返回位置
  37. Dim lower_acc As String
  38. Dim upper_acc As String
  39. ' 一共需要处理三种情况:
  40. ' 1) 有..号,有科目被选择
  41. ' 2) 有..号,没有科目被选择
  42. ' 3) 没有..号,表示单个科目
  43. If dot_pos > 0 Then ' Found
  44. lower_acc = Left(continous_acc, dot_pos - 1)
  45. upper_acc = Right(continous_acc, Len(continous_acc) - dot_pos - 1)
  46. Dim account_range As Range
  47. Set account_range = GLListSheet.Range("A2:A" & acc_count + 1)
  48. Dim i As Integer
  49. Dim item As Variant
  50. Dim temp_acc As String
  51. i = 1 ' cocd_coa下标从1开始
  52. For Each item In account_range
  53. temp_acc = CStr(item)
  54. If temp_acc >= lower_acc And temp_acc <= upper_acc Then
  55. acc_list(i) = CStr(item)
  56. i = i + 1
  57. End If
  58. ' 确保会计科目表示按科目大小排序的
  59. If temp_acc > upper_acc Then
  60. Exit For
  61. End If
  62. Next
  63. If i > 1 Then ' i > 1表示有科目包括在其中
  64. ReDim Preserve acc_list(1 To i - 1)
  65. Else ' 表示没有科目被选择
  66. ReDim Preserve acc_list(1 To 1)
  67. acc_list(1) = "#EmptyFor" & continous_acc & "#" ' 没有找到科目,empty错误
  68. End If
  69. Else
  70. ReDim acc_list(1 To 1)
  71. acc_list(1) = continous_acc
  72. End If
  73. split_continous_account = acc_list
  74. End Function
  75. Public Function accounts_resolution(acc_range As String) As String()
  76. '----------------------------------------------------
  77. ' 根据科目范围,将范围分解为单个会计科目,放在数组中
  78. '----------------------------------------------------
  79. ' account_array()表示所有范围内科目,函数最后作为返回值
  80. Dim account_array() As String
  81. ' 先判断参数acc_range是否为空
  82. If Len(acc_range) = 0 Then
  83. ReDim account_array(1 To 1)
  84. account_array(1) = "#Empty#"
  85. accounts_resolution = account_array
  86. Exit Function
  87. End If
  88. ' 如果参数acc_range不为空,正常处理
  89. Dim count As Integer
  90. count = get_gl_count()
  91. If count > 1 Then
  92. ReDim account_array(1 To count)
  93. Else
  94. ReDim account_array(1 To 1)
  95. End If
  96. ' 先根据分号进行分解,分解后每一项表示单一科目或连续的科目
  97. Dim continous_acc() As String
  98. continous_acc = split_account_by_semicolon(acc_range)
  99. Dim i As Integer
  100. i = 1
  101. Dim item As Variant
  102. For Each item In continous_acc
  103. ' 再将连续的科目分解为具体的会计科目
  104. Dim acc_in_continuous_rng() As String
  105. acc_in_continuous_rng = split_continous_account(CStr(item))
  106. Dim acc As Variant
  107. For Each acc In acc_in_continuous_rng
  108. account_array(i) = CStr(acc)
  109. i = i + 1
  110. Next
  111. Next
  112. ' split_continuous_account()至少返回一个值
  113. ' 没有分解成功返回:#EmptyForXXX#, i的值一定会>=2, 所以不用判断i的大小
  114. ReDim Preserve account_array(1 To i - 1)
  115. accounts_resolution = account_array
  116. End Function
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149

代码说明

  • split_account_by_semicolon 函数,用于将科目范围中 有分号 的分解,并存放在一个数组之中
  • split_continous_account 函数,用于将连续的科目分解,结果存放在数组中
  • accounts_resolution 函数,综合上面的两个函数,对科目范围进行分解,并将结果存放在数组中

3.2 定义 AccAmount 函数供用户使用

将会计科目范围中的科目,循环调用 SumIfs 函数,结果加总,并作为返回值。

  1. ' @Author: Stone Wang
  2. ' @version 1.0
  3. ' @date: 2015-6-14
  4. '
  5. ' 模块说明:
  6. ' 自定义函数,获取科目在某一列的合计,该函数是对EXCEL SUMIFs函数的增强
  7. Option Explicit
  8. Public Function AccAmount(ByVal acc_range As Range, _
  9. ByVal acc_criteria As String, _
  10. ByVal sum_range As Range) As Double
  11. ' 获取account期间的余额
  12. ' acc_range: account range
  13. ' acc_criteria: account criteria
  14. ' sum_range: sum range
  15. Dim result As Double
  16. Dim acc_array() As String
  17. acc_array = accounts_resolution(acc_criteria)
  18. Dim item As Variant
  19. Dim current_acc As String
  20. For Each item In acc_array
  21. current_acc = CStr(item)
  22. If Len(current_acc) > 0 Then
  23. result = result + _
  24. WorksheetFunction.SumIfs(sum_range, acc_range, current_acc)
  25. End If
  26. Next
  27. AccAmount = result
  28. End Function
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

3.3 AccAmount 函数使用方法说明

设置专门的数据源定义区,比如货币资金,设置科目范围如下:

货币资金科目范围

并且将 1001000000..1099999999 所在的单元格定义一个名称为 CASH,这样在资产负债表的货币资金行中,使用如下公式:

=AccAmount(BS_Source!B:B,CASH,BS_Source!J:J)
  • 1
  • 1

表示根据 CASH 单元格的科目范围(即 1001000000 至 1099999999),从 BS_Source 工作表的 B 列找到科目范围中有的科目,将相应行的 J 列数据汇总。

这样,定义了所有科目的公式之后,资产负债表就做好了。因为函数是给用户使用的,用户可以自由定义报表的各种细节,灵活性就非常好,自动化程度也高。

我曾在两个 SAP 实施项目中使用了这种方法来实现资产负债表、损益表和现金流量表。如果用户接受 Excel 作为数据展示的平台,我们可以用这个思路实现很多报表。

版权声明:本文为博主原创文章,转载请注明出处:http://blog.csdn.net/stone0823 http://blog.csdn.net/stone0823/article/details/54149183
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/黑客灵魂/article/detail/746596
推荐阅读
相关标签
  

闽ICP备14008679号