【VBA研究】打印银行对账单（套打）

iamlaosong文

根据需要打印银行对账单，打印方式是套打（这种应用很多，比如快递单、发票等），现就有关技术问题进行总结。

1、如何实现准确套打

1）将对账单样式图片插入excel，略微调整大小为一张A4纸大小，插入图片的目的是为了准确定位套打位置，图片本身不用打印，需设置为不打印（套打需要先印刷，后打印）。如果不是套打，则打印图片本身。方法是右击图片，点击菜单“大小和属性”，去掉打印对象勾选，如下图：

2）设置打印区域，即将对账单图片所在区域设为打印区域，其它地方就不会打印了，这样做的目的是只要发出打印命令就可以打印，不用指定打印哪一页这些麻烦事，其它单元格有内容也没关系。套打如果位置有差，可适当调整控件位置适应。设置打印区域方法是先选择需要打印的区域，点击菜单“页面布局”-“打印区域”-“设置打印区域”，如下图：

3）将需要打印的数据复制到指定区域（VBA程序根据指定的行从数据表中复制到A42:Z46），在对账单需要套打的地方插入文本框，并将文本框连接到对应的单元格提取数据。这里可能用到文本框的对齐、分布，如果打印的和显示的不同，可适当调整文本框的边距。如下图：

4）套打时，如个别位置有差，可调整文本框控件位置，如所有的控件位置都有差，则调整页边距，以保证文本框和图片基本适应，因为图片本就是为套打定位的。

 

 

5）打印功能有单个打印和连续打印，如下图：

 

'打印指定行的数据：excel是可以设置打印区域的。
'将A1:K40设置为打印区域，则只会打印一页，就不用指定从哪打到哪了。
'单行打印
Sub SinglePrint()
    Dim rr As Long
    
    rr = Cells(2, "O")
    rr = GetRowData(rr)
    'Worksheets.("sheet1").PrintOut From:=2, To:=3, Copies:=3
    'ActiveSheet.PrintPreview
    msg = GenPic(0)
    ActiveSheet.PrintOut
End Sub

'多行打印
Sub MutiPrint()
    Dim rr1, rr2, rr As Long, msg As String
    
    rr1 = Cells(5, "O")
    rr2 = Cells(5, "P")
    For rr = rr1 To rr2
        rr = GetRowData(rr)
        'Worksheets.("sheet1").PrintOut From:=2, To:=3, Copies:=3
        'ActiveSheet.PrintPreview
        msg = GenPic(0)
        ActiveSheet.PrintOut
    Next rr
End Sub

'指定全部行号
Sub GetRows()
    Dim stName As String
    
    stName = Cells(2, "P")
    Cells(5, "O") = 2
    Cells(5, "P") = Sheets(stName).[A1].End(xlDown).Row
End Sub

2、二维码生成

对账单需要生成对账用的二维码（易码），银行提供了接口函数（通过DLL文件），但VBA调用不成功，后将其提供的Demo程序改造成控制台命令，通过命令行参数传递函数需要的数据，从而实现了二维码的生成。VBA执行DOS命令还是很简单的，有两种方式，其一是通过"Wscript.shell"对象的exec方法执行，其二是用shell命令完成（或者通过"Wscript.shell"对象的run方法执行，效果一样，shell对双引号的支持更好 , run则需要添加额外的双引号或chr(34)）。两种方法各有优缺点，第一种方法可以读取屏幕上的执行结果，以便判断执行情况，但执行时会有DOS弹窗一闪；第二种方法则可以隐藏窗口，不会出现闪窗，但无法得到执行情况。需要注意的是，shell命令是重建一个进程完成的（异步执行），所以VBA并不会等这个命令执行完成再执行下面一个语句。本应用中需要命令生成的二维码，所以执行shell命令后需要一个延时，以便等待二维码生成结束（网上有根据返回的进程号查询系统该进程是否还在运行来判断是否结束，但比较麻烦，本应用只是生成一个二维码，时间很短，一个延时就够了）。

    '图片生成模式
    If mode = 0 Then
        'mode=0,有弹窗，但可以根据弹窗的内容确定命令执行情况
        Set oShell = CreateObject("Wscript.shell")
        Set oExec = oShell.exec(strCmd)
        'result = vbaPing(Ip)
        Do Until oExec.stdout.AtEndOfStream
            result = result & oExec.stdout.readline() & Chr(13)
        Loop
        'Debug.Print result
        '反馈：编码成功或者编码失败
        If InStr(result, "编码成功") > 0 Then
            UpdatePic
            GenPic = "OK"
        Else
            MsgBox "编码失败，请检查原因！"
            GenPic = "Fail"
        End If
    Else
        'shell执行命令，隐藏弹窗。mode的数值作为延时
        Shell strCmd, vbHide
        tim1 = Timer
        Do While Timer < tim1 + mode
        Loop
        '无反馈：不论编码成功还是编码失败
        UpdatePic
        GenPic = "OK"
    End If

shell命令也可以用下面语句代替：

        Set oShell = CreateObject("Wscript.shell")
        rs = oShell.Run(strCmd, vbHide, True)

通过WScript调用命令，有两种方法run和exec，这两种方法的区别如下：

run有一个等待程序完成的参数 , exec则可以返回执行的对象, run返回一个整数，这两个方法的声明如下：
Function Exec(ByVal Command As String) As WshExec

Function Run(ByVal Command As String, [ByVal WindowStyle], [ByVal WaitOnReturn]) As Integer

可以看出几点区别：

（1）run的返回值是一个整数，就是0或1成功和失败两个状态，而exec方法的返回值是一个对象，从返回对象中可以获得控制台输出信息和控制台错误信息，即StdOut和StdErr属性等。例如：

Set exeRs = objws.Exec("pscp -r -scp -pw 。。。。"

errMsg = exeRs.StdErr.ReadAll()

stdMsg = oExec.StdOut.ReadAll()

可以取到控制台错误和控制台信息，本程序就是通过这个方法判断执行结果。

（2）Run 的后两个参数，一个是cmd窗口的风格，一个是是否等待执行完成。最后一个参数很有用，如果你希望等待本次cmd执行的程序结束后，在执行 objws.Run后面的语句的话，只要设置这个参数为true就可以了，否则后面的语句将不等待cmd窗口完成，直接运行（我们程序就有这个需求，需要生成二维码图片后，再更新到Excel工作表图像控件中，如果没有生成完就去更新图片会出错的）。另外，如果你使用exec 方法的时候，如果希望等待cmd中程序执行完后，在执行后面的语句，也可以通过下面的方法：

oExec.StdErr.ReadAll()或者oExec.StdOut.ReadAll（），道理上也应该好理解，要得到输出的信息，肯定要cmd执行完后才会有输出的。

3、更新图片

 

每张对账单有不同的二维码，在打印时生成并更新到对账单上面，代码如下：

'更换图片
Private Sub UpdatePic()
    Dim Pic As Object, PicName As String
    Dim PicT, PicL, PicH, PicW As Integer
    '所选图片路径
    ActiveSheet.Unprotect
    PicName = ThisWorkbook.Path & "\YiCode.bmp"
    Set Pic = ActiveSheet.Shapes("图片 2")
    '原图片的位置和大小：纵横比属性 .LockAspectRatio
    With Pic
        PicT = .Top
        PicL = .Left
        PicH = .Height
        PicW = .Width
    End With
    '删除原图片
    Pic.Delete
    '插入所选图片
    Set Pic = ActiveSheet.Shapes.AddPicture(Filename:=PicName, LinkToFile:=msoFalse, _
          SaveWithDocument:=msoTrue, Left:=PicL, Top:=PicT, Width:=PicW, Height:=PicH)
    '设置图片名称
    Pic.Name = "图片 2"
    Pic.Locked = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    'Set Pic = Nothing
End Sub

4、插入图片和更改图片名称。应用本身不需要，但调试过程中可能会用到，代码如下：

'插入图片
Private Sub InsertPic()
    'On Error Resume Next
    Dim Pic As Object, PicName As String
    
    PicName = ThisWorkbook.Path & "\YiCode.bmp"
    '参数：path,left,top,width,height(宽和高为-1表示用原始尺寸)
    Set Pic = ActiveSheet.Shapes.AddPicture(Filename:=PicName, LinkToFile:=msoFalse, _
          SaveWithDocument:=msoTrue, Left:=60, Top:=60, Width:=-1, Height:=-1)
    Pic.LockAspectRatio = msoTrue     '锁定高宽比例
    Pic.ScaleHeight 0.5, msoTrue      '高度缩为一半
    'ActiveSheet.Shapes.AddPicture PicName, True, True, 60, 60, 127, 127
End Sub

'更换图片名称
Private Sub SetPicName()
    Dim Pic As Object
    
    Set Pic = ActiveSheet.Shapes("图片 6")
    Pic.Name = "图片 2"
    Set Pic = Nothing
End Sub

附：VB Shell函数用法


示例代码：
1． 打开记事本
   Private Sub Command1_Click()
    Shell "Notepad E:\VB练习\添加DTPicker控件.txt ", vbNormalFocus
   End Sub
注：NotePad后有空格
2． 打开QQ登陆界面
   Private Sub Command2_Click()
    Shell "D:\Program Files\Tencent\QQ2009\Bin\qq.exe", vbNormalFocus
   End Sub
3． 打开Word文档
   Private Sub Command3_Click(Index As Integer)
   Dim strDir As String
   strDir = "E:\VB练习\Test.doc"
   Select Case Index
   Case 0
         Shell "C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE " & strDir, vbHide
   Case 1
         Shell "C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE " & strDir, vbNormalFocus
   Case 2
         Shell "C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE " & strDir, vbMinimizedNoFocus
   Case 3
         Shell "C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE " & strDir, vbNormalNoFocus
   Case 4
         Shell "C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE " & strDir, vbMaximizedFocus
   End Select
   End Sub
注：C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE之后有空格
 
* 在开始->运行里面能执行的命令用shell函数也行
 
Shell的语法：Shell(PathName[，WindowStyle])。
 
　　PathName为必需参数。类型为String，它指出了要执行的程序名，以及任何需要的参数或命令行变量，也可以包括路径名。
 
　　WindowStyle为可选参数。Integer类型，指定在程序运行时窗口的样式。WindowStyle有以下这些值。
 
　　常量                   值        描述
 
VbHide                      0      窗口被隐藏，且焦点会移到隐式窗口。
 
VbNormalFocus         1      窗口具有焦点，且会还原到它原来的大小和位置。
 
VbMinimizedFocus     2      窗口会以一个具有焦点的图标来显示（缺省值）。
 
VbMaximizedFocus     3      窗口是一个具有焦点的最大化窗口。
 
VbNormalNoFocus     4      窗口会被还原到最近使用的大小和位置，而当前活动的窗口仍然保持活动。
 
VbMinimizedNoFocus 6     窗口会以一个图标来显示，而当前活动的窗口仍然保持活动。

 

总结一下，Shell就是一个打开应用程序的函数，所带的参数为样式参数。