Excel中批量生成标签,批量打印标签。按指定模板批量打印标签 |
您所在的位置：网站首页 › vba打印标签 › Excel中批量生成标签,批量打印标签。按指定模板批量打印标签 |
Excel中批量生成标签,批量打印标签。按指定模板批量打印标签
|
工作中经常有批量生成标签的场景，有多条数据，需要分别写到标签里面。并且，标签需要进行批量打印。 自己写了个工具，来实现这个功能。可以将三天的工作量缩减到10分钟。 生成的效果如下：
数据源如下：
插件名称：速码工具箱 操作两步即可完成！
适用于已有数据源的情况下，一键生成多个标签。使用场景：固定资产标签、设备管理铭牌、产品生产信息标签卡等等。 插件由VBA转化而来，参考代码如下： Sub 资产卡片() Rem 定义几个必要的变量，最大行数，最大列数，指定表及区域 Dim Rm_lng As Long, Cm_int As Integer, Sh As Worksheet, Kap_Rng As Range Rem 卡片跳跃数，循环变量，将隔行数，卡片数组 Dim Rs_By As Byte, i As Long, ii As Byte, RowC As Long, Arr() Dim New_Sh As Worksheet Set Sh = Worksheets("资产明细") With Sh Set Kap_Rng = Application.Intersect(.Range("a1").CurrentRegion, .UsedRange) With Kap_Rng Rm_lng = .Rows.Count Cm_int = .Columns.Count End With End With Set New_Sh = Worksheets.Add With New_Sh .Columns(1).ColumnWidth = 5.5 .Columns(2).ColumnWidth = 9 .Columns(3).ColumnWidth = 5.5 .Columns(4).ColumnWidth = 9 End With Application.ScreenUpdating = False Rs_By = 1 '每页1个卡片页面，3改3 ReDim Arr(1 To 5, 1 To 2) Arr(1, 1) = "惠州分公司固定资产标签" Arr(2, 1) = "资产编号" Arr(3, 1) = "规格型号" Arr(4, 1) = "管理单位" Arr(5, 1) = "使用地点" For i = 2 To Rm_lng Step Rs_By RowC = RowC + IIf(i = 2, 1, 6) For ii = 1 To Rs_By Arr(2, 2) = Kap_Rng(i + ii - 1, "A").Text Arr(3, 2) = Kap_Rng(i + ii - 1, "C") Arr(4, 2) = Kap_Rng(i + ii - 1, "E") Arr(5, 2) = "'" & Kap_Rng(i + ii - 1, "F").Text With New_Sh With .Cells(RowC, ii + IIf(ii = 1, 0, 2)) .RowHeight = 30 .Resize(5, 2) = Arr .Resize(1, 2).Merge .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .CurrentRegion.Borders.LineStyle = 1 .Offset(1, 0).Resize(5, 1).RowHeight = 20 .Offset(1, 0).Resize(5, 1).HorizontalAlignment = xlCenter .Offset(1, 0).Resize(5, 1).VerticalAlignment = xlCenter .Offset(1, 1).Resize(5, 1).HorizontalAlignment = xlLeft .Offset(5, 0).RowHeight = 13 End With End With If i = Rm_lng And i - 1 Mod 2 0 Then Exit For Next ii Next i Application.ScreenUpdating = True End Sub生成好的标签，配合标签打印机，即可一键打印，非常方便。 |
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |