实务与縣作
利用Excel自动制作“工资条汀
黄小毛刘清饪
摘要:日常财务工作中,在制作和发放毎月员工工资信息时,为了便于信息管理与保密,经常需要具体到个人发放工资条,操作重复且繁琐,尤其是员工数量较大时会箱别复杂。本文使用Excel软件及里面的Visual Basic for Appli-cations语言编写宏功能,实现工资条制作、生成图片和发送邮件的一系列自动化操作。
关键词:ExcelVBA;工资条;批量操作
一、整理基础数据
本文使用的T-资数据模板如下所示。
第1行为标题行,第2行为数据的头行,以下为数据区域。
第A列至第D列依次为“员工编号”、“员工姓名”、“员工部门”和“员工岗位”,这四列显示了员工的基本信息。
第E列至第I列为各项该名员工本月应该发放的工资,依次为“基本工资”、“绩效工资”、“奖金”、“补贴”和“加班工资”,然后第J列为这五项的汇总项,代表了本月该员T.在未扣除相关项目时应得的工资,以J2为例,J2=E2+F2+G2+H2+I2。
第K列和第N列为扣除项,依次为“五险一金扣除”、“事、病假扣除”、“其他扣款”和所得税扣款。其中“其他扣款”指的是不能分类进其他扣除项目的项目,例如以前月份多扣工资的返还,如果是扣除项则以正数表示,如果是增加项则以负数表示。第0列是“实发工资”,表示该名员工本月应该收到的钱,以02为例,O2=J2-K2-L2-M2-N2。
二、补充基础数据
由于需要使用Outlook邮箱来发送工资条,在原有数据的右边增设“邮箱”一列,即P列,使用V LOOKUP函数在"联系方式工作表”中按员工编号来查找“邮箱”,以P1为例,P1输入函数V LOOKUPC A2,联系方式!A2:Ell,5,0)o
另外在R列插入标题为“图片文件名”一列,在此例中,邮件的标题与生成的工资条图片相同,故不额外填写邮件标题,以R1为例,输入公式“A2&”-"&B2&”-“2020年1月工资明细”,命名格式为“员工编号-员工姓名-2020年1月工资明细”。
三、生成工资条
首先在数据工作表中插入一个“按钮(窗体控件1)”,命名为“Stepl:Make Pay Stub",在VBA模块中输入以下代码:Sub MakePayStub()
Dim number1As Integer
number1=ActiveSheet.Range("al").End(xlDown).Row
Dini i As Integer
For i=3To numberl*2-3Step2
Range("Al").Select
Range(Selection,Selection.End(xlToRight)).Select
Selection.Copy
Range(Cells(i,"A"),Cells(i, "a").End(xlToRight)).S elect Selection.Inrt Shift:=xlDown
Next
End Sub
这串代码首先会声明一个名为“numberl”的Integer型的变量,变量的值为当前工资表包含A1单元格的区域A列最下方单元格的行数,在此例中为11(第1行的标题和第2行到第11行的数据,之后再声明一个名为“i”的Integer型变量,接着是一个用来定义“i”的For Next语句,“i”等于3到“numberl”的值的2倍减3.间隔为2,这段语句的作用是将第1行的标题复制粘贴到从第3行(数据的第2行)开始到最后一行之中每一行的上一行,原来的数据下移一行,最终生成工资条数据区域。
四、导出图片
插入一个“按钮(窗体控件)”,命名为“Step2:Make Picture",在VBA模块中输入如下代码:
Sub MakePicture()
Dim path1As String
pathl=InputBox("请输入文件夹路径”)
On Error Resume Next
VBA.MkDir(pathl)
Dim number2As Integer
number2=ActiveSheet.Range("al").End(xlDown).Row
Dim row1As String
rowl=InputBox("请输入生成工资条起始列”)
Dim row2As String
row2=InputBcx("请输入生成工资条结束列”)
Dim tow3A s String
row3=InputBox(44请选择图片名称列")
Dim k As Integer
For k=1To number2-1Step2
Sheets("工资数据”).Range(Cells(k,row1),Cells(k+1, row2)).Select
Selection.Copy
中国农业会计2021—4
宾务与操作
Selection.CopyPicture
With ActiveSheet.ChartObjects.Add(1,1,Selection.Width, Selection.Height).Chart
.Parent.Select
.Paste
.Export"&pathl&&Cells(k+1,row3)&".jpg"
.Parent.D elete
End With
Next
End Sub
这段代码首先会声明一个名为“pathl”的String型变量,接着会弹出一个对话框。这里要求使用者输入接下来保存工资条图片的文件夹路径,如果该文件夹不存在,将会新建一个,这里输入的是“C:\Urs\l1796\Desktop\T资条”,意思是保存在桌面的名为“工资条”的文件夹。接下来定义一个名为“numher2”的Integer型变量,它的含义与"Step1:Make Pay Stub"中"num-berl”的含义相同,但两者属于不同的宏,所以需要重新定义。
之后会弹出主题分别为“请输入生成工资条起始列”和“请输入生成工资条结束列”两个对话框,可以根据需求来自主选择,如果需要员工个人信息在里面就分别输入“A”和“0”,如果只需要工资信息就输入“E”和“0”,本例中输入的是后者。
再后会弹出一个主题为“请选择图片名称列”的弹窗,这里输入图片文件名这列“Q”。最后定义一个名为“k”的Integer型变量,加入一个For Next语句,"k”的值从1到"number2”的值-1,间隔为2,这段语句会依次将“E1:O2”、“E3:O4^此类推到最后的单元格导岀为图片并保存至刚刚创建的"C:\Urs\11796\Desk-top\工资条”文件夹,并以Q列的值命名,命名规则为“员工编号-员工姓名-2020年1月工资明细”。
五、发送邮件
插入一个名为“Step3:Send Email”的按钮(窗体控件),输入如下代码:
Sub SendEmaiK)
"
Set myOlApp=CreateObject("Outlook.Application")
Set objMail=my01App.CreateItem(olMailltem)
Dim path2As String
Path2=InputBox(u请输入文件夹路径”)
Dim row4As String
row4=InputBox("请输入收件人列')
Dim row5As String
row5=InputBox(**请选择主题列”)
Dim row6As String
row6=InputBox("请选择图片名称列")
Dim number3As Integer
number3=ActiveSheet.Range("al").End(xlDown).Row
Dim a As Integer
For a=2To number3Step2
Set objMail=my01App.CreateItem(olMailltem)
With objMail
.To=Application.Workbooks("工资条(测试3)M).Worksheets(**工资数据").Cells(a,row4).V alue
.Subject=Application.Workbooks("工资条(测试3)") .Worksheets(M工资数据”).Cells(a,row5).V alue
.Body=""
="”
.
Attachments.Add"&path2&"\”&Cells(a,row6).Value&“•jpg”
.Display
.nd
End With
Set objMail=Nothing
Next
End Sub
这段代码首先会打开Outlook邮箱应用,然后使用默认的发件人新建一封邮件。接下来出现的四个对话框的主题分别为:“请输入文件夹路径”,“请输入收件人列”,“请输入主题列”,“请输入图片名称列”,第一个对话框输入“Step2:Make Picture"中用来保存工资条图片的文件路径,第二个对话框输入邮箱列,在这里输入“P”,邮件主题和图片文件名是相同的,所以在第三、四对话框都输入“Q”,接下来的For Next语句将会保存在"C:\Urs\l1796\Desktop\Jl资条”文件夹下的工资条图片,以与工资条图片同名的主题发送至一一对应的邮箱。
六、小结
本例中使用Excel VBA功能在模块中制作了三段代码,分别实现了生成工资条、导出工资条图片和发送邮件三项功能,适用于数据区域左上角为A1单元格,第1行为标题的所有工资数据模板,缺陷是outlook邮箱在实际工作中使用得较少,第三步功能有时候无法完成,但是依然提高了效率和质量,使原来复杂的工作简单化。
主要参考文献
[1]陈国栋.利用Excel加载宏制作工资条[J].财会月刊,2014(07):107-109.
[2]朱庆东.利用Excel VBA制作工资条[J].财会月刊,2011(13):53-54.
[3]何学武.Excel下基于VBA的工资条制作模板开发[几财会月刊,2009(23):88-89.
[4]谭震,邵志荣.EXCEL在工资管理中的运用[J].财会通讯,2006(11):70-71.
作者单位:江西理工大学
(责任编辑:帅一)
中国农业会计2021—4