How to write Macro to create bulk pdf in Excel ?
Excel vba pdf : Excel VBA programming is a powerful tool to automate the task. We can automate the repetitive task and save lot of time in work. Mastering excel vba will definitly help us in improving the work efficiency. In this post you can learn how to automate salary slip creation in pdf format. You can create salary slip in pdf for many employees with one click by VBA macros. Lets understand step by step process to create macro to create bulk pdf in excel.
To create this, first we have to format the pay slip to be printed. The format is to be linked to a particular serial number or unique id with vlookup formula so that the information of different employees will be updated automatically when we change the serial number. You can see that in the below picture. The data fields are linked to a DATABASE sheet, when we change the serial number or unique id, with vlookup formula, the values in the pay slip format will change with respect to serial number or unique id.
You can change the serial number with vba code “For –Next” loop. Input the serial number from and to, to execute number of pay slip to create. For example if we give serial number 1 to 5, the vba code will execute 5 times with serial number from 1 to 5 and five pay slip file will be created.
Excel file which contains VBA macro should be saved in .xlsm format.
You can press Alt+F11 to open visual basic project and put the below mentioned code as shown below.
Alternatively, you can also right click on sheet and click on “View Code” to open visual basic project.
PDF files will be saved in the same folder where the excel file is saved.
This will work in excel version 2010 and above.
The button can be linked to VBA program by right click and selecting Assign Macros… as shown in the below picture.
Sub Savepdf()
Dim from, to1 As Variant, Dim flname As String, Dim filepath As String
from = Sheets("Pay slip").Range("H2")
to1 = Sheets("Pay slip").Range("H3")
For i = from To to1
Sheets("Pay slip").Range("C2") = i
filepath = Application.ActiveWorkbook.Path
flname = Sheets("Pay slip").Range("N9")
Sheets("Pay slip").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
filepath & "" & flname, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Next i
MsgBox ("File saved in " & filepath)
End Sub