VBA: Excel / Word to PDF Encrypted / Password Protected

• Download ProtectPDFProject.zip - 5.5 MB

Introduction

Microsoft Office VBA macro: Excel or Word to save a document as password protected / encrypted PDF.

Let’s talk Office 2010. Looks like not too complex task. Start a macro recorder, go to: File / Save as. Select “Save as Type” and pick “PDF”. You will see an “Options…” button appear. Click that and check the box “Encrypt the document with a password”. Now, you will be asked to type in a password. Save. Stop macro recording.

Copy this code into whatever your VBA macro will be. Done!

But you’re not finished yet, the recorded code has no trace of password related activity.

Alright. Let’s go to MSDN: https://msdn.microsoft.com/en-us/library/office/ff836084.aspx.

Document.SaveAs2 Method (Word)

SaveAs2(FileName, FileFormat, LockComments, Password, AddToRecentFiles, WritePassword,
ReadOnlyRecommended, EmbedTrueTypeFonts, SaveNativePictureFormat, SaveFormsData, SaveAsAOCELetter,
Encoding, InsertLineBreaks, AllowSubstitutions, LineEnding, AddBiDiMarks, CompatibilityMode)

Saving as a word encrypted from VBA such as:

ActiveDocument.SaveAs2 fileName:= "myDoc" , Password:="mySecret" ,  
WritePassword:= "mySecret2" , AddToRecentFiles:=False

Will produce a password protected document (word).

Now let’s move on to PDF.

You can use:

ActiveDocument.SaveAs2 fileName:= "myDoc" , AddToRecentFiles:=False,  Password:="mySecret" , 
WritePassword= "mySecret2" , FileFormat:=WdSaveFormat.wdFormatPDF

Or a similar format:

ActiveDocument .ExportAsFixedFormat . . .

In both cases, you will get your PDF. Alas, unprotected. I have spent time trying to play with some parameters so you can spare yours. Unfortunately, MSDN documentation remains silent on this topic. As for PDF, the parameter Password, as mentioned above, is ignored. Oddly, Word and Excel can do it, but Microsoft did not deliver this functionality to the document object model. At the very minimum, MSDN should address this deficiency, saving the developer’s time.

A simple Internet search will refer you to third party tools (you need install one on a user computer) and remains the only the choice thus far. For PDF tools, you can get either paid PRO versions or free. Some of PDF tools can be engaged with VBA/VBS via COM/Interop, and some only via command line executable that you can run from VBA.

Beside encrypting/password protecting a PDF document, there are different permissions can be set as a document’s security options.

Selecting third party tool/libraries can be somewhat time consuming due to licensing (and if you’re doing work for a corporation you may also require IT/Security approval, which can take time – and I’m guessing you don’t want to waste time coding for tools which can be rejected … months later), performance, memory footprint – some are even full PDF editors which include GUI components. Some of the free tools do not even provide a full set of PDF security options or are limited to only 40 – bit encryption, unless you pay for the pro version.

Rummaging through all the above to address the simple task of filling in a gap in the Word/Excel object model functionality to save an encrypted PDF and set different security options from VBA, I found that the simplest and quickest solution to make a custom COM wrapper around one of the well-known .NET open source libraries. As an in house component, you shouldn’t have to worry about getting IT approval.

If you not familiar with making COM visible .NET DLL, here is an attached Visual Studio 2013 .NET v 4.0 project, written in C# and wrapping a popular library ITEXTSHARP.dll which is a .NET PDF library ported from Java.

This example is fully functioning. Feel free to modify / extend in order to tailor to your needs.

For simplicity, ITEXTSHARP.dll is imbedded into single output resulting library ProtectPDF.dll with a single method:

GoPDF.ProtectPdfStandard(string passwordUser,  string passwordOwner, 
                                                string namepathPDFIn, string namepathPDFOUT )

This method converts an unprotected PDF into password protected / 128 – bit encrypted one, with security setting only to Allow Printing. You can modify the code to customize security settings as they are just bitwise OR options.

If you are not familiar with what needs to be done next, here are the remaining steps:

• Your Windows computer should have .NET 4.0 installed, which is a standard now.
• You need to place a projects output library ProtectPDF.dll anywhere on your local hard drive (probably your application folder or a common one if you will be sharing this component) MyFolder.
• Copy .NET Utility RegAsm.exe (which comes with .NET framework) into MyFolder. If you are comfortable with all DOS prompts commands, you can run that utility from the original location, but I always tend to copy that utility along with the component I want to register. Sometimes, you want to move folder, which means you would have to unregister the component using the same utility, move folder, then register again. Or maybe you want to install the component on a different computer so you won’t have to look for RegAsm.exe again as it is VERSION SPECIFIC! In other words, if your component is built on .NET 4.0, then you need to use the appropriate version of RegAsm.exe. .NET 5.0 yet has to be seen.

  Anyway, a typical location for RegAsm.exe for .NET 4.0 is usually in a Windows folder similar to:

  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\  

  or:

  C:\Windows\Microsoft.NET\Framework\v4.0.30319\ if you are on 32-bit CPU.

  · Register the library on computer (this may require admin rights), by running the following CMD (PROMPT) command:

  >cd myFolder
  >myFolder>RegAsm.exe    ProtectPDF.dll /tlb: ProtectPDF.tlb /codebase

• The above will register the ProtectPDF.dll component on the system and also generate/register the type library ProtectPDF.tlb which you will be referring to from your Excel/Word VBA by adding ProtectPDF.tlb to References.
• If you need to re-register your component (i.e., you want to move the folder or have a new version of the component), unregister the component using the following:

  RegAsm.exe    ProtectPDF.dll /tlb: ProtectPDF.tlb /codebase /unregister

The rest is as usual:

Dim error As String
Dim protectObj as ProtectPDF.GoPdf
Set protectObj = New ProtectPDF.GoPdf
error = protectObj. ProtectPdfStandard( "usrSecret" , "ownerSecret" , "namepathPDFIn" ,  
 "namepathPDFOUT" )

Hopefully, the steps above will save you time when you need to create a protected PDF from MS Office VBA code.