用VBA实现防伪开票系统数据导入用友软件

freelie

[原创] 用VBA实现防伪开票系统数据导入用友软件http://club.excelhome.net/thread-1151846-1-1.html (出处: ExcelHome技术论坛) 看了cf7315752大大发的帖子对于我很有用处。但是里面生成的凭证对于日常做帐有些不方便，求大神能帮忙修改为“期望”表里面的格式，万分感谢！

CAOJIADU

Sub 数据转换接口()
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"
    Sheets("凭证").Select
    Rows("2:2057").Select
    Selection.ClearContents



hmax = Sheets("发票数据").Range("A65535").End(xlUp).Row



With Sheets("凭证")
For I = 7 To hmax
.Cells(1 * I - 5, 6) = "1122"
.Cells(1 * I - 5, 7) = Sheets("发票数据").Cells(I, 14)
.Cells(1 * I - 5, 14) = Sheets("发票数据").Cells(I, 2)
.Cells(1 * I - 5, 18) = Sheets("发票数据").Cells(I, 4)
For j = 0 To 0
.Cells(1 * I + j - 5, 1) = Sheets("发票数据").Cells(I, 7)
.Cells(1 * I + j - 5, 2) = "转"
.Cells(1 * I + j - 5, 3) = Sheets("发票数据").Cells(1, 1)
.Cells(1 * I + j - 5, 4) = Sheets("发票数据").Cells(1, 2)
.Cells(1 * I + j - 5, 5) = "销售" & Sheets("发票数据").Cells(I, 3)
.Cells(1 * I + j - 5, 5) = .Cells(1 * I + j - 5, 5) & Sheets("发票数据").Cells(I, 9)
.Cells(1 * I + j - 5, 12) = "张三"
Next j

Next I
.Cells(I - 5, 1) = "=TODAY()"
.Cells(I - 5, 2) = "转"
.Cells(I - 5, 3) = Sheets("发票数据").Cells(1, 1)
.Cells(I - 5, 4) = Sheets("发票数据").Cells(1, 2)
.Cells(I - 5, 5) = "确认主营收入"
.Cells(I - 5, 6) = "6001"
.Cells(I - 5, 8) = "=ROUND(SUM(发票数据!R[-15]C[6]:R[271]C[6])-SUM(发票数据!R[-16]C[8]:R[271]C[8]),2)"

.Cells(I - 4, 1) = "=TODAY()"
.Cells(I - 4, 2) = "转"
.Cells(I - 4, 3) = Sheets("发票数据").Cells(1, 1)
.Cells(I - 4, 4) = Sheets("发票数据").Cells(1, 2)
.Cells(I - 4, 5) = "确认增值税"
.Cells(I - 4, 6) = "222105"

.Cells(I - 4, 8) = "=ROUND(SUM(发票数据!R[-16]C[8]:R[271]C[8]),2)"


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) & SDH
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 & SDH
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

freelie

谢谢大神，不过我修改了几个小地方，望大神指证
.Cells(1 * I - 5, 7) = Sheets("发票数据").Cells(I, 14) + Sheets("发票数据").Cells(I, 16)
.Cells(I - 5, 8) = "=ROUND(SUM(发票数据!N:N),2)"
.Cells(I - 4, 8) = "=ROUND(SUM(发票数据!P:P),2)"

freelie

CAOJIADU 发表于 2017-12-9 15:00
Sub 数据转换接口()
Dim Line_head As String
Dim sLine As String

谢谢大神，不过我修改了几个小地方，望大神指证
.Cells(1 * I - 5, 7) = Sheets("发票数据").Cells(I, 14) + Sheets("发票数据").Cells(I, 16)
.Cells(I - 5, 8) = "=ROUND(SUM(发票数据!N:N),2)"
.Cells(I - 4, 8) = "=ROUND(SUM(发票数据!P:P),2)"