Adding a password to a worksheet converted to PDF

[worthm]

Hello,
I'm using Office 2010 and I have a dashboard with about 22 sheets in a workbook. At the start of each month I finalize the numbers for the prior month, then dump all of the sheets into their own PDFs with this piece of VB code:

Option Explicit
Private Sub Button9_Click()  '<---Rename as appropriate mwmmm
  Dim myPath As String, FileName As String
  Dim sht As Worksheet
  Dim wrk As Workbook
  Set wrk = ThisWorkbook
  myPath = "V:\Arizona CSRs\Dashboard\Team PDFs\Phoenix\"  '<---Save the PDF here
  For Each sht In wrk.Worksheets
    'Execute on every sheet except the Welcome Screen
    If sht.Name <> "Passwords" Then '<---Skip this Worksheet
      FileName = sht.Name & ".Pdf"   '<---Name the PDF
      sht.ExportAsFixedFormat Type:=xlTypePDF, FileName:=myPath & FileName, Quality _
                :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
    End If
  Next sht
End Sub

I got this from a forum and it works well but the dashboard is growing. Once the dump into PDF is done I go in and manually add a password to each using Adobe Acrobat 8.0. This used to be easy with ten employees but now I have about 40 employee sheets and there ought to be an easier way. I have a sheet on the workbook with the list of passwords. How can I automate the task of adding the password to each PDF?

I haven't attached anything because I don't know if this is possible. Thanks in advance for any help

[Edit] I am not a programmer

[mancubus]

this may help:
http://www.experts-exchange.com/Soft..._26649382.html

the code in verified answer uses a freware named PDFtk which can be downloaded from
https://www.pdflabs.com/docs/pdftk-cli-examples/

[worthm]

Mancubus
Thank you for the help. I am on a work computer so I can't download items. Thanks though

[Kenneth Hobs]

My guess is that there would be a java solution if you really do have Adobe Acrobat Pro and not just the reader. If so, it might be possible. Adobe is funny about security though.

The 3rd party solution using a program like pdftk, I also explained in: http://www.vbaexpress.com/forum/show...rd-protect-PDF

For the sake of others, another 3rd party method is at: https://social.msdn.microsoft.com/Fo...forum=exceldev
and Ken Puls pdfCreator example: http://www.excelguru.ca/content.php?...y-options-set&

I doubt that I added that to my vb.net program where I used iTextSharp.dll. It is a mute point though as you would need to install the vb.net framework files possibly and my EXE. For those interested in the password part for iTextSharp, see: http://stackoverflow.com/questions/6...586551#6586551
For example vb.net project where I used iTextSharp see: http://www.wpuniverse.com/vb/showthr...-iTextSharp&s=

Please use code tags when posting code. Click the # icon in the reply toolbar and paste code between the inserted tags.

When cross-posting, it is common courtesy to post the link to the others in each. One that I know of is: http://www.ozgrid.com/forum/showthread.php?t=195185

[worthm]

Thanks Kenneth,
I'm using a work PC so I cant download third party files. I posted to Ozgrid because I hadn't received a useful response after 50 views. Thanks for the tip on code tags. I didn't know that.

Isnt there a way using VB code to write this? Im not a programmer but if Windows is written in VB then the PDFs would be as well

[Kenneth Hobs]

Could you not download files from home to a USB drive? Some programs can be installed to such which would bypass the need to install to a work computer. Of course you need the drive plugged into the work computer to access the 3rd party program via a Shell() as I linked in an example for pdftk if you could even go that route.

Otherwise, I see the only option would be to find some Adobe Java example code and use Adobe Acrobat Pro object, providing that you have that program installed. I generally avoid using the Adobe object methods as most don't have it installed. For the ones that do, which you seem to indicate, if they are the only one that will be using that VBA code with the Adobe reference, that is a good option. As I linked earlier, there is code to get close but not quite there. I can only test that kind of code on my work computer as I only have the Adobe reader on my home computers.

Regarding your last question, I gave you several ways to do it in VBA. It really matters little what language a program was written it. What matters is can you use VBA in Excel to get access to use that object (Adobe) and its methods and properties to work with the object. Using the Adobe Acrobat object method as I explained above might be possible but I have not seen code that can do it, fully. I will look around (read google search) and post back here if I find viable Adobe object methods to do it. As I said, it might be a few days as I can only test Adobe object methods at work.

[worthm]

The USB drive idea might work. The files I use are on a shared drive so Id have to link the files on the USB to the shared drive. The link from Mancubus took me to a site where you can join free for thirty days and then start paying. Im not fond of that idea. The users who get the PDFs won't see the work that went into creating them. I would do it on my PC and then email the PDFs to them. I'll think about your email. I don't know Java

[Kenneth Hobs]

If you go that route do this:
1. Install to USB drive. IF it does, without adding to your hard drive files and modify the registry, you will be set.
2. The next step is to test using Shell(). The program you are shelling to must accept command line parameters.