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. 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 programming.
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 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, with vlookup formula
, the values will change with respect to serial number.
You can change the serial number with vba code “For –Next” loop. Input the serial number from and to, to execute this. For example if we give serial number 1 to 5, the vba code will execute 5 times with serial number from 1 to 5.
Excel file which contains VBA macro should be saved in .xlsm format.
Then, press Alt+F11 to open visual basic project and put the below mentioned code as shown below.
PDF files will be saved in the same folder where the excel file is saved.
This will work in excel version 2010 and above.
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”)
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:= _
MsgBox (“File saved in ” & filepath)
The button can be linked to VBA program by right click as shown in the below picture.
excel vba pdf