注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

早衰男的巢

大音希声 道隐无名

 
 
 

日志

 
 

EXCEL(VBA):自动套用模板批量打印快递单  

2016-05-18 16:23:40|  分类: others note. |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
甲方需求:批量打印快递单 代替手写提高效率
试过若干办法,如果连续供稿不切断而用网页脚本来打印输出,对打印参数(板式、边框、页面尺寸)订制较弱,而且浏览器还得弹出预览要逐页确定,更大的问题是..不同浏览器排版输出还有偏差。在上述诸多的问题下,退而求其次,选择导出报表(csv之类) 进入excel 批量打印。找到了网上一些现成的excel打印模板..觉得做的不错的一个..额锁了..要注册码,淘宝卖36块一年。好吧..自己研究写

(废话完,入正题)
实现目标:
1. 一个打印模板
2. 一个数据表
3. 用vba实现以下3个功能按键
    3.1 一键打印数据表第一行(从数据表第一行提取数据,套用在打印模板上,然后输出打印)
    3.2 一键打印选中的N行数据(上一个按钮的强化,获取当前选中行数,循环打印,需要跳过无数据空行)
    3.3 调试按钮,显示当前选中的区域,(坐标和行数)

设计打印模板(例为 韵达快递)

1. 先设置好打印机的页面尺寸,本例用尺子量了是 21cm*12.7cm
2. 新建一个sheet 名为 【打印模板】
3. 设置打印区域 (参照的参考虚线),下文会说明这个有什么作用
4. 拉入5个文本框(比表格定位更为方便),分别命名为 姓名、城市、地址、手机、订单ID 调整好字体大小和位置
5. 逐个文本框选中、设置边框为无边框(否则打印会输出边框)
EXCEL(VBA):自动套用模板批量打印快递单 - 恶 - 早衰男的巢
 

6 新建sheet 【数据表】 如图,ID,姓名,电话,城市,地址
7 新建 开发 - 表单组件 - 按钮(我的excel默认没有,从自定义菜单里添加进来的),按钮文本修改为 “打印第一行”
8 点录制宏,依次进行以下操作
   点选【打印模板】;
   选中文本框"姓名",在值的输入栏输入“=”,然后切回【数据表】点$A$2 回车,引用数据表第一行的姓名的值
   如此把其余几个文本框也进行引用,除了订单ID
   点选【打印模板】,在打印区域以外(本例选择$P$2随便选的),值输入栏输入 ="#"&数据表!$D$2 回车
   选中文本框“订单ID”,值输入框输入 =$P$2
   *上面步骤是因为需要在快递单打印输出 “#888” 这样格式的,要在订单号前加上“#” 如果直接在文本框进行 =“#”&sheet!$R$C 是不支持的,会包括“无法找到区域引用或定义的名称”,所以通过在打印区域外的一个单元格来进行跨页引用后再引用到文本框曲线解决这个问题。

EXCEL(VBA):自动套用模板批量打印快递单 - 恶 - 早衰男的巢
 

9 完成后点打印(根据自己的打印机进行打印参数的调整),点打印。
10 最后点,结束录制宏。
这样在旁边的 VBA编辑窗上就会多出一大串像下面这样的宏脚本

Sub 打印第一行()

i = 2 '标题行不算
Sheets("打印区域设置表").Select
ActiveSheet.Shapes.Range(Array("姓名框")).Select
'Selection.Formula = "=数据表!$C$2"
Selection.Formula = "=数据表!$C$" & i

ActiveSheet.Shapes.Range(Array("城市框")).Select
'Selection.Formula = "=数据表!$E$2"
Selection.Formula = "=数据表!$E$" & i

ActiveSheet.Shapes.Range(Array("地址框")).Select
'Selection.Formula = "=数据表!$F$2"
Selection.Formula = "=数据表!$F$" & i

ActiveSheet.Shapes.Range(Array("手机框")).Select
'Selection.Formula = "=数据表!$D$2"
Selection.Formula = "=数据表!$D$" & i


Range("P2").Select
'ActiveCell.Formula = "=""#""&数据表!A2"
ActiveCell.Formula = "=""#""&数据表!A" & i

ActiveSheet.Shapes.Range(Array("ORDERID")).Select
Selection.Formula = "=$P$2"

’打印
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$20"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 180
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = 122
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
'
' 执行打印
'
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub


可以看到脚本主要分成两部分,第一部分对模板赋值,第二部分设置打印参数,最后执行打印

(之后经历若干次调整文本框大小位置对准快递单)
这样第一个按钮【打印第一行】就完成了。

11 第二步,输出选中的区域
这个vbs代码就有点简单了

Sub btn_debug()
a = Selection.Row
b = Selection.Rows.Count

MsgBox "start:" & a & ", rows:" & b & ";"
End Sub

运行效果点击按钮,会把当前选中的区域(左上角第一格)的行数(a) 以及选区有多少行(b)显示出来。

12 第三个按钮
前文的两个按钮的功能合并在一起
让选中区域的每一行循环遍历,套用模板打印
为了更完善点,还加上发检测,如果选中的区域是空行(没有订单ID)弹出提示且跳过不执行打印

最终脚本是这样

Sub 打印选中行()

'获得选中区域
a = Selection.Row '选中的起始行
b = Selection.Rows.Count '选中的行数

For i = a To (b + a - 1)

'判断空行跳过不打印
If Worksheets("数据表").Cells(i, 1).Value = "" Then
MsgBox "跳过空行#" & i
Else
'MsgBox "打印" & i & "(" & Worksheets("数据表").Cells(i, 1).Value & ")" 'debug

Sheets("打印区域设置表").Select
ActiveSheet.Shapes.Range(Array("姓名框")).Select
'Selection.Formula = "=数据表!$C$2"
Selection.Formula = "=数据表!$C$" & i

ActiveSheet.Shapes.Range(Array("城市框")).Select
'Selection.Formula = "=数据表!$E$2"
Selection.Formula = "=数据表!$E$" & i

ActiveSheet.Shapes.Range(Array("地址框")).Select
'Selection.Formula = "=数据表!$F$2"
Selection.Formula = "=数据表!$F$" & i

ActiveSheet.Shapes.Range(Array("手机框")).Select
'Selection.Formula = "=数据表!$D$2"
Selection.Formula = "=数据表!$D$" & i


Range("P2").Select
'ActiveCell.Formula = "=""#""&数据表!A2"
ActiveCell.Formula = "=""#""&数据表!A" & i

ActiveSheet.Shapes.Range(Array("ORDERID")).Select
Selection.Formula = "=$P$2"


Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$20"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 180
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = 122
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
'
' 打印
'
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End If

Next

End Sub


到最后记得,把这个文档保存为包含宏脚本的xlsm格式。

完美!

为了省36块,也是够拼

excel的文件已共享在百度盘。如果需要套用 发件人信息,只需要参考上述步骤进行补充完善
 链接: http://pan.baidu.com/s/1qYAFbg4 密码: egk9 
我就不拿来卖钱了xlsm文件免费提供,如果觉得还不错或者有启发~ 可以扫下面QR请我喝杯可乐.哈哈哈

EXCEL(VBA):自动套用模板批量打印快递单 - 恶 - 早衰男的巢
 
  评论这张
 
阅读(534)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018