Great question—this is a super common automation task, and VBA makes it straightforward to pull off. Below are step-by-step solutions for both Excel and Access, depending on which tool you’re more comfortable working with.

Excel VBA Solution #

First, Set Up Your Workbook #

1. Data Sheet: Create a sheet named Data with all your rows of data (put headers in row 1 to keep things organized).
2. Template Sheet: Make a sheet named Template formatted exactly how you want each PDF to look. Add placeholder cells where you’ll insert each row’s data (e.g., cell B2 for a name, B3 for an ID, etc.).

The VBA Code #

Open the VBA editor (press Alt + F11), insert a new module, and paste this code:

Sub GeneratePDFsPerRow()
    Dim wsData As Worksheet, wsTemplate As Worksheet
    Dim lastRow As Long, i As Long
    Dim savePath As String, fileName As String
    Dim invalidChars As Variant, char As Variant
    
    ' Configure your sheets and save path here
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsTemplate = ThisWorkbook.Sheets("Template")
    savePath = "C:\Your\Desired\Save\Folder\" ' Update this to your actual path!
    
    ' Create the save folder if it doesn't exist
    If Dir(savePath, vbDirectory) = "" Then
        MkDir savePath
    End If
    
    ' Get the last row of data in column A (adjust column if needed)
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    
    ' List of invalid filename characters to replace
    invalidChars = Array("/", "\", ":", "*", "?", """", "<", ">", "|")
    
    ' Loop through each data row (start at row 2 to skip headers)
    For i = 2 To lastRow
        ' Populate template with data from current row
        ' Adjust these ranges to match your Data and Template sheets!
        wsTemplate.Range("B2").Value = wsData.Range("A" & i).Value ' Name
        wsTemplate.Range("B3").Value = wsData.Range("B" & i).Value ' Employee ID
        wsTemplate.Range("B4").Value = wsData.Range("C" & i).Value ' Department
        wsTemplate.Range("B5").Value = wsData.Range("D" & i).Value ' Notes
        
        ' Create a unique, valid filename (use a unique field like ID to avoid duplicates)
        fileName = savePath & "Employee_" & wsData.Range("B" & i).Value & ".pdf"
        
        ' Replace invalid characters in the filename
        For Each char In invalidChars
            fileName = Replace(fileName, char, "-")
        Next char
        
        ' Export the template sheet to PDF
        wsTemplate.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=fileName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False ' Set to True if you want PDFs to open after saving
        
        ' Optional: Clear the template for the next row
        wsTemplate.Range("B2:B5").ClearContents
    Next i
    
    MsgBox "PDF generation complete! Files saved to: " & savePath, vbInformation
End Sub

How to Use This #

1. Update the savePath to your actual folder location.
2. Adjust the cell ranges (wsTemplate.Range("B2") and wsData.Range("A" & i)) to match your data and template layout.
3. Press F5 to run the macro, or assign it to a button in Excel for one-click access.

Access VBA Solution #

If you’re working with Access instead, use a report as your template:

Prerequisites #

1. Create a report designed to display a single record (format it exactly how you want each PDF to look).
2. Ensure your table has a unique identifier (like an ID field) to filter each record.

The VBA Code #

Open the VBA editor, insert a module, and paste this:

Sub GeneratePDFsFromAccess()
    Dim rs As Recordset
    Dim savePath As String, fileName As String
    Dim invalidChars As Variant, char As Variant
    
    ' Configure your save path and table/report names
    savePath = "C:\Your\Save\Folder\"
    Dim tableName As String: tableName = "Employees"
    Dim reportName As String: reportName = "EmployeeReport"
    
    ' Create save folder if it's missing
    If Dir(savePath, vbDirectory) = "" Then MkDir savePath
    
    ' Open a recordset of your data
    Set rs = CurrentDb.OpenRecordset(tableName)
    
    invalidChars = Array("/", "\", ":", "*", "?", """", "<", ">", "|")
    
    Do While Not rs.EOF
        ' Filter the report to the current record
        DoCmd.OpenReport reportName, acViewPreview, , "ID = " & rs("ID"), acHidden
        
        ' Create a valid filename
        fileName = savePath & "Employee_" & rs("ID") & ".pdf"
        For Each char In invalidChars
            fileName = Replace(fileName, char, "-")
        Next char
        
        ' Export the report to PDF
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        
        ' Close the report
        DoCmd.Close acReport, reportName
        
        rs.MoveNext
    Loop
    
    ' Clean up resources
    rs.Close
    Set rs = Nothing
    
    MsgBox "All PDFs generated successfully!", vbInformation
End Sub

Key Tips #

• Test first: Run the macro with a small number of rows to catch any range or path errors before processing all data.
• Invalid characters: The code replaces common invalid filename characters, but add more to the array if you encounter others.
• Macro security: In Excel, you may need to enable macros (File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable all macros—only do this for trusted files!).

内容的提问来源于stack exchange，提问作者mmehta