2 Effective Methods to Batch Convert Multiple Excel Workbooks to PDF Files

Sometimes, you may want to convert multiple Excel workbooks to PDF files. Here, in this article, we will introduce 2 methods to get it in batches.

In some cases, you may need to convert an Excel workbook to a PDF file in that a PDF file cannot be modified by others readily. As we all know, it is considerably easy to convert an Excel workbook to PDF, which can be achieved by native “Save As” feature, like the following Method 1. Yet, if there are many workbooks to be converted to PDF, you had better use the Method 2 introduced thereinafter, which is much more efficient. Now, read on to get them in detail.

Method 1: Convert One by One

1. First off, open an Excel workbook which you want to convert to PDF file.
2. Then, go to “File” menu and click “Save as” option.
3. Next, in the “Save As” window, select “PDF” from the drop down list of “Save as type”.
4. After that, click “Options” button in the “Save As” window.
5. Then, in the subsequent “Options” dialog box, select “Entire workbook”.
6. Lastly, click “OK” and “Save” to complete converting.
7. In this way, you can convert the other Excel workbooks to PDF files one by one manually.

Method 2: Batch Convert via VBA

1. At the very outset, collect all the Excel workbooks to be converted to PDF in a same Windows folder.
2. Then, launch Excel application.
3. Next, access Excel VBA editor by referring to “How to Run VBA Code in Your Excel“.
4. Then, put the following code into a module or project.

Sub BatchOpenMultiplePSTFiles()
    Dim objShell As Object
    Dim objWindowsFolder As Object
    Dim strWindowsFolder As String

    'Select the specific Windows folder
    Set objShell = CreateObject("Shell.Application")
    Set objWindowsFolder = objShell.BrowseForFolder(0, "Select a Windows folder:", 0, "")

    If Not objWindowsFolder Is Nothing Then
       strWindowsFolder = objWindowsFolder.self.Path & "\"

       Call ProcessFolders(strWindowsFolder)

       'Open the windows folder
       Shell "Explorer.exe" & " " & strWindowsFolder, vbNormalFocus
   End If
End Sub

Sub ProcessFolders(strPath As String)
    Dim objFileSystem As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim objExcelFile As Object
    Dim objWorkbook As Excel.Workbook
    Dim strWorkbookName As String

    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFileSystem.GetFolder(strPath)

    For Each objFile In objFolder.Files
        strFileExtension = objFileSystem.GetExtensionName(objFile)
        If LCase(strFileExtension) = "xls" Or LCase(strFileExtension) = "xlsx" Then
           Set objExcelFile = objFile
           Set objWorkbook = Application.Workbooks.Open(objExcelFile.Path)

           strWorkbookName = Left(objWorkbook.Name, (Len(objWorkbook.Name) - Len(strFileExtension)) -1)
           objWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & strWorkbookName & ".pdf"

           objWorkbook.Close False
        End If
    Next

    'Process all folders and subfolders
    If objFolder.SubFolders.Count > 0 Then
       For Each objSubFolder In objFolder.SubFolders
           If ((objSubFolder.Attributes And 2) = 0) And ((objSubFolder.Attributes And 4) = 0) Then
              ProcessFolders (objSubFolder.Path)
           End If
       Next
    End If
End Sub

5. Afterwards, click into the first subroutine and press “F5” key.
6. In the popup dialog, select the specific Windows folder where the Excel files are collected.
7. Then, click “OK”.
8. When macro finishes, the Windows folder will be opened, in which you can see the PDF files, like the following screenshot.

Comparison

In Case of Excel File Corruption

Multiple Excel users have ever experienced Excel file corruption. At times, Excel’s native recovery feature can restore the corrupt Excel file. Yet, sometimes it can’t. Therefore, it is highly recommended to prepare a powerful Excel fix tool, such as DataNumen Excel Repair.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover sql and outlook repair software products. For more information visit www.datanumen.com