How to write Macro to create bulk pdf in Excel ?
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. This 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.
The serial number can be changed with vba code "For –Next" loop. To execute this we have to input from and to serial number. For example if we give serial number 1 to 5, the vba code will execute 5 times with serial number from 1 to 5.
The 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.
This will save the excel to PDF files in the same folder where the excel file is saved.
This will work in excel version 2010 and above.
VBA Code:
--------------------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------------------------
This VBA program can be linked to the button in the sheet by right click.
Comments
Post a Comment