Automate Excel to PDF Conversion Using VBA

Introduction:

Excel offers a myriad of functionalities for data analysis and reporting. However, sharing these reports often requires converting them into a more accessible format like PDF. In this post, we’ll explore a VBA (Visual Basic for Applications) script that automates the process of converting Excel files into PDFs. MS Excel VBA Excel to PDF

What the Script Does:

The script consists of three main subroutines:

`Excel_To_PDF`: This is the core subroutine that opens each Excel file in a specified folder and converts it to a PDF file in another specified location.
`excel_PickFolder`: This subroutine allows the user to select the folder containing the Excel files to be converted.
`Pdf_PickFolder`: This subroutine enables the user to pick the destination folder for the PDF files.

The VBA Code: Excel to PDF

Here is the VBA code for the script:

  Option Explicit

' Convert Excel files in a folder to PDF
Sub Excel_To_PDF()
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim fso As New FileSystemObject
    Dim fo As Folder
    Dim f As File
    Dim wb As Workbook
    Dim n As Integer

    Set fo = fso.GetFolder(sh.Range("E13").Value)

    For Each f In fo.Files
        VBA.DoEvents
        n = n + 1
        Application.StatusBar = "Processing..." & n & "/" & fo.Files.Count
        Set wb = Workbooks.Open(f.Path)
        wb.ExportAsFixedFormat xlTypePDF, sh.Range("E14").Value & Application.PathSeparator & VBA.Replace(f.Name, ".xlsx", ".pdf")
        wb.Close False
    Next
    Application.StatusBar = ""
    MsgBox "Process Completed"
    Application.ScreenUpdating = True
End Sub

' Pick the folder with Excel files
Sub excel_PickFolder()
    Dim fd As FileDialog
    Dim selectedFolder As String

    ' Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    ' Display the Folder Picker dialog box and return the user's action.
    If fd.Show = -1 Then
        ' Get the path of the selected folder.
        selectedFolder = fd.SelectedItems(1)
        Sheet1.Range("E13").Value = selectedFolder
    End If

    ' Release the FileDialog object.
    Set fd = Nothing
End Sub

' Pick the folder to save PDF files
Sub Pdf_PickFolder()
    Dim fd As FileDialog
    Dim selectedFolder As String

    ' Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    ' Display the Folder Picker dialog box and return the user's action.
    If fd.Show = -1 Then
        ' Get the path of the selected folder.
        selectedFolder = fd.SelectedItems(1)
        Sheet1.Range("E14").Value = selectedFolder
    End If

    ' Release the FileDialog object.
    Set fd = Nothing
End Sub

How It Works:

Selecting Folders:
- The excel_PickFolder and Pdf_PickFolder subroutines use the FileDialog object to open a dialog box where users can select the source and destination folders, respectively.
- The paths of these folders are then stored in specific cells in the workbook (Sheet1.Range("E13") and Sheet1.Range("E14")).
Converting to PDF:
- The Excel_To_PDF subroutine uses a FileSystemObject to access and iterate through all the files in the source folder.
- Each Excel file is opened, and the ExportAsFixedFormat method is used to save it as a PDF in the destination folder.
- The script updates the status bar with the progress and shows a message box once the process is completed.