Excel Macro : Convert Multiple Excel Files to PDF

Deepanshu Bhalla 22 Comments , ,
How to convert multiple excel files to PDF using Excel VBA - This is one of the most asked question on any excel discussion forum.

Scenario:
In analytics industry, reports are generally generated in excel and powerpoint format. But companies generally prefer delivering them to clients in PDF format. A medium sized project requires conversion of hundreds of excel files to PDF.

Description:
It generates PDF files from workbooks in a folder automatically. And PDF files are saved in the folder where you wish them to be. (Code adapted from a post from Ron de Bruin)

Prerequisites:
MS Office Add-in: Save as PDF or XPS
If this add-in is not installed on your computer, download it from the Microsoft site : Download Add-In (If this link is not working, Google search on "Save as PDF or XPS MS Office' and you'll find the link working)

How to check if the add-in is installed?
Click on Office button, go to Save As button. If PDF or XPS is showing in the list, it means it is installed on your system.


Download the workbooks:
1. PDF conversion of the whole workbook
It creates a PDF file of the whole workbook from workbooks in a folder.

As shown above, define paths where excel files are stored and converted files to be saved. And then Hit "Run Macro" button. Download this workbook

2. PDF conversion of the selected worksheet
It creates a PDF file of the selected worksheet from the workbooks in a folder.


Download this workbook

3. PDF conversion of a particular range
It creates a PDF file of a particular range from the active worksheet from the workbooks in a folder. Download this workbook

4. PDF conversion of each sheet as a separate file
It creates a separate PDF file of each sheet from the active worksheet from the workbooks in a folder. Download this workbook

5. PDF conversion of each multiple selected sheets as a separate file
It creates a separate PDF file of each sheet from the multiple selected sheets from the workbooks in a folder. Download this workbook

6. PDF conversion of two selected sheets in a single file
It creates a single PDF file of two selected sheets from the workbooks in a folder.
 Download this workbook

7. PDF conversion of multiple selected sheets in a single file
It creates a single PDF file of multiple selected sheets from the workbooks in a folder. Download this workbook

How to use:
1. Open Excel . Press Alt + F11 to open the visual basic editor.
2. If you wish to make any changes in the code, select a module where code is placed

Q. How to create a PDF report of a particular range of the worksheet?
Sol : Download this workbookOpen it and press Alt + F11. 
Select a particular module and change a range in this code ActiveSheet.Range("A1:F50").Select
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

22 Responses to "Excel Macro : Convert Multiple Excel Files to PDF"
  1. Thanks, very useful and helpful! Previously painful task was done in 5.63 seconds :)

    ReplyDelete
    Replies
    1. Thanks for stopping by. I'm glad you found it helpful!

      Delete
  2. HI thanks for the help

    ReplyDelete
  3. actually you only have to press Shift after clicking the first sheet to the last sheet then select pdf or xps tab to convert all sheets

    ReplyDelete
  4. Super Boss, Can you just send the code to save them in multiple pdf's instead under sheet names instead single pdf

    ReplyDelete
  5. Great work man. Thanks very much.

    I copied the code in different work book is that ok? (I will not say I wrote the code myself and will say took it from the internet)

    ReplyDelete
    Replies
    1. It's absolutely okay. I appreciated you asked me for permission!

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. I have about 1200 excel files that need to be converted to pdf. All of my files are in separate folders in one parent folder. can you give me some pointers on how I might edit your code to search within subfolders? thank you

    ReplyDelete
  8. Hi I need to do the procedure for converting range of sheets in a workbook for a list of many workbooks with different sheet ranges specified within those workbooks. (not all files within a directory) I am having a lot of trouble trying to alter your VBA code to get this done. I currently have a list of the files and a list of the sheet names associated with those files but can't figure out how to loop the code across each workbook and each sheet range.

    Thanks

    ReplyDelete
  9. PDF Extractor is software that can extract text and images from a PDF File. The software is easy to use and can be operated by anyone. There are a few basic steps involved in the conversion process and all that one has to do is to follow those steps. Adobe After Effects CS5.5

    ReplyDelete
  10. How can anyone make sure that any (or your) downloadable macro doesn't contain any malware/spyware? It sure is great to learn from others while using VBA and Macros, but safety first right?

    ReplyDelete
    Replies
    1. LOL. I can expect this kind of response from 'anonymous' user only. The intend of this article is to share knowledge with others, not spreading malware/spyware. Always use anti-virus. Don't trust anyone!

      Delete
  11. You can check this by the arranging, format, content and pictures in the changed over document. altocompresspdf.com

    ReplyDelete
  12. No. 2 worksheet is exactly what I was looking for. Thank you soo much.....

    ReplyDelete
  13. Very useful these macros.
    I need to use version 7 but my excel files names contain the dot "." and the pdfs created don't have the same name as excel file as I need, eg.
    excel file name 0103-FK-01.40-4+0-0039_523FK
    pdf file name look like this 0103-FK-01
    How can I solve this?
    Thank you

    ReplyDelete
  14. The file is gone. Can you reupload please?

    ReplyDelete
Next → ← Prev