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.
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:
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 workbook. Open it and press Alt + F11.
Select a particular module and change a range in this code ActiveSheet.Range("A1:F50").Select
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 workbook. Open it and press Alt + F11.
Select a particular module and change a range in this code ActiveSheet.Range("A1:F50").Select
Thanks, very useful and helpful! Previously painful task was done in 5.63 seconds :)
ReplyDeleteThanks for stopping by. I'm glad you found it helpful!
DeleteHI thanks for the help
ReplyDeleteactually 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
ReplyDeleteSuper Boss, Can you just send the code to save them in multiple pdf's instead under sheet names instead single pdf
ReplyDeleteGreat work man. Thanks very much.
ReplyDeleteI 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)
It's absolutely okay. I appreciated you asked me for permission!
DeleteI 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
ReplyDeletenevermind I figured it out haha
DeleteHow did you do this?
DeleteHi 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.
ReplyDeleteThanks
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?
ReplyDeleteLOL. 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!
DeleteThanks. It helped me a lot
ReplyDeleteNo. 2 worksheet is exactly what I was looking for. Thank you soo much.....
ReplyDeleteVery useful these macros.
ReplyDeleteI 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
amazing, solved !!!! thanks
ReplyDeleteThe file is gone. Can you reupload please?
ReplyDeleteFixed. Check again.
Delete