Making Password Protected PDFs using Excel Vba and PDFtk Tool

I am using excel to process salary. I have one sheet for Salary Data and other Sheet for Salary Slip. I am using Vlookup function for making salary slips.

Right Now, I change one value (on which vlookup is based) and it gives the numbers related to that salary slip. Then, I save that salary slip in other file with a password and mail it to others.

Now I basically need VBA for printing salary slip to pdf file with a password. I will be giving the password for each employee which will remain same for all times. And if possible I can directly mail the file to employee email using excel.

Can you help me with this.

Thanks
Mohit

Here are the steps:-

1. Download PDF Creator here:-
http://download.pdfforge.org/download/pdfcreator

2. Install only PDF Creator with Compact Installation settings. Don't use Full installation as it would download other unnecessary parts.
3. Open PDF Creater --> Profile settings. You have to go through all visible tabs and change many settings to make it automated.
a) Goto AutoSave section and Enable Auto Save option and Specify a path as well for auto save.
b) Goto Action Tab and Uncheck all the option. ( i.e. Open the Document and Open PDF files with PDF Architect.
c) Goto PDF--> Security --> Set security parameters appropriately like setting passwords and other settings. Set a password for opening the File.

4. Now use the following code in your excel file to print PDFs.

HTML Code: 

5. It will create the Password protected Printouts automatically to a pre specified location using PDF creator.

I have tested it and its working for me.

yes.. It did says while I downloaded.
You can Goto Downloads and Click on the appropriate link to get it downloaded.

For the new password for different file, I think I have to rethink about it.
It will take some time, I will catch you soon.

Hi Mohit,
Finally, I have succeeded in making a Protected PDF Code. Here are some steps:-

1. Download Pdftk (PDF toolkit) from the link below and Install.
Download PDF Toolkit

Actually, PDF toolkit provides Command line interface to make password protect pdfs.

2. Now Use the following code to Make Pdfs.

HTML Code: 

Change oPdf and Password Variable Accordingly.

You should provide your password list and explain that how you would like to generate PDFs.

I have tested it and its working for me.

Thanks Vikas. That was great. That worked for me too.
Can you help me with more than one file to be saved in a click with different passwords.

I have attached a dummy file.

1st worksheet - salary sheet.
2nd work sheet - salary slip - to be saved in pdf files.
3rd work sheet - contains file name to be saved as and respective password.

I will be customizing the code as per your needs and let you know.

Can you change the Thread Title to one I used in Post #6..?
this way, this would help many others as well.

Is the thread got changed?
If not pls tell how to change.

Okay,
Use the drop downs in the sheet1 and click the button to Generate Password protected salary slips.
I have eliminated Msgbox. Instead I have used Status Bar to show the progress. That will definitely reduce your clicks.
So keep an eye on the Left bottom.

Here is the amended code:-

HTML Code: 

Check the attached file:-

Here is the new code. Replace it with older one.

HTML Code: 

Hello Mohit,

Find attached Payroll Sample.xls. This may be of some help to you. I have written VBA code to generate payslips, create PDF with password protection (Pan Number will the password) and automatically email to the employees email id.

Download and install PDFtk from https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/

Password to open sample excel sheet : 2015may

Let me know if you need further assistance.

Regards,

Srikanth

Hello rook!E,
may be shell is not able to find pdftk.

I searched and found that 53 error comes for file not found.

Okay I found a potential solution here.
try to copy the pdftk.exe from the system32 folder to a location say D:/pdftk.exe and use it in the code.
http://www.mrexcel.com/forum/excel-q...not-found.html

like this.

Please Login or Register  to view this content.

it should work.

rizmomin, you should know begger
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Deleted original message.

chien_bap

Stick to the thread you created yourself. Old threads do not attrackt much response by other users.
You can always link back to this thread when you think it's relevant to your question.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Originally Posted by nyemoooot

Hi Vikas,
Kill Replace(fTemp, """", "") is giving me runtime error 53 and the password protected pdf file is not created.
what do i have to do? please help.
Thanks in advance

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread