利用EXCEL VBA批量處理科目匯總表

摘 要：批量憑證裝訂是業(yè)務(wù)量較大單位財(cái)務(wù)基礎(chǔ)工作的一個(gè)難題。以用友財(cái)務(wù)軟件U8為例，利用Excel和VBA，通過(guò)按照預(yù)設(shè)的年、月及憑證范圍，批量完成科目匯總表、憑證封面及包角的打印，以期為提高會(huì)計(jì)基礎(chǔ)工作效率提供一種思路。

關(guān)鍵詞：批量處理;科目匯總表;excel;VBA

0 ?引 ?言

隨著IT技術(shù)的不斷普及，以關(guān)系數(shù)據(jù)庫(kù)為基礎(chǔ)的管理系統(tǒng)在日常工作中得以廣泛應(yīng)用，憑證裝訂前的科目匯總表、憑證封面及包角填寫成為會(huì)計(jì)基礎(chǔ)工作一項(xiàng)必要內(nèi)容，但對(duì)憑證業(yè)務(wù)量大的單位如何對(duì)該類工作進(jìn)行批量處理成為一個(gè)難以解決的問(wèn)題。目前較常見(jiàn)的解決辦法有兩種：一種是通過(guò)用友財(cái)務(wù)軟件進(jìn)行查詢打印;另一種是專門開(kāi)發(fā)某一管理系統(tǒng)的軟件，根據(jù)操作結(jié)果來(lái)判分。但這兩種方法存在靈活性差、開(kāi)發(fā)不易的問(wèn)題。

以用友U8的憑證和科目總賬為例，通過(guò)取自數(shù)據(jù)庫(kù)中的數(shù)據(jù)，利用Excel和VBA，按照預(yù)定的的年、月、憑證范圍來(lái)批量完成這一過(guò)程。

1 ?相關(guān)工作簿

為完成批量處理過(guò)程，設(shè)計(jì)了sheet1（分冊(cè)）、sheet7（科目匯總）兩個(gè)工作表，連接分冊(cè)也為憑證信息文件，科目匯總為科目匯總表查詢結(jié)果，兩表結(jié)構(gòu)如圖1、圖2。

1.1 ?憑證信息表：sheet1（分冊(cè)）

如圖1所示，工作表中，B1為賬務(wù)年份，B2為賬務(wù)月份，B3為自動(dòng)統(tǒng)計(jì)的憑證總冊(cè)數(shù);A列A5以下為記賬憑證第幾冊(cè)，B列B5以下為憑證起號(hào)，C列C5以下為憑證止號(hào)。

如圖2所示，工作表的第二行取自分冊(cè)工作表，第三行A列為科目編碼;B列為科目名稱，C列為金額合計(jì)借方，D列為金額合計(jì)貸方;從第四行開(kāi)始是根據(jù)用友財(cái)務(wù)軟件U8數(shù)據(jù)庫(kù)中的gl_accvouch表的內(nèi)容統(tǒng)計(jì)所得。

批量處理分下3個(gè)步驟。

（1）錄入憑證起止號(hào)。先在分冊(cè)表的B1輸入年份;在B2選擇月份，B2運(yùn)用數(shù)據(jù)驗(yàn)證菜單功能，設(shè)置月份序列，提供下拉列表;B3輸入函數(shù)“=COUNT（C6：C200）”，自動(dòng)統(tǒng)計(jì)憑證冊(cè)數(shù)。

然后，自C6開(kāi)始向下，逐本輸入每本憑證的末張憑證號(hào)。

（2）設(shè)文本框、命令按鈕。在sheet7（科目匯總）中，插入文本框，存儲(chǔ)第幾冊(cè)憑證。插入兩個(gè)命令按鈕，一個(gè)上顯示"上一頁(yè)"，另一個(gè)上顯示“下一頁(yè)”。

（3）用程序生成科目匯總表。在sheet7（科目匯總）中，按ALT+Fll進(jìn)入VBE窗口后輸入取數(shù)程序代碼，執(zhí)行后從第四行開(kāi)始顯示統(tǒng)計(jì)結(jié)果。

3 ?程序代碼

程序代碼如下：[2]

Sub hzb（）

Dim i

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim strCn As String， strSQL As String

strCn="Provider=sqloledb;Server=caiwu;Database=UFDATA_011_2019;Uid=sa;Pwd=******;"

'strCn = "Provider=sqloledb;Server=yhcwb;Database=pubs;Uid=sa;Pwd=;"

cn.Open strCn

Rows（"4：65536"）.Select

Selection.ClearContents

ActiveSheet.Cells（1， 1）.Select

Dim cMaxPz As String， cMinPz As String

Dim cPZ As String

Dim iLen As Integer， iPos As Integer

'***取出憑證號(hào)***

iPos = InStr（1， ActiveSheet.Cells（2， 2）， "-"）

If iPos = 0 Then ?'******只輸入一個(gè)憑證號(hào)

cPZ = ActiveSheet.Cells（2， 2）

Else

iLen = Len（Trim（ActiveSheet.Cells（2， 2）））

cMinPz = Left（ActiveSheet.Cells（2， 2）， iPos - 1）

cMaxPz = Right（ActiveSheet.Cells（2， 2）， iLen - iPos）

End If

'**寫入科目代碼和借貸余額**

If iPos = 0 Then

strSQL = "SELECT ?LEFT（ccode，4） AS km，SUM（md） AS jf，SUM（mc） AS df ?FROM GL_accvouch WHERE iyear="

strSQL = strSQL & ActiveSheet.Cells（2， 3） & " AND iperiod=" & ActiveSheet.Cells（2， 4）

strSQL = strSQL & " AND ino_id=" & cPZ

strSQL = strSQL & " GROUP BY LEFT（ccode，4） ORDER BY LEFT（ccode，4） "

Else

strSQL = "SELECT ?LEFT（ccode，4） AS km，SUM（md） AS jf，SUM（mc） AS df ?FROM GL_accvouch WHERE iyear="

strSQL = strSQL & ActiveSheet.Cells（2， 3） & " AND iperiod=" & ActiveSheet.Cells（2， 4）

strSQL = strSQL & " AND ino_id>=" & cMinPz

strSQL = strSQL & " AND ino_id<=" & cMaxPz

strSQL = strSQL & " GROUP BY LEFT（ccode，4） ORDER BY LEFT（ccode，4） "

End If

rs.Open strSQL， cn

i = 4

Do While Not rs.EOF

ActiveSheet.Cells（i， 1） = rs（"km"）

ActiveSheet.Cells（i， 3） = Format（rs（"jf"）， "##，##0.00"）

ActiveSheet.Cells（i， 4） = Format（rs（"df"）， "##，##0.00"）

i = i + 1

rs.MoveNext

Loop

Set rs = Nothing

'***************************

'**寫入科目名稱*

strSQL = "select ccode，ccode_name from code where iyear=" & ActiveSheet.Cells（2， 3） & " and len（ccode）=4 order by ccode"

rs.Open strSQL， cn

Do While Not rs.EOF

For i = 4 To ActiveSheet.UsedRange.Rows.Count

If ActiveSheet.Cells（i， 1） = rs（"ccode"） Then

ActiveSheet.Cells（i， 2） = rs（"ccode_name"）

End If

Next i

rs.MoveNext

Loop

Set rs = Nothing

cn.Close

r = Range（"d650"）.End（xlUp）.Row

Sheet7.Cells（Range（"d650"）.End（xlUp）.Row + 1， 4） = "單位：江蘇省地質(zhì)調(diào)查研究院"

r = Range（"d650"）.End（xlUp）.Row

Cells（r， 4）.HorizontalAlignment = xlRight

End Sub

‘**上一頁(yè)**

Private Sub CommandButton1_Click（）

If chTxtBx.Value > 1 Then ? '第幾冊(cè)憑證

chTxtBx.Value = chTxtBx.Value - 1

qspzh = Sheet1.Cells（chTxtBx.Value + 5， 2）.Text

zzpzh = Sheet1.Cells（chTxtBx.Value + 5， 3）.Text

Sheet7.Cells（2， 2） = qspzh + "-" + zzpzh

hzb ? ? ? ? ? ? ? ? ? ?'按條件取數(shù)

Else

MsgBox "Hi，已經(jīng)是第一冊(cè)了"

End If

End Sub

‘**下一頁(yè)**

Private Sub CommandButton2_Click（）

fff = Sheet1.Cells（3， 2）.Value

ddd = chTxtBx.Value

If Val（ddd） < fff Then

chTxtBx.Value = chTxtBx.Value + 1

qspzh = Sheet1.Cells（chTxtBx.Value + 5， 2）.Text

zzpzh = Sheet1.Cells（chTxtBx.Value + 5， 3）.Text

Sheet7.Cells（2， 2） = qspzh + "-" + zzpzh

hzb ? '按條件取數(shù)

Else

MsgBox "恭喜，已經(jīng)是最后一冊(cè)了"

End If

End Sub

‘**工作表激活，按第一本憑證參數(shù)取數(shù)**

Private Sub Worksheet_Activate（）

chTxtBx.Value = 1

qspzh = Sheet1.Cells（chTxtBx.Value + 5， 2）.Text

zzpzh = Sheet1.Cells（chTxtBx.Value + 5， 3）.Text

Sheet7.Cells（2， 2） = qspzh + "-" + zzpzh

hzb ? ? ? '按條件取數(shù)

End Sub

4 ?方法的優(yōu)點(diǎn)與結(jié)論

該方法在Excel2003、Excel2010和用友U8環(huán)境下測(cè)試通過(guò)，其具有下列優(yōu)點(diǎn)。

（1）簡(jiǎn)單易用。該辦法主要利用EXCEL的易操作性進(jìn)行處理，只要用VBA通過(guò)SQL語(yǔ)句將數(shù)據(jù)取到工作簿中，完成分冊(cè)表中相關(guān)設(shè)置，利用上述代碼即可完成科目匯總表輸出功能。

（2）一數(shù)三用。分冊(cè)參數(shù)可以用于封面批量打印和包角批量打印的數(shù)據(jù)源，只要按照封面和包角的格式略加設(shè)計(jì)調(diào)整。

（3）可移植性強(qiáng)。上述程序代碼不受用友財(cái)務(wù)系統(tǒng)本身各表結(jié)構(gòu)影響，具有通用性。其他用戶只需要修改取數(shù)程序代碼中的服務(wù)器地址和數(shù)據(jù)庫(kù)名稱，就可以使用該方法法來(lái)批量處理科目匯總表、憑證封面和憑證包角的打印。

參考文獻(xiàn)：

[1] 衣光臻. 分旬科目匯總表在Excel中的模型構(gòu)建[J]. 商業(yè)會(huì)計(jì)，2014（11）：128-129.

[2] 羅剛軍. Excel VBA程序開(kāi)發(fā)自學(xué)寶典[M]. 3版. 北京：電子工業(yè)出版社，2014.

作者簡(jiǎn)介：

許洪品，男，漢族，江蘇建湖人，1971年5.26，本科，會(huì)計(jì)師，江蘇省地質(zhì)調(diào)查研究院（江蘇 南京）（210018），研究方向：會(huì)計(jì)學(xué)理論與實(shí)踐

課題項(xiàng)目：此文為江蘇省國(guó)土資源廳課題“江蘇省地質(zhì)勘查基金項(xiàng)目資金管理系統(tǒng)開(kāi)發(fā)”項(xiàng)目階段性成果之一。