Log in  \/ 
x
or
x
Register  \/ 
x

or

How to convert multiple workbooks or worksheets to PDF files at once in Excel?

Sometime, you need to convert your Excel files to PDF files, so that they cannot be modified by others. In Excel, we can save an Excel file as PDF file by using Save As function. But how do you convert multiple workbooks to PDF files at the same time in Excel?


Convert multiple workbooks to PDF files with Save As function one by one

Normally, the Save As feature in Excel can help you to save the entire workbook to a PDF file, please do as this:

1. Launch you workbook that you want to convert to PDF format.

2. Click File > Save As, and in the Save As dialog box, please specify a folder for saving the new file, then select PDF from the Save as type drop down list, and then, click Options button, see screenshot:

3. Then, in the popped out Options dialog box, check Entire workbook, see screenshot:

4. And then click OK > Save, this whole workbook has been saved as PDF file format.

Tips: If you have multiple workbooks need to be converted, please apply above steps to save the workbooks to PDF files one by one.

Convert multiple workbooks to PDF files with VBA code at once

If there are hundreds of Excel files need to be converted, the above method will be troublesome, here, I will talk about a VBA code for converting multiple workbooks to PDF files at once, please do with the following steps:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Convert multiple workbooks to PDF files at once

Sub ExcelSaveAsPDF()
'Updateby Extendoffice
    Dim strPath As String
    Dim xStrFile1, xStrFile2 As String
    Dim xWbk As Workbook
    Dim xSFD, xRFD As FileDialog
    Dim xSPath As String
    Dim xRPath, xWBName As String
    Dim xBol As Boolean
    Set xSFD = Application.FileDialog(msoFileDialogFolderPicker)
    With xSFD
    .Title = "Please select the folder contains the Excel files you want to convert:"
    .InitialFileName = "C:\"
    End With
    If xSFD.Show <> -1 Then Exit Sub
    xSPath = xSFD.SelectedItems.Item(1)
    Set xRFD = Application.FileDialog(msoFileDialogFolderPicker)
    With xRFD
    .Title = "Please select a destination folder to save the converted files:"
    .InitialFileName = "C:\"
    End With
    If xRFD.Show <> -1 Then Exit Sub
    xRPath = xRFD.SelectedItems.Item(1) & "\"
    strPath = xSPath & "\"
    xStrFile1 = Dir(strPath & "*.*")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Do While xStrFile1 <> ""
        xBol = False
        If Right(xStrFile1, 3) = "xls" Then
            Set xWbk = Workbooks.Open(Filename:=strPath & xStrFile1)
            xbwname = Replace(xStrFile1, ".xls", "_pdf")
            xBol = True
        ElseIf Right(xStrFile1, 4) = "xlsx" Then
            Set xWbk = Workbooks.Open(Filename:=strPath & xStrFile1)
            xbwname = Replace(xStrFile1, ".xlsx", "_pdf")
            xBol = True
        ElseIf Right(xStrFile1, 4) = "xlsm" Then
            Set xWbk = Workbooks.Open(Filename:=strPath & xStrFile1)
            xbwname = Replace(xStrFile1, ".xlsm", "_pdf")
            xBol = True
        End If
        If xBol Then
            xWbk.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xRPath & xbwname & ".pdf"
            xWbk.Close SaveChanges:=False
       End If
        xStrFile1 = Dir
    Loop
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

3. Then, press F5 key to run this code, and a window will be displayed, please select a folder which contains the workbooks that you want to convert, see screenshot:

4. Then,click OK, another window is popped out, please select a folder path where you want to output the new PDF files, see screenshot:

5. And then, clik OK, after finishing the conversion, you can go to the specified folder to preview the converted result, see screenshots:


Convert multiple workbooks to PDF files with a powerful feature

If you have Kutools for Excel, with its Format Converter utility, you can quickly convert multiple workbooks to PDF files at once.

Tips:To apply this Format Converter feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools Plus > Workbook > Format Converter, see screenshot:

2. And a prompt box is popped out to remind you must close the workbooks that you want to convert.

3. Click OK, then, in the FileFormat Converter dialog, specify the following options:

  • Under the Convert type drop down list, select Excel workbook to PDF;
  • Then click Add button to add the Excel files that you want to convert, you can add the workbooks from your computor disk or OneDrive as you need; 

4. After inserting the workbooks that you want to convert, still in the File Format Converter dialog box, please click button to choose one folder path to output the converted files, and then specify some operations you need at the bottom of the dialog box, see screenshot:

Notes: In the above dialog box:
  • If the workbooks in the subfolders needed to be converted as well, please check the Include subfolders when adding folders check box;
  • If you want to remove the original files after converting, please check the Delete source files after conversion box;
  • If you want to keep the modified date of original files, please check the Keep modified date of original files box;
  • The directory structure of the file can be retained by checking the The directory structure of the file is preserved when converted box;

5. Then click OK to start converting, when all the specified Excel files have been converted to the PDF files, you can go to the specified folder to preview the converted result, see screenshots:


Convert multiple worksheets from a workbook to separate PDF files with VBA code

In some cases, you may want to convert each worksheet in the workbook to separate PDF files, if there are lots of sheets in your workbook, the below VBA code can help you to save each worksheet as a PDF file at once.

1. Open a workbook that you want to convert, and then hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Convert multiple worksheets from a workbook to separate PDF files

Sub SplitEachWorksheet()
'Updateby Extendoffice
Dim xSPath As String
Dim xSFD As FileDialog
Dim xWSs As Sheets
Dim xWb As Workbook
Dim xWbs As Workbooks
Dim xNWb As Workbook
Dim xInt, xI As Integer
Set xSFD = Application.FileDialog(msoFileDialogFolderPicker)
With xSFD
.title = "Please select a folder to save the converted files:"
.InitialFileName = "C:\"
End With
If xSFD.Show <> -1 Then Exit Sub
xSPath = xSFD.SelectedItems.Item(1)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xWb = Application.ActiveWorkbook
Set xWbs = Application.Workbooks
Set xWSs = xWb.Sheets
Set xNWb = xWbs.Add
xInt = xWSs.Count
For xI = 1 To xInt
On Error GoTo EBreak
Set xWs = xWSs.Item(xI)
If xWs.Visible Then
xWSs(xWs.Name).Copy
Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xSPath & "\" & xWs.Name & ".pdf"
Application.ActiveWorkbook.Close False
End If
EBreak:
Next
xWb.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

3. Then, press F5 key to run this code, and a window is popped out, please choose a folder path for locating the converted PDF files, see screenshot:

4. Then, click OK button, all worksheets within the current workbook have been converted PDF files separately, see screenshot:


Convert multiple worksheets from a workbook to separate PDF files with a handy feature

Kutools for Excel also supports a useful feature - Split Workbook, with this feature, you can save multiple worksheets within the workbook to separate Excel file, Txt file, CSV file and PDF file as you need.

Tips:To apply this Split Workbook feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Open the workbook that you want to save each sheet as PDF file, and then click Kutool Plus > Workbook > Split Workbook, see screenshot:

2. In the Split Workbook dialog box, check the sheet that you want to save, and then choose PDF (*.pdf) from the Save as type drop down list, and then click Split button, in the Select Folder window, specify a folder for putting the converted files, see screenshot:

3. And then, all selected worksheets in the current workbook have been saved to PDF files individually, see screenshot:


More relative articles:

  • Convert Multiple Xls Files To Xlsx Files In Excel
  • To convert an old xls Excel file to a new xlsx file, you can apply the Save As feature in Excel, but, if there are multiple xls files needed to be converted, how could deal with this task quickly and easily?
  • Save All Worksheets As Values Only
  • If you have a workbook which contains multiple formulas, now, you need to distribute this file to other users, you don’t want to show the formulas but only values be displayed. Normally, we can quickly save a worksheet as values by copying and pasting the data as values only. But, how could you save all worksheets as values only without copying and pasting one by one?
  • Command Button To Save Active Worksheet As PDF File
  • While working in Microsoft Excel, you may face the problem of saving active worksheet as PDF file. In this article, you can learn how to save active worksheet as PDF file with VBA code through a Command Button. And if you also want to save a range or each worksheet in active workbook as individual PDF file, this article can also help you.
  • Split A Large Table Into Multiple Small Tables
  • If you have a large worksheet which contains multiple columns and hundreds or thousands of rows data, now, you want to split this large table into multiple small tables based on the column value or number of rows to get the following results. How could you deal with this task in Excel?
  • Split A Workbook To Separate Excel Files
  • You may need to split a large workbook to separate Excel files with saving each worksheet of the workbook as an individual Excel file. For example, you can split a workbook into multiple individual Excel files and then deliver each file to different person to handle it. By doing so, you can get certain persons handle specific data, and keep your data safe. This article will introduce ways to split a large workbook to separate Excel files based on each worksheet.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    silvesterfairchildzd@gmail.com · 1 years ago
    This is perfect for what I was looking for. Thanks for the sharing. I used to convert excel file to PDF file with Acethinker PDF Creator, one by one, not so quick, share it here as an alternative solution.
  • To post as a guest, your comment is unpublished.
    javed · 4 years ago
    i am editing and updating my data in pdf explorer and i have already my data in excel sheet so now i want to export to my excel log to pdf explorer how