陳鋒
【摘要】在增值稅納稅企業(yè)中,企業(yè)稅務(wù)會計根據(jù)業(yè)務(wù)部門提出的申請,在開票系統(tǒng)中開具發(fā)票,銷售會計則根據(jù)發(fā)票信息在財務(wù)軟件中編制記賬憑證。在這個過程中,稅務(wù)會計需要將開票信息輸入開票系統(tǒng),銷售會計需要將發(fā)票信息輸入財務(wù)軟件,原始數(shù)據(jù)的反復(fù)輸入,既重復(fù)了工作,又降低了準(zhǔn)確性。本文采用EXCELVBA技術(shù),以增值稅防偽稅控開票子系統(tǒng)V7.23.10版本和用友U8軟件為例,設(shè)計開發(fā)了一款將開票系統(tǒng)數(shù)據(jù)轉(zhuǎn)換為財務(wù)系統(tǒng)數(shù)據(jù)的接口(以下簡稱數(shù)據(jù)轉(zhuǎn)換接口),實現(xiàn)了開票系統(tǒng)數(shù)據(jù)向用友軟件自動導(dǎo)入。
【關(guān)鍵詞】VBA 防偽開票系統(tǒng) 用友軟件 數(shù)據(jù)導(dǎo)入
一、數(shù)據(jù)轉(zhuǎn)換接口的開發(fā)既具有必要性又具有可能性
增值稅防偽稅控系統(tǒng)是1994年國家稅務(wù)總局聯(lián)合航天工業(yè)總公司等部門研制開發(fā)的,作為國家金稅工程的重要組成部分,其在全國各企業(yè)中普遍使用。防偽開票子系統(tǒng),是增值稅防偽稅控系統(tǒng)的四個子系統(tǒng)之一,是專門用于企業(yè)開具專用發(fā)票的應(yīng)用軟件。然而,在V7.23.10之前的版本(以下簡稱舊版),防偽開票子系統(tǒng)在發(fā)票填開過程中只能采用手動填開的方式,且其開票數(shù)據(jù)不支持導(dǎo)出。因此,在銷售業(yè)務(wù)中,稅務(wù)會計需要將業(yè)務(wù)部門提交的開票信息輸入開票系統(tǒng)開具發(fā)票,銷售會計需要將已開具發(fā)票信息輸入財務(wù)軟件生成記賬憑證。原始數(shù)據(jù)的重復(fù)輸入造成工作繁瑣、效率低下、出錯率高等問題,同時也大大地消耗了企業(yè)的人力和時間。因此,設(shè)計一款數(shù)據(jù)轉(zhuǎn)換接口,實現(xiàn)開票系統(tǒng)數(shù)據(jù)直接導(dǎo)入財務(wù)系統(tǒng)生成記賬憑證,十分必要。
防偽開票子系統(tǒng)的升級為數(shù)據(jù)轉(zhuǎn)換接口的開發(fā)提供了保障。實現(xiàn)數(shù)據(jù)導(dǎo)入,前提是獲得需要導(dǎo)入的數(shù)據(jù)。然而,舊版防偽開票子系統(tǒng)是不支持已開具發(fā)票數(shù)據(jù)導(dǎo)出的,為此,在防偽開票子系統(tǒng)接口領(lǐng)域,出現(xiàn)了許多商業(yè)化軟件。由于相關(guān)標(biāo)準(zhǔn)的缺失,各商業(yè)化接口價格差異較大,技術(shù)水平也良莠不齊,價格低的,納稅人擔(dān)心接口是否會對原系統(tǒng)造成損害,后續(xù)服務(wù)是否及時;價格高的,對于中小企業(yè)納稅人來講,是一筆額外的負(fù)擔(dān)。然而,隨著國家稅務(wù)總局2014年第17號公告的發(fā)布,這個問題得到了較好地解決。公告中指出,為進(jìn)一步減輕納稅人負(fù)擔(dān),優(yōu)化納稅服務(wù),國家稅務(wù)總局決定對納稅人使用的增值稅發(fā)票稅控開票軟件數(shù)據(jù)接口規(guī)范予以發(fā)布。根據(jù)公告的要求,新版的防偽開票子系統(tǒng)可以將已開具的發(fā)票數(shù)據(jù)從稅控開票軟件中導(dǎo)出(導(dǎo)出數(shù)據(jù)提供XLS和XML兩種格式),滿足納稅人內(nèi)部管理信息系統(tǒng)與稅控開票軟件的銜接需要,數(shù)據(jù)接口規(guī)范的發(fā)布使得個人用VBA開發(fā)數(shù)據(jù)轉(zhuǎn)換接口的成為了可能。
用友U8軟件總賬工具提供了良好的數(shù)據(jù)導(dǎo)入接口。在用友U8軟件中,總賬工具是總賬系統(tǒng)中攜帶的一個應(yīng)用子程序,它可以實現(xiàn)不同賬套或同一賬套不同機器之間的數(shù)據(jù)傳遞,總賬工具中的憑證引入功能可以按標(biāo)準(zhǔn)格式將TXT文本文件引入到憑證庫中。了解了標(biāo)準(zhǔn)格式TXT文件,用VBA技術(shù)將防偽開票系統(tǒng)導(dǎo)出的數(shù)據(jù)轉(zhuǎn)換成標(biāo)準(zhǔn)文件,即可實現(xiàn)數(shù)據(jù)的引入。
VBA 是Visual Basic for Application的簡稱,可用來擴展Windows應(yīng)用程序(特別是Excel)的功能。Excel與VBA的結(jié)合,可以使大量重復(fù)、繁瑣的工作自動化,成倍地提高工作效率,在辦公自動化中應(yīng)用廣泛。此外,VBA代碼并不需要逐句編寫,用戶可通過錄制宏來產(chǎn)生所需要的代碼,然后對錄制的代碼稍加修改便可得到自己需要的代碼,這大大降低了學(xué)習(xí)VBA的門檻。本文利用VBA處理表格方面的優(yōu)越特性和易學(xué)性,將開票系統(tǒng)導(dǎo)出的XLS文件,加工處理后生成能夠直接導(dǎo)入用友U8軟件的標(biāo)準(zhǔn)格式TXT文件,實現(xiàn)開票數(shù)據(jù)的共享。
二、數(shù)據(jù)轉(zhuǎn)換接口設(shè)計思路
首先,先從“防偽稅控開票子系統(tǒng)”中導(dǎo)出開票數(shù)據(jù)。打開開票系統(tǒng),點擊“報稅處理\抄報稅管理\開票數(shù)據(jù)導(dǎo)出”功能,系統(tǒng)彈出文件導(dǎo)出對話框,輸入月份、開票日期及購方信息等,點擊“確定”按鈕,在系統(tǒng)彈出的對話框中選擇導(dǎo)出文件存放路徑,輸入文件名,選擇保存類型為XLS,點擊“保存”按鈕即可完成數(shù)據(jù)導(dǎo)出,在導(dǎo)出文件存放路徑下可以查看到導(dǎo)出的文件。
從開票系統(tǒng)導(dǎo)出的數(shù)據(jù)表(以下簡稱數(shù)據(jù)表)結(jié)構(gòu)如下:第1至第5行為納稅企業(yè)的基本信息;第6行為發(fā)票各項目的名稱,A~P列依次為:A發(fā)票代碼,B發(fā)票號碼,C購方企業(yè)名稱,D購方稅號,E銀行賬號,F(xiàn)地址電話,G開票日期,H單據(jù)號,I商品名稱,J規(guī)格,K單位,L數(shù)量,M單價,N金額,O稅率,P稅額;第7行及以下為所導(dǎo)出發(fā)票的具體數(shù)據(jù)。以第8行為例,列示數(shù)據(jù)如下:
A8:000001,B8:01002,C8:B公司,D8:11B,E8:X銀行,F(xiàn)8:XX市,G8: 2014-1-2,H8:00B,I8:商品2,J8:GG02,K8:EA,L8:10,M8:20.00,N8:200.00,O8:17%,P8:34.00
假設(shè)數(shù)據(jù)表所在工作表的名稱為“發(fā)票數(shù)據(jù)”,在VBA中表示為Sheets("發(fā)票數(shù)據(jù)")?!鞍l(fā)票數(shù)據(jù)”的第6行顯示發(fā)票各項目名稱,如發(fā)票代碼、發(fā)票號碼等,表的第7行及以下是對應(yīng)項目的值,如C8單元格表示票號為“01002”的發(fā)票所對應(yīng)的購方企業(yè)為“B公司”,C8單元格的值在VBA中表示為Cells(8,3),Cells兩個參數(shù)8和3分別指單元格所在的行和列。根據(jù)“發(fā)票數(shù)據(jù)”中的數(shù)據(jù),足以編制出對應(yīng)的分錄,以第8行為例,假設(shè)出售商品2為XX公司主營業(yè)務(wù),記入“主營業(yè)務(wù)收入”科目,XX公司設(shè)置“應(yīng)收賬款”科目,該科目按客戶分類核算,則該分錄如下:
摘要:銷售B公司商品2#1002
借:應(yīng)收賬款(1122)——B公司(11B) 234.00
貸:主營業(yè)務(wù)收入(6001) 200.00
應(yīng)交稅費——應(yīng)交銷項稅(22210105)?34.00
在該分錄中,“摘要”并不能直接獲取,它是將幾個單元格的值和一些其他字符重新組合而成,若以ZY表示摘要,則ZY=“銷售”&Cells(8,3)&Cells(8,9)&“#”&Right(Cells(8,2),4),&為字符串連接符號,Right函數(shù)用于取發(fā)票號的后四位。endprint
其次,需要了解用友軟件可識別的標(biāo)準(zhǔn)格式TXT文件。標(biāo)準(zhǔn)格式TXT文件內(nèi)容如下:第1行數(shù)據(jù)“填制憑證,V800”,第2行及以下數(shù)據(jù)“日期,類型,憑證號,附件張數(shù),摘要,科目,借方,貸方,數(shù)量,外幣,匯率,制單人,結(jié)算方式,發(fā)票號碼,發(fā)生日期,部門,個人,客戶,供應(yīng)商”。
其中,“填制憑證,V800”是文件標(biāo)識,表示該文件可被用友U8軟件識別且執(zhí)行“填制憑證”功能,該標(biāo)識對每一個TXT文件只有一個,位于文件第一行。標(biāo)識以下是該文件所包含的會計分錄信息,其中:“日期”指填制憑證的日期,格式為“XXXX-XX-XX”;“類型”指憑證類型,值為“現(xiàn)”“銀”“轉(zhuǎn)”;“科目”指分錄中對應(yīng)的科目編碼;“借方”“貸方”指借貸金額,每行數(shù)據(jù)只能包含“借方”“貸方”二者之一;“部門”“個人”“客戶”“供應(yīng)商”均指該項對應(yīng)編碼。在標(biāo)準(zhǔn)格式TXT文件中,每一行數(shù)據(jù)描述分錄中某一會計科目的各項信息,由此不難得出,上文中提到的會計分錄轉(zhuǎn)換成標(biāo)準(zhǔn)格式TXT文件如下:
20X4-1-2,"轉(zhuǎn)","1",1,"銷售B公司商品2#1002","1122",234.00,,,,,"張三",,"01002",,"","","11B","",
20X4-1-2,"轉(zhuǎn)","1",1,"銷售B公司商品2#1002","6001",,200.00,,,," 張三",,"",,"","","","",
20X4-1-2,"轉(zhuǎn)","1",1,"銷售B公司商品2#1002","22210105",,34.00,,,," 張三",,"",,"","","","",
通過以上的分析得出,對于“發(fā)票數(shù)據(jù)”中的每行數(shù)據(jù),在產(chǎn)生的標(biāo)準(zhǔn)格式TXT文件中對應(yīng)3行數(shù)據(jù)??紤]構(gòu)造一個輔助表格(簡稱為輔助表),在該表格的第1行A~S列依次輸入:“日期”“類型”“憑證號”“附件張數(shù)”“摘要”“科目”“借方”“貸方”“數(shù)量”“外幣”“匯率”“制單人”“結(jié)算方式”“發(fā)票號碼”“發(fā)生日期”“部門”“個人”“客戶”“供應(yīng)商”,假設(shè)其所在工作表名為“憑證”。“憑證”的格式已類似于標(biāo)準(zhǔn)格式TXT文件,只要調(diào)用VBA中文件操作的相關(guān)函數(shù),依次將表中的數(shù)據(jù)寫入TXT文件中即可。
再次,考慮如何建立“發(fā)票數(shù)據(jù)”與“憑證”的聯(lián)系。不難看出,“發(fā)票數(shù)據(jù)”中的第7行數(shù)據(jù),對應(yīng)“憑證”中第2、第3、第4行數(shù)據(jù),分析這種對應(yīng)關(guān)系,得出“發(fā)票數(shù)據(jù)”中的第i行數(shù)據(jù)(i>=7),對應(yīng)“憑證”中的第3*i-19,3*i-18,3*i-17行數(shù)據(jù)。以“日期”為例,“發(fā)票數(shù)據(jù)”中“開票日期”在G列,“憑證”中“日期”在A列,假定憑證填制日期為開票日期,對于“發(fā)票數(shù)據(jù)”中的第i行數(shù)據(jù),則有:
Sheets(“憑證”).Cells(3*i-19,1)= Sheets("發(fā)票數(shù)據(jù)").Cells(i,7)
Sheets(“憑證”).Cells(3*i-18,1)= Sheets("發(fā)票數(shù)據(jù)").Cells(i,7)
Sheets(“憑證”).Cells(3*i-17,1)= Sheets("發(fā)票數(shù)據(jù)").Cells(i,7)
“=”為賦值符號,含義為將等號右邊的值賦給等號左邊。
再來看一下借貸方金額的表示,“發(fā)票數(shù)據(jù)”中 N7單元格對應(yīng)“憑證”中H3單元格,P7單元格對應(yīng)H4單元格,N7與P7單元格數(shù)值加和對應(yīng)G2單元格,于是對于“發(fā)票數(shù)據(jù)”中的第i行數(shù)據(jù),則有:
Sheets(“憑證”).Cells(3*i-19,7)= Sheets("發(fā)票數(shù)據(jù)").Cells(i,14)+Sheets("發(fā)票數(shù)據(jù)").Cells(i,16)
Sheets”憑證”).Cells(3*i-18,8)= Sheets("發(fā)票數(shù)據(jù)").Cells(i,14)
Sheets(“憑證”).Cells(3*i-17,8)= Sheets("發(fā)票數(shù)據(jù)").Cells(i,16)
同理,也可建立其他數(shù)據(jù)之間的對應(yīng)關(guān)系。
最后,看一下如何將“憑證”中的數(shù)據(jù)輸出到TXT文件。VBA中的“Print #語句”可以將EXCEL中的數(shù)據(jù)順序?qū)懭胛募?,具體語法可參考VBA書籍。對于“憑證”中每一行數(shù)據(jù),連接規(guī)則是:同行中不同單元格數(shù)據(jù)用“&”連接,保證其在輸出TXT文件中位于一行;不同單元格之間以“,”分割,但若單元格值為字符串,則該單元格前后需添加“””。以Line2表示第2行數(shù)據(jù),SDH表示"," ,SYH表示"""",則:
Line2 = Sheets("憑證").Cells(2, 1) &SDH
Line2 = Line2&SYH& Sheets("憑證").Cells(2, 2).&SYH&SDH
以此類推,通過循環(huán)賦值,可將第2行所有單元格的值賦給Line2,完成賦值后,使用Print#語句可將其輸出到文件。
三、數(shù)據(jù)轉(zhuǎn)換接口設(shè)計實例
以上分析厘清了接口制作的思路,并對制作過程中的關(guān)鍵點作了說明,為了更直觀地了解整個過程,下面提供一個簡化案例作為展示,對該案例,作如下假設(shè):
1.XX公司每賣一批商品開具一張發(fā)票,開票系統(tǒng)中導(dǎo)出的工作表名稱為“發(fā)票數(shù)據(jù)”,“發(fā)票數(shù)據(jù)”所在工作簿含有名稱為“憑證”的工作表。
2.XX公司根據(jù)每張發(fā)票生成1張記賬憑證,憑證類型為“轉(zhuǎn)”,附件張數(shù)為2,填制日期為“開票日期”,制單人為“張三”。
3.XX公司出售商品為主營業(yè)務(wù),主營業(yè)務(wù)科目編碼為6001,公司應(yīng)收賬款按客戶分類核算,應(yīng)收賬款科目編碼為1122,客戶編碼為“購方稅號”。
根據(jù)以上假設(shè),做如下操作,打開存放開票信息的EXCEL表格,使用“Alt+F11”快捷鍵,進(jìn)入VBA界面,點擊“插入\模塊”子菜單,在插入的代碼輸入窗口中輸入以下代碼:endprint
Sub 數(shù)據(jù)轉(zhuǎn)換接口()
Dim Line_head As String
Dim sLine As String
Dim sFName As String
Dim iFNnumber As Integer
Const SDH As String = ","
Const SYH As String = """"
On Error Resume Next
sFName = "E:\憑證引入.txt"
sLine_head = "填制憑證,V800"
hmax = Sheets("發(fā)票數(shù)據(jù)").Range("A65535"). End(xlUp).Row
With Sheets("憑證")
For i = 7 To hmax
.Cells(3 * i - 19, 6) = "2211"
.Cells(3 * i - 18, 6) = "6001"
.Cells(3 * i - 17, 6) = "22210105"
.Cells(3 * i - 19, 7) = Sheets("發(fā)票數(shù)據(jù)").Cells(i, 14)
.Cells(3 * i - 19, 7) =.Cells(3 * i - 19, 7) + Sheets("發(fā)票數(shù)據(jù)").Cells(i, 16)
.Cells(3 * i - 18, 8) = Sheets("發(fā)票數(shù)據(jù)").Cells(i, 14)
.Cells(3 * i - 17, 8) = Sheets("發(fā)票數(shù)據(jù)").Cells(i, 16)
.Cells(3 * i - 19, 14) = Sheets("發(fā)票數(shù)據(jù)").Cells(i, 2)
.Cells(3 * i - 19, 18) = Sheets("發(fā)票數(shù)據(jù)").Cells(i, 4)
For j = 0 To 2
.Cells(3 * i + j - 19, 1) = Sheets("發(fā)票數(shù)據(jù)").Cells(i, 7)
.Cells(3 * i + j - 19, 2) = "轉(zhuǎn)"
.Cells(3 * i + j - 19, 3) = i - 6
.Cells(3 * i + j - 19, 4) = 2
.Cells(3 * i + j - 19, 5) = "銷售" & Sheets("發(fā)票數(shù)據(jù)").Cells(i, 3)
.Cells(3 * i + j - 19, 5)= .Cells(3 * i + j - 19, 5) & Sheets("發(fā)票數(shù)據(jù)").Cells(i, 9)
.Cells(3 * i + j - 19, 12) = "張三"
Next j
Next i
iFNnumber = FreeFile
Open sFName For Output As #iFNnumber
Print #iFNnumber, sLine_head
t = 2
Do
sLine = .Cells(t, 1) & SDH
sLine = sLine & SYH & .Cells(t, 2) & SYH & SDH
sLine = sLine & SYH & .Cells(t, 3) & SYH & SDH
sLine = sLine & .Cells(t, 4) & SDH
sLine = sLine & SYH & .Cells(t, 5) & SYH & SDH
sLine = sLine & SYH & .Cells(t, 6) & SYH & SDH
sLine = sLine & Format(.Cells(t, 7), "0.00") & SDH
sLine = sLine & Format(.Cells(t, 8), "0.00") & SDH
sLine = sLine & .Cells(t, 9) & SDH
sLine = sLine & .Cells(t, 10) & SDH
sLine = sLine & .Cells(t, 11) & SDH
sLine = sLine & SYH & .Cells(t, 12) & SYH & SDH
sLine = sLine & .Cells(t, 13) &S DH
sLine = sLine & SYH & .Cells(t, 14) & SYH & SDH
sLine = sLine & .Cells(t, 15) & SDH
sLine = sLine & SYH & .Cells(t, 16) & SYH & SDH
sLine = sLine & SYH & .Cells(t, 17) & SYH & SDHendprint
sLine = sLine & SYH & .Cells(t, 18) & SYH & SDH
sLine = sLine & SYH & .Cells(t, 19) & SYH & SDH
Print #iFNnumber, sLine
t = t + 1
Loop Until IsEmpty(.Cells(t, 1))
End With
Close #iFNnumber
End Sub
點擊“保存”按鈕,關(guān)閉VBE界面,點擊“開發(fā)工具\代碼\宏”,在彈出的對話框中選中“數(shù)據(jù)轉(zhuǎn)換接口”,點擊“執(zhí)行”,即可看到處理后的工作表,并且在E盤下找到“憑證引入.txt”文件。利用VBA的封裝技術(shù)可以將以上的代碼封裝成dll文件或EXE文件,注冊后即以按鈕的形式存在于EXCEL選項卡中,點擊按鈕即可實現(xiàn)相應(yīng)功能,在此不作詳述。
點擊“用友U890\系統(tǒng)服務(wù)\總賬工具”,打開總賬工具界面,在“數(shù)據(jù)源\文件數(shù)據(jù)”處選擇“E:\憑證引入.txt”,雙擊“憑證引入”,即可將憑證引入到用友軟件中。
四、小結(jié)
本文利用Excel VBA在處理表格方面的優(yōu)越特性,將新版防偽開票子系統(tǒng)導(dǎo)出的數(shù)據(jù)加工處理,生成了能直接導(dǎo)入用友軟件的標(biāo)準(zhǔn)格式TXT文件,減輕了會計人員賬務(wù)處理的負(fù)擔(dān)。然而,針對該問題本文更多地是提供一種處理思路,雖然文中給出了一個案例的完整代碼,但所用案例建立在眾多假設(shè)之上。在實際會計業(yè)務(wù)中,這些假設(shè)不一定全部滿足,需要具體問題具體分析。盡管如此,本文提出的處理思路仍具有重要的理論價值和應(yīng)用價值,各企業(yè)可根據(jù)企業(yè)的實際情況,結(jié)合VBA的強大功能,設(shè)計出適合于自己企業(yè)的數(shù)據(jù)轉(zhuǎn)換接口。
參考文獻(xiàn)
[1] 郭剛.Excel VBA 入門與應(yīng)用典型實例[M].北京:北京希望電子出版社,2009.
[2] 韓良智.Excel 在財務(wù)管理與分析中的應(yīng)用[M].北京:中國水利水電出版社,2004.
[3] 孫裕亮,等.增值稅防偽稅控系統(tǒng)設(shè)計存在的問題及改進(jìn)措施[J].財會月刊,2012(3).endprint