|
- Option Explicit
- Sub tt()
- On Error Resume Next
- Dim 最后行号, i, m, start1, end1 As Integer
- Dim Word对象 As New Word.Application ', Fso As New FileSystemObject
- Dim Str1, Str2, 当前路径, 导出路径, 导出文件名, 导出路径文件名, 邮编, 地址, 联系人, 联系人电话, 缴费号, 申请人 As String
- 最后行号 = Sheets("Sheet1").Range("C65536").End(xlUp).Row
- For i = 6 To 最后行号
- start1 = i '根据不同缴费单号分类
- While Sheets("Sheet1").Cells(i, "AC") = Sheets("Sheet1").Cells(i + 1, "AC")
- i = i + 1
- Wend
- end1 = i
- With Sheets("Sheet1")
- Sheets("Sheet2").Rows("2:65536").Clear
- .Range(Cells(start1, "E"), Cells(end1, "G")).Copy Sheets("Sheet2").Range("B2")
- .Range(Cells(start1, "A"), Cells(end1, "A")).Copy
- Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValues
- Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormats
-
- .Range(Cells(start1, "AO"), Cells(end1, "AO")).Copy '总计
- Sheets("Sheet2").Range("H2").PasteSpecial Paste:=xlPasteValues
- Sheets("Sheet2").Range("H2").PasteSpecial Paste:=xlPasteFormats
-
- .Range(Cells(start1, "I"), Cells(end1, "J")).Copy '官费和服务费
- Sheets("Sheet2").Range("E2").PasteSpecial Paste:=xlPasteValues
- Sheets("Sheet2").Range("E2").PasteSpecial Paste:=xlPasteFormats
-
- .Range(Cells(start1, "N"), Cells(end1, "N")).Copy '缴费期限
- Sheets("Sheet2").Range("G2").PasteSpecial Paste:=xlPasteValues
- Sheets("Sheet2").Range("G2").PasteSpecial Paste:=xlPasteFormats
-
- .Range(Cells(start1, "P"), Cells(end1, "P")).Copy '专利名称
- Sheets("Sheet2").Range("T2").PasteSpecial Paste:=xlPasteValues
- Sheets("Sheet2").Range("T2").PasteSpecial Paste:=xlPasteFormats
-
- .Range(Cells(start1, "AD"), Cells(end1, "AL")).Copy Sheets("Sheet2").Range("I2")
- .Range(Cells(start1, "AC"), Cells(end1, "AC")).Copy Sheets("Sheet2").Range("R2")
-
- .Cells(1, "Z") = Application.Sum(Sheets("Sheet2").Range("E2:E65536")) '本期发生官费总金额
- .Cells(1, "X") = Application.Sum(Sheets("Sheet2").Range("F2:F65536")) '本期发生服务费总金额
- .Cells(1, "V") = .Cells(1, "Z") + .Cells(1, "X") '本期应缴总额
- .Range(Cells(1, "U"), Cells(1, "V")).Copy Sheets("Sheet2").Cells(end1 - start1 + 3, "A")
- End With
- Sheets("Sheet2").UsedRange.Borders.ColorIndex = 0 '已使用的范围,即有内容的表格添加黑色边框线
-
- Sheets("Sheet2").UsedRange.Copy
- 邮编 = Sheets("Sheet1").Cells(start1, "T")
- 地址 = Sheets("Sheet1").Cells(start1, "U")
- 联系人 = Sheets("Sheet1").Cells(start1, "V")
- 联系人电话 = Sheets("Sheet1").Cells(start1, "W")
- 申请人 = Sheets("Sheet1").Cells(start1, "C")
-
- 当前路径 = "\\192.168.1.120\杭诚软件\缴费单"
- 导出路径 = 当前路径 & "\已生成" & Format(Now, "yyyy-mm-dd")
- MkDir 导出路径
- 导出文件名 = Cells(i, "AC") & ".doc"
-
- 导出路径文件名 = 导出路径 & "" & 导出文件名
- 'If Fso.FileExists(导出路径文件名) = True Then Fso.DeleteFile 导出路径文件名, True
- FileCopy 当前路径 & "\缴纳授权费用模板实样.doc", 导出路径文件名
- With Word对象
- .Documents.Open 导出路径文件名
- .Visible = True
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[当前时间]"
- .Selection.Find.Execute
- .Selection = Format(Now, "yyyy-mm-dd")
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[缴费号]"
- .Selection.Find.Execute
- .Selection = Sheet2.Cells(2, "R")
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[收款账户]"
- .Selection.Find.Execute
- ' .Selection = Cells(1, "J")
- .Selection = Sheet2.Cells(2, "J")
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[银行账号]"
- .Selection.Find.Execute
- ' .Selection = Cells(1, "L")
- .Selection = Sheet2.Cells(2, "K")
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[开户银行]"
- .Selection.Find.Execute
- ' .Selection = Cells(2, "K")
- .Selection = Sheet2.Cells(2, "L")
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[公司地址]"
- .Selection.Find.Execute
- ' .Selection = Cells(1, "P")
- .Selection = Sheet2.Cells(2, "M")
-
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[公司邮编]"
- .Selection.Find.Execute
- ' .Selection = Cells(2, "J")
- .Selection = Sheet2.Cells(2, "N")
-
- ' .Selection.HomeKey Unit:=wdStory
- ' .Selection.Find.Text = "[公司电话]"
- ' .Selection.Find.Execute
- '' .Selection = Cells(2, "L")
- ' .Selection = Sheet2.Cells(2, "O")
- '
- ' .Selection.HomeKey Unit:=wdStory
- ' .Selection.Find.Text = "[公司传真]"
- ' .Selection.Find.Execute
- '' .Selection = Cells(2, "N")
- ' .Selection = Sheet2.Cells(2, "P")
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[收款户名]"
- .Selection.Find.Execute
- ' .Selection = Cells(2, "N")
- .Selection = Sheet2.Cells(2, "J")
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[邮编]"
- .Selection.Find.Execute
- .Selection = 邮编
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[地址]"
- .Selection.Find.Execute
- .Selection = 地址
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[联系人]"
- .Selection.Find.Execute
- .Selection = 联系人
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[联系人电话]"
- .Selection.Find.Execute
- .Selection = 联系人电话
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[申请人]"
- .Selection.Find.Execute
- .Selection = 申请人
-
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[缴费号]"
- .Selection.Find.Execute
- .Selection = 缴费号
- .Selection.HomeKey Unit:=wdStory
- .Selection.Find.Text = "[表格处]"
- .Selection.Find.Execute
- .Selection.PasteExcelTable False, False, False
-
- With .Selection.Find '替换连续的手动换行符
- .Text = "^l^l"
- .Replacement.Text = "^p"
- .MatchWildcards = False
- .Execute Replace:=wdReplaceAll, Forward:=True, _
- Wrap:=wdFindContinue
- End With
-
- End With
-
- Word对象.Documents.Save
- Word对象.ActiveDocument.PrintOut
- Word对象.Documents.Close
- Next i
- Set Word对象 = Nothing
- Word对象.Quit
- End Sub
- 这段代码是调用系统模板打印的代码 ,问题就是不知道怎么原因,打到7-8分的时候就会出现莫名其妙的错误,格式变了,不知道是什么问题 还请指点一下~~!!
复制代码 |
|