VBA代码、批量生成工作证模板

实现功能：它可以快速地插入图片、姓名、职位、编号

需要这个模板可以去公众号上下载，公众号搜索天涯追梦54

下面是代码分享

Sub 批量生成工作证()

Dim sr As Shape, r%, lx%, ty%, tu$, wu As Shape, tu2$

Dim wu2 As Shape, wu3 As Shape, scu As Shape

For Each scu In Sheet1.Shapes

If Not scu.Type = 8 Then

scu.Delete

End If

Next

tu = ThisWorkbook.Path & '\工作证.jpg'

For r = 1 To Application.CountA(Sheet2.[a:a]) - 1

tu2 = ThisWorkbook.Path & '\' & Sheet2.Cells(r + 1, 'b') & '.jpg'

lx = (r - 1) Mod 3

ty = Int((r + 2) / 3 - 1)

Set sr = Sheet1.Shapes.AddPicture(tu, 1, 1, lx * 218, ty * 303, 208, 293)

On Error Resume Next

Sheet1.Shapes.AddPicture tu2, 1, 1, sr.Left + 71, sr.Top + 72, 65, 85

Set wu = Sheet1.Shapes.AddLabel(1, sr.Left + 77, sr.Top + 181, 96, 14)

Set wu2 = Sheet1.Shapes.AddLabel(1, wu.Left, wu.Top + 19, 96, 14)

Set wu3 = Sheet1.Shapes.AddLabel(1, wu.Left, wu.Top + 38, 96, 14)

wu.TextFrame2.TextRange.Characters.Text = Sheet2.Cells(r + 1, 2).Value

wu2.TextFrame2.TextRange.Characters.Text = Sheet2.Cells(r + 1, 3).Value

wu3.TextFrame2.TextRange.Characters.Text = Sheet2.Cells(r + 1, 1).Value

Next

End Sub