Create PDFs for each Excel Rows - Tutorial

In this tutorial, I will show you how to write a VBA code in Excel to generate multiple PDFs for each Excel rows. 

Step 1: Download Student Marks Excel sheet

Step 2: Create the PDF report format (which included in the downloaded Excel) as 'Format' sheet

Step 3: Save the Excel file as 'Macro-Enabled workbook' - .xlsm file

Step 4: Go to Developers tab and select Visual Studio

Step 5: Add the following VB code

'Copyrighted of
Sub ExportingPDF()

'Defining worksheets
Dim detailsSheet As Worksheet
Dim reportSheet As Worksheet

Set reportSheet = ActiveWorkbook.Sheets("Format")
Set detailsSheet = ActiveWorkbook.Sheets("Details")

Application.ScreenUpdating = False

'Looping the through each row
For i = 2 To 20

'Assigning values
SName = detailsSheet.Cells(i, 1)
SCommerece = detailsSheet.Cells(i, 2)
SEnglish = detailsSheet.Cells(i, 3)
SMaths = detailsSheet.Cells(i, 4)
STotal = detailsSheet.Cells(i, 5)

'Generating the output
reportSheet.Cells(3, 2).Value = SName
reportSheet.Cells(4, 2).Value = SCommerece
reportSheet.Cells(5, 2).Value = SEnglish
reportSheet.Cells(6, 2).Value = SMaths
reportSheet.Cells(7, 2).Value = STotal

'Save the PDF file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "D:\app\" & SName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _

Next i

Application.ScreenUpdating = True

End Sub

Step 6: Run the program. Your output PDFs will be generated in "D:\app\" folder or the folder you add in the code

Step 7: Your output will like this

Download the .xlsm file.

No comments:

Post a Comment