Pages

Wednesday, February 24, 2021

Determining a Machine Catalog's master image, snapshot and VMware vSphere datacenter with PowerShelll for Citrix Virtual Apps and Desktops

One of the most common administration questions I get asked for Citrix Virtual Apps and Desktops is how to determine the master image currently being used for a Machine Catalog. Those familiar with the Web Studio will be familiar that you can select the Machine Catalog in the administration console:

image

Then click on the Template Properties tab, then review the name under Disk Image to determine the snapshot being used. In the example below, the snapshot being used is named Citrix_XD_Dev Desktops. However, this does not tell the administrator the actual virtual machine name that is being used:

image

To retrieve the details of the master image currently being used for the Machine Catalog, launch the PowerShell console and connect to Citrix Cloud with:

asnp citrix*

Get-XDAuthentication

Then proceed to use the follow cmdlet to display the details of the Machine Catalog:

Get-ProvScheme -ProvisioningSchemeName "Dev Desktops"

**We have used the Dev Desktops as the Machine Catalog for this example.

image

The virtual machine used for the machine catalog is provided in the MasterImageVM setting:

MasterImageVM : XDHyp:\HostingUnits\WorkspaceSTG\MasterImage Feb 2020 v1.vm\Citrix_XD_Dev Desktops.snapshot

With the details of the virtual machine determined, we can look for the VMware vSphere datacenter it is located in by further drilling into the details of the virtual machine by using the CD command as such:

CD "XDHyp:\HostingUnits\WorkspaceSTG\MasterImage Feb 2020 v1.vm"

Then use the DIR command to list the details:

image

The setting that provides the datacenter is ObjectPath:

ObjectPath: /TechHall.datacenter/Desktops.cluster/MasterImage Feb 2020 v1.vm/Citrix_XD_Dev Desktops.snapshot

The datacenter in this example is named TechHall.

Hope this helps anyone looking for how to retrieve the details of a machine catalog within Citrix Virtual Apps and Desktops.

Monday, February 22, 2021

Duo MFA login prompt does not show up after successfully authenticating username and password with Citrix ADC / NetScaler Citrix Gateway

Problem

You’ve completed configuring a Citrix Gateway with Duo MFA as described in the following configuration documentation:

Duo for Citrix Gateway Basic Primary Authentication
https://duo.com/docs/citrix-netscaler

image

However, you notice that authenticating through the portal does not display the Duo 2FA prompt as expected:

image

Note how only a field labeled as Password is displayed:

image

It should be noticed that entering the passcode from the Duo mobile app would successfully authenticate the identity but the following expected Duo login prompt is the desired outcome:

image

Solution

One of the common reasons why this behavior would be exhibited is if the portal them RFWebUI is selected (this the default theme in later versions of Citrix ADM / NetScalers) and the quick way to see if this is causing an issue is to switch the them to X1 as shown below:

image

image

image

However, the better solution is to refer to the following KB:

Is the Citrix Gateway RFWebUI theme compatible with Duo?
https://help.duo.com/s/article/3755?language=en_US

Which will refer to the complete instructions for information on how to configure the RFWebUI theme with Duo: https://duo.com/docs/citrix-netscaler#configure-the-proxy-for-your-citrix-gateway

Prior to implementing the configuration, verify that the requirements of having the Citrix ADC / NetScaler and Authentication Proxy at a certain version is met:

citrix_netscaler - Use citrix_netscaler when with the Default, Green Bubbles, or X1 themes.

citrix_netscaler_rfwebui - Use citrix_netscaler_rfwebui with the RFWebUI theme. Requires Authentication Proxy v3.1.0 and NS build 12.1-51.16 or later.

image

Attempting to log into a Citrix ADC / NetScaler published Citrix Gateway displays the prompt: "Cannot Complete Your Request"

Problem

You’ve completed configuring two Citrix StoreFront configured as a Load Balancing Virtual Server that is published by a Citrix ADC / NetScaler Gateway but noticed that the successfully logging on would continuously display the prompt:

Cannot complete your request.

image

image

Attempting to directly access the Load Balancing Virtual Server representing the StoreFront servers exhibits the same issue.

The following article’s recommendations does not appear to correct the issue:

Common Resolutions to “Cannot Complete Your Request” Error when connecting directly to StoreFront Server
https://support.citrix.com/article/CTX207162

Solution

The Cannot Complete Your Request prompt can be caused by many reasons but one of the more common reason I’ve come across is if the Load Balancing Virtual Server representing the StoreFront servers does not have Persistence configured:

image

To correct the issue, add the Persistence setting to the Load Balancing Virtual Server representing the StoreFront servers and configure

Select Persistence Type: SOURCEIP

Time-out (mins): the desired duration

Leave the rest as defaults.

image

Applying the settings should correct the issue:

image

Thursday, February 18, 2021

Using PowerShell to Batch Password Protect Adobe PDF Files in a Directory

As a follow up to my previous post:

Attempting to use itextsharp.dll throws the error: "Could not load file or assembly 'BouncyCastle.Crypto, Version=1.8.6.0, Culture=neutral, PublicKeyToken=0e99375e54769942'..."

http://terenceluk.blogspot.com/2021/02/attempting-to-use-itextsharpdll-throws.html

… the script provided in the blog post did not match the requirements I needed so I’ve gone ahead to modify it and created two versions:

  1. Uses an Excel spreadsheet with predefined passwords to password protect PDF documents and fills in the name of the file
  2. Uses an Excel spreadsheet to fill in PDF file names and the randomly generated password used to password protect it

The following are the two PowerShell scripts:

Using PowerShell to Password Protect Adobe PDF Files (requires only passwords)

The following describes how to use PowerShell to password protect Adobe PDF files with a reference spreadsheet with only the passwords defined. The PowerShell script’s logic is as follows:

  1. Opens up a spreadsheet with two columns:
    1. Source Name < blank
    2. Password < password specified
  2. Looks into a directory and begins password protecting the documents with the passwords in the spreadsheet AND fills in the source name column with the file name
  3. Saves the PDF into another directory

1. Begin by creating a spreadsheet with two columns leaving the Source Name blank with the Password column filled in:

    a) Source Name

    b) Password

image

2. Proceed by creating a .ps1 file from the following code (also attached as PasswordProtectPDF.ps1):

Add-Type -Path "C:\itextsharp.5.5.13.2\lib\BouncyCastle.Crypto.dll"

[System.Reflection.Assembly]::LoadFrom("itextsharp.dll")

function PSUsing {

param (

[System.IDisposable] $excelSheetNumbernputObject = $(throw "The parameter -inputObject is required."),

[ScriptBlock] $scriptBlock = $(throw "The parameter -scriptBlock is required.")

)

Try {

&$scriptBlock

}

Finally {

if ($excelSheetNumbernputObject.psbase -eq $null) {

$excelSheetNumbernputObject.Dispose()

} else {

$excelSheetNumbernputObject.psbase.Dispose()

}

}

}

$sourcePath = "C:\PDF\"

$destinationPath = "C:\PDF\WithPassword\"

$excelReferenceSheet = "c:\ScriptsNew\PDFList.xlsx"

$xlCellTypeLastCell = 11

$startRow,$col=2,1

$excelSheetNumber=1

# OPEN EXCEL FILE WITH PASSWORDS

$excel=new-object -com excel.application

$excel.DisplayAlerts = $false

$excel.Visible = $false

$workbook=$excel.workbooks.open($excelReferenceSheet)

$worksheet=$workbook.Sheets.Item($excelSheetNumber)

$rowIncrement=0;

# BEGIN TRAVERSING THROUGH SOURCE FOLDER

$files = Get-ChildItem "C:\PDF\\*" -file

foreach ($f in $files){

# WRITE SOURCE FILE NAME INTO SPREADSHEET

#Write-Output $f.name

$worksheet.Cells.Item($startRow + $rowIncrement,$col) = $f.name

#RETRIEVE PASSWORD FROM EXCEL

$password=$worksheet.Cells.Item($startRow + $rowIncrement,$col+1).Value2

#Write-Output $password

$rowIncrement++

$fullSourcePathAndFileName = $sourcePath + $f.name

$destinationPathAndFileName = $destinationPath + $f.name

# PASSWORD PROTECT PDF

New-Object PSObject -Property @{Source=$sourcePath + $sourceFileName;Destination=$destinationFileName;Password=$password}

$file = New-Object System.IO.FileInfo $fullSourcePathAndFileName

$fileWithPassword = New-Object System.IO.FileInfo $destinationPathAndFileName

PSUsing ( $fileStreamIn = $file.OpenRead() ) {

PSUsing ( $fileStreamOut = New-Object System.IO.FileStream($fileWithPassword.FullName,[System.IO.FileMode]::Create,[System.IO.FileAccess]::Write,[System.IO.FileShare]::None) ) {

PSUsing ( $reader = New-Object iTextSharp.text.pdf.PdfReader $fileStreamIn ) {

[iTextSharp.text.pdf.PdfEncryptor]::Encrypt($reader, $fileStreamOut, $true, $password, $password, [iTextSharp.text.pdf.PdfWriter]::ALLOW_PRINTING)

}

}

}

}

$workbook.SaveAs($excelReferenceSheet)

$excel.Close

$excel.Quit()

[System.GC]::Collect()

[System.GC]::WaitForPendingFinalizers()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Remove-Variable -Name excel

3. Open the ps1 script and edit the 3 paths as highlighted in red above:

$sourcePath = "C:\PDF\" <-- Directory containing the source PDFs

$destinationPath = "C:\PDF\WithPassword\" <-- Directory containing the source PDFs

$excelReferenceSheet = "c:\ScriptsNew\PDFList.xlsx" <-- Directory where the spreadsheet that the script will reference for the PDF file name and password

$files = Get-ChildItem "C:\PDF\\*" -file <-- Update the C:\PDF to the directory containing the source PDFs

4. Download the following .nupkg packages:

iTextSharp 5.5.13.2
https://www.nuget.org/packages/iTextSharp/

BouncyCastle 1.8.6.1
https://www.nuget.org/packages/BouncyCastle/1.8.6.1

Rename the .nupkg extension to .zip, extract them to a folder and then copy the itextsharp.dll and BouncyCastle.Crypto.dll files

5. Create the directory where the password protected PDF files will be stored.

6. Proceed to execute the PowerShell script to password protect PDFs.

image

7. New PDFs should be created in the destination directory upon successfully executing the PowerShell script.

image

8. The spreadsheet referenced for configuring the passwords will have the Source Name column filled in with the file names of the password protected PDFs:

image

Using PowerShell to Password Protect Adobe PDF Files (auto generate passwords)

The following describes how to use PowerShell to password protect Adobe PDF files with a reference spreadsheet with only the passwords defined. The PowerShell script’s logic is as follows:

  1. Opens up a spreadsheet with two columns:
    1. Source Name < blank
    2. Password < password specified
  2. Looks into a directory and begins password protecting the documents with the passwords in the spreadsheet AND fills in the source name column with the file name
  3. Saves the PDF into another directory

1. Begin by creating a spreadsheet with two columns leaving the Source Name and Password columns blank:

    a) Source Name

    b) Password

image

2. Proceed by creating a .ps1 file from the following code (also attached as PasswordProtectPDF.ps1):

Add-Type -Path "C:\itextsharp.5.5.13.2\lib\BouncyCastle.Crypto.dll"

Add-Type -AssemblyName System.Web

[System.Reflection.Assembly]::LoadFrom("itextsharp.dll")

function PSUsing {

param (

[System.IDisposable] $excelSheetNumbernputObject = $(throw "The parameter -inputObject is required."),

[ScriptBlock] $scriptBlock = $(throw "The parameter -scriptBlock is required.")

)

Try {

&$scriptBlock

}

Finally {

if ($excelSheetNumbernputObject.psbase -eq $null) {

$excelSheetNumbernputObject.Dispose()

} else {

$excelSheetNumbernputObject.psbase.Dispose()

}

}

}

$sourcePath = "C:\PDF\"

$destinationPath = "C:\PDF\WithPassword\"

$excelReferenceSheet = "C:\Scripts-GeneratePassword\PDFList.xlsx"

$xlCellTypeLastCell = 11

$startRow,$col=2,1

$excelSheetNumber=1

$passwordLength = 15

$numberOfSpecialCharacters = 4

# OPEN EXCEL FILE TO FILL OUT PDF FILENAME AND PASSWORDS

$excel=new-object -com excel.application

$excel.DisplayAlerts = $false

$excel.Visible = $false

$workbook=$excel.workbooks.open($excelReferenceSheet)

$worksheet=$workbook.Sheets.Item($excelSheetNumber)

$rowIncrement=0;

# BEGIN TRAVERSING THROUGH SOURCE FOLDER

$files = Get-ChildItem "C:\PDF\\*" -file

foreach ($f in $files){

# WRITE SOURCE FILE NAME INTO SPREADSHEET

#Write-Output $f.name

$worksheet.Cells.Item($startRow + $rowIncrement,$col) = $f.name

#GENERATE RANDOM PASSWORD AND FILL OUT SPREADSHEET

$password = [System.Web.Security.Membership]::GeneratePassword($passwordLength,$numberOfSpecialCharacters)

$worksheet.Cells.Item($startRow + $rowIncrement,$col+1).Value2 = $password

#Write-Output $password

$rowIncrement++

$fullSourcePathAndFileName = $sourcePath + $f.name

$destinationPathAndFileName = $destinationPath + $f.name

# PASSWORD PROTECT PDF

New-Object PSObject -Property @{Source=$sourcePath + $sourceFileName;Destination=$destinationFileName;Password=$password}

$file = New-Object System.IO.FileInfo $fullSourcePathAndFileName

$fileWithPassword = New-Object System.IO.FileInfo $destinationPathAndFileName

PSUsing ( $fileStreamIn = $file.OpenRead() ) {

PSUsing ( $fileStreamOut = New-Object System.IO.FileStream($fileWithPassword.FullName,[System.IO.FileMode]::Create,[System.IO.FileAccess]::Write,[System.IO.FileShare]::None) ) {

PSUsing ( $reader = New-Object iTextSharp.text.pdf.PdfReader $fileStreamIn ) {

[iTextSharp.text.pdf.PdfEncryptor]::Encrypt($reader, $fileStreamOut, $true, $password, $password, [iTextSharp.text.pdf.PdfWriter]::ALLOW_PRINTING)

}

}

}

}

$workbook.SaveAs($excelReferenceSheet)

$excel.Close

$excel.Quit()

[System.GC]::Collect()

[System.GC]::WaitForPendingFinalizers()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Remove-Variable -Name excel

3. Open the ps1 script and edit the 3 paths as highlighted in red above:

$sourcePath = "C:\PDF\" <-- Directory containing the source PDFs

$destinationPath = "C:\PDF\WithPassword\" <-- Directory containing the source PDFs

$excelReferenceSheet = "c:\ScriptsNew\PDFList.xlsx" <-- Directory where the spreadsheet that the script will reference for the PDF file name and password

$passwordLength = 15 <-- The length of the randomly generated password

$numberOfSpecialCharacters = 4 <-- The amount of non-numerical special characters in the password (e.g. !, -, $, &, @, #, %, etc)

$files = Get-ChildItem "C:\PDF\\*" -file <-- Update the C:\PDF to the directory containing the source PDFs

4. Download the following .nupkg packages:

iTextSharp 5.5.13.2

https://www.nuget.org/packages/iTextSharp/

BouncyCastle 1.8.6.1

https://www.nuget.org/packages/BouncyCastle/1.8.6.1

Rename the .nupkg extension to .zip, extract them to a folder and then copy the itextsharp.dll and BouncyCastle.Crypto.dll files

5. Create the directory where the password protected PDF files will be stored.

6. Proceed to execute the PowerShell script to password protect PDFs.

image

7. New PDFs should be created in the destination directory upon successfully executing the PowerShell script.

image

8. The spreadsheet referenced for configuring the passwords will have the Source Name column filled in with the file names and corresponding passwords of the password protected PDFs:

image

Batch converting Microsoft Word .doc to .docx with VBA

I was recently asked to provide an way to batch process a set of Microsoft Word documents that are still saved in the older .doc format to the modern .docx format and I wasn’t able to find a script that did exactly what I wanted so ended up writing one for the client. Coding isn’t my forte given it is not something I commonly perform so I thought I’d write this blog post for anyone who may be looking for something similar as well as have it for myself to reference in the future.

The following demonstrates how to open .doc documents in a specified folder and save them as .docx.

1. Begin by launching Word, click on the Developer tab and click on Visual Basic

image

2. Click on the Insert tab and then Module:

image

3. Paste the following code into the module:

Sub UpdateOldDocToDocx()

    Application.ScreenUpdating = False

    'Disable privacy settings warning

    Application.DisplayAlerts = False

    Dim oldDocument As Document

    Dim strPassword As String, strFile As String, strFolder, strDestFolder As String

    Dim passwordRow As Integer

    strFolder = "C:\Word\"

    strFile = Dir(strFolder & "*.doc", vbNormal)

    strDestFolder = "C:\Word\Upgraded\"

    ' BEGIN LOOPING THROUGH OLD WORD DOCUMENTS

    While strFile <> ""

      Set oldDocument = Documents.Open(FileName:=strFolder & strFile)

      'OPEN WORD, SAVE AND CLOSE

      With oldDocument

        .SaveAs2 FileName:=strDestFolder & strFile & "x", FileFormat:=wdFormatXMLDocument, CompatibilityMode:=15

        .Close

      End With

      strFile = Dir()

      Wend

End Sub

6. Update the variables to desired values:

strFolder: Where the source files are (ensure that the trailing "\" is in the path)

strDestFolder: Where the destination password protected files should be

(ensure that the trailing "\" is in the path)

7. Proceed to run the module by clicking on the play button or F5:

image

8. The upgraded files should be present in the defined directory:

imageimage

Wednesday, February 17, 2021

Using Microsoft Excel and Word VBA to batch password protect documents

I was recently asked to provide an way to batch process a set of Microsoft Excel and Word documents to password protect them and managed to use VBA to accomplish this. Coding isn’t something I commonly perform so I thought I’d write this blog post for anyone who may be looking for something similar as well as have it for myself to reference in the future.

Using an Excel Spreadsheet with passwords to password protect Excel files

1. Begin by creating an Excel spreadsheet with two columns and fill in the Password column with as many rows of passwords as there are Excel files to encrypt:

image

2. Save the file at a location that is outside of the folder containing the Excel files to encrypt. We will use C:\temp for this example.

image

3. Launch Excel, click on the Developer tab and click on Visual Basic:

image

4. Click on the Insert tab and then Module:

image

5. Paste the following code into the module:

Sub ProtectMultiExcelWithOpenPasswordwithExcelReferenceFile()

     Application.ScreenUpdating = False

    'Disable privacy settings warning

     Application.DisplayAlerts = False

    Dim passwordListExcel As Workbook

    Dim protectThisWorkbook As Workbook

    Dim strPassword As String, strFile As String, strFolder, strDestFolder As String

    Dim passwordRow As Integer

    strFolder = "C:\Excel\"

    strFile = Dir(strFolder & "*.xlsx", vbNormal)

    strDestFolder = "C:\Excel\Encrypted\"

    ' OPEN THE EXCEL WORKBOOK WITH PASSWORDS

    Set passwordListExcel = Workbooks.Open("C:\temp\ExcelFiles.xlsx")

    'SET ROW TO START AT 2 IGNORING THE HEADER

    passwordRow = 2

    ' BEGIN LOOPING THROUGH EXCEL WORKBOOK TO SET PASSWORDS

    While strFile <> ""

      Set protectThisWorkbook = Workbooks.Open(Filename:=strFolder & strFile)

      strPassword = passwordListExcel.Worksheets("Sheet1").Range("B" & passwordRow).Formula

      'OPEN EXCEL, SET PASSWORD, SAVE AND CLOSE

      With protectThisWorkbook

        .Password = strPassword

        .SaveAs Filename:=strDestFolder & "Encrypted-" & strFile, Password:=strPassword

        .Close

      End With

      ' WRITE THE DOCUMENT NAME INTO THE CELL BESIDE THE PASSWORD IN THE REFERENCE SPREADSHEET

       passwordListExcel.Worksheets("Sheet1").Range("A" & passwordRow).Formula = strFile

      'INCREMENT THE ROW COUNT BY 1 TO GO TO THE NEXT ROW

      passwordRow = passwordRow + 1

      strFile = Dir()

      Wend

       passwordListExcel.Close True

      Set passwordListExcel = Nothing

End Sub

6. Update the variables to desired values:

strFolder: Where the source files are (ensure that the trailing "\" is in the path)

strDestFolder: Where the destination password protected files should be

(ensure that the trailing "\" is in the path)

Set passwordListExcel = Workbooks.Open("C:\temp\ExcelFiles.xlsx"): Update the path to where the spreadsheet with the passwords is located as created in step #1.

7. Proceed to run the module by clicking on the play button or F5:

image

8. Reopening the reference Excel spreadsheet will now have the encrypted document name filled out:

image

9. Encrypted files should be present in the defined directory:

Generate random passwords to password protect Excel files

The following demonstrates how use VBA code to generate random passwords, encrypt Excel files in a specified folder, write the Excel file name and the respective password into an Excel spreadsheet.

1. Begin by creating an Excel spreadsheet with two columns with the heading Document Name and Password:

image

2. Save the file at a location that is outside of the folder containing the Excel files to encrypt. We will use C:\temp for this example.

image

3. Launch Excel, click on the Developer tab and click on Visual Basic

image

4. Click on the Insert tab and then Module:

image

5. Paste the following code into the module: 

Function RandomString(Length As Integer)

Dim CharacterBank As Variant

Dim x As Long

Dim str As String

'Test Length Input

  If Length < 1 Then

    MsgBox "Length variable must be greater than 0"

    Exit Function

  End If

CharacterBank = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", _

  "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", _

  "y", "z", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "!", "@", _

  "#", "$", "%", "^", "&", "*", "A", "B", "C", "D", "E", "F", "G", "H", _

  "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _

  "W", "X", "Y", "Z")

'Randomly Select Characters One-by-One

  For x = 1 To Length

    Randomize

    str = str & CharacterBank(Int((UBound(CharacterBank) - LBound(CharacterBank) + 1) * Rnd + LBound(CharacterBank)))

  Next x

'Output Randomly Generated String

  RandomString = str

End Function

Sub ProtectMultiExcelWithOpenPasswordwithExcelReferenceFile()

     Application.ScreenUpdating = False

    'Disable privacy settings warning

     Application.DisplayAlerts = False

    Dim passwordListExcel As Workbook

    Dim protectThisWorkbook As Workbook

    Dim strPassword As String, strFile As String, strFolder, strDestFolder As String

    Dim passwordRow As Integer

    Dim passwordLength As Integer

    strFolder = "C:\Excel\"

    strFile = Dir(strFolder & "*.xlsx", vbNormal)

    strDestFolder = "C:\Excel\Encrypted\"

    passwordLength = 14

    ' OPEN THE EXCEL WORKBOOK WITH PASSWORDS

   Set passwordListExcel = Workbooks.Open("C:\temp\ExcelFiles.xlsx")

    'SET ROW TO START AT 2 IGNORING THE HEADER

    passwordRow = 2

    ' BEGIN LOOPING THROUGH EXCEL WORKBOOK TO SET PASSWORDS

    While strFile <> ""

      Set protectThisWorkbook = Workbooks.Open(Filename:=strFolder & strFile)

      'GENERATE RANDOM PASSWORD WITH SPECIFIED LENGTH

      strPassword = RandomString(passwordLength)

      'OPEN EXCEL, SET PASSWORD, SAVE AND CLOSE

      With protectThisWorkbook

        .Password = strPassword

        .SaveAs Filename:=strDestFolder & "Encrypted-" & strFile, Password:=strPassword

        .Close

      End With

      ' WRITE THE DOCUMENT NAME INTO THE CELL BESIDE THE PASSWORD IN THE REFERENCE SPREADSHEET

      passwordListExcel.Worksheets("Sheet1").Range("A" & passwordRow).Formula = strFile

      ' WRITE THE PASSWORD INTO THE CELL IN THE REFERENCE SPREADSHEET

       passwordListExcel.Worksheets("Sheet1").Range("B" & passwordRow).Formula = strPassword

      'INCREMENT THE ROW COUNT BY 1 TO GO TO THE NEXT ROW

      passwordRow = passwordRow + 1

      strFile = Dir()

      Wend

       passwordListExcel.Close True

      Set passwordListExcel = Nothing

End Sub

6. Update the variables to desired values:

strFolder: Where the source files are (ensure that the trailing "\" is in the path)

strDestFolder: Where the destination password protected files should be

(ensure that the trailing "\" is in the path)

Set passwordListExcel = Workbooks.Open("C:\temp\ExcelFiles.xlsx"): Update the path to where the spreadsheet with the passwords is located as created in step #1.

7. Proceed to run the module by clicking on the play button or F5:

image

8. Reopening the reference Excel spreadsheet will now have the encrypted document name and password filled out:

image

9. Encrypted files should be present in the defined directory:

image

Using an Excel Spreadsheet with passwords to password protect Word files

The following demonstrates how to use an Excel spreadsheet with passwords to password protect a folder with Word files.

1. Begin by creating an Excel spreadsheet with two columns and fill in the Password column with as many rows of passwords as there are Word files to encrypt:

image

2. Save the file at a location that is outside of the folder containing the Excel files to encrypt. We will use C:\temp for this example.

image

3. Launch Word, click on the Developer tab and click on Visual Basic

image

4. Click on the Insert tab and then Module:

image

5. Paste the following code into the module:

Sub ProtectMultiDocWithOpenPasswordwithExcelReferenceFile()

    Application.ScreenUpdating = False

    'Disable privacy settings warning

    Application.DisplayAlerts = False

    Dim excelObject As Object

    On Error Resume Next

    Set excelObject = GetObject(, "Excel.Application")

    If Err Then

        Set excelObject = CreateObject("Excel.Application")

    End If

    On Error GoTo 0

    Dim passwordListExcel

    Dim protectThisDocument As Document

    Dim strPassword As String, strFile As String, strFolder, strDestFolder As String

    Dim passwordRow As Integer

  strFolder = "C:\Word\"

    strFile = Dir(strFolder & "*.docx", vbNormal)

    strDestFolder = "C:\Word\Encrypted\"

    ' OPEN THE EXCEL WORKBOOK WITH PASSWORDS

    Set passwordListExcel = excelObject.Workbooks.Open("C:\temp\WordFiles.xlsx")

    'SET ROW TO START AT 2 IGNORING THE HEADER

    passwordRow = 2

    ' BEGIN LOOPING THROUGH WORD WORKBOOK TO SET PASSWORDS

    While strFile <> ""

      Set protectThisDocument = Documents.Open(FileName:=strFolder & strFile)

      strPassword = passwordListExcel.Worksheets("Sheet1").Range("B" & passwordRow).Formula

      'OPEN WORD, SET PASSWORD, SAVE AND CLOSE

      With protectThisDocument

        .Password = strPassword

        .SaveAs2 FileName:=strDestFolder & "Encrypted-" & strFile, Password:=strPassword

        .Close

      End With

      ' WRITE THE DOCUMENT NAME INTO THE CELL BESIDE THE PASSWORD IN THE REFERENCE SPREADSHEET

      passwordListExcel.Worksheets("Sheet1").Range("A" & passwordRow).Formula = strFile

      'INCREMENT THE ROW COUNT BY 1 TO GO TO THE NEXT ROW

      passwordRow = passwordRow + 1

      strFile = Dir()

      Wend

      passwordListExcel.Close True

      Set passwordListExcel = Nothing

End Sub

6. Update the variables to desired values:

strFolder: Where the source files are (ensure that the trailing "\" is in the path)

strDestFolder: Where the destination password protected files should be

(ensure that the trailing "\" is in the path)

Set passwordListExcel = Workbooks.Open("C:\temp\WordFiles.xlsx"): Update the path to where the spreadsheet with the passwords is located as created in step #1.

7. Proceed to run the module by clicking on the play button or F5:

image

8. Reopening the reference Excel spreadsheet will now have the encrypted document name filled out:

image

9. Encrypted files should be present in the defined directory:

image

Generate random passwords to password protect Word files

The following demonstrates how use VBA code to generate random passwords, encrypt Word files in a specified folder, write the Word file name and the respective password into an Excel spreadsheet.

1. Begin by creating an Excel spreadsheet with two columns with the heading Document Name and Password:

image

2. Save the file at a location that is outside of the folder containing the Excel files to encrypt. We will use C:\temp for this example.

image

3. Launch Word, click on the Developer tab and click on Visual Basic

image

4. Click on the Insert tab and then Module:

image

5. Paste the following code into the module:

Function RandomString(Length As Integer)

Dim CharacterBank As Variant

Dim x As Long

Dim str As String

'Test Length Input

  If Length < 1 Then

    MsgBox "Length variable must be greater than 0"

    Exit Function

  End If

CharacterBank = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", _

  "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", _

  "y", "z", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "!", "@", _

  "#", "$", "%", "^", "&", "*", "A", "B", "C", "D", "E", "F", "G", "H", _

  "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _

  "W", "X", "Y", "Z")

'Randomly Select Characters One-by-One

  For x = 1 To Length

    Randomize

    str = str & CharacterBank(Int((UBound(CharacterBank) - LBound(CharacterBank) + 1) * Rnd + LBound(CharacterBank)))

  Next x

'Output Randomly Generated String

  RandomString = str

End Function

Sub ProtectMultiDocWithOpenPasswordwithExcelReferenceFile()

    Application.ScreenUpdating = False

    'Disable privacy settings warning

    Application.DisplayAlerts = False

    Dim excelObject As Object

    On Error Resume Next

    Set excelObject = GetObject(, "Excel.Application")

    If Err Then

        Set excelObject = CreateObject("Excel.Application")

    End If

    On Error GoTo 0

    Dim passwordListExcel

    Dim protectThisDocument As Document

    Dim strPassword As String, strFile As String, strFolder, strDestFolder As String

    Dim passwordRow As Integer

    Dim passwordLength As Integer

    strFolder = "C:\Word\"

    strFile = Dir(strFolder & "*.docx", vbNormal)

   strDestFolder = "C:\Word\Encrypted\"

    passwordLength = 14

    ' OPEN THE EXCEL WORKBOOK WITH PASSWORDS

    Set passwordListExcel = excelObject.Workbooks.Open("C:\temp\WordFiles.xlsx")

    'SET ROW TO START AT 2 IGNORING THE HEADER

    passwordRow = 2

    ' BEGIN LOOPING THROUGH WORD WORKBOOK TO SET PASSWORDS

    While strFile <> ""

      Set protectThisDocument = Documents.Open(FileName:=strFolder & strFile)

      'GENERATE RANDOM PASSWORD WITH SPECIFIED LENGTH

      strPassword = RandomString(passwordLength)

      'OPEN WORD, SET PASSWORD, SAVE AND CLOSE

      With protectThisDocument

        .Password = strPassword

        .SaveAs2 FileName:=strDestFolder & "Encrypted-" & strFile, Password:=strPassword

        .Close

      End With

      ' WRITE THE DOCUMENT NAME INTO THE CELL BESIDE THE PASSWORD IN THE REFERENCE SPREADSHEET

      passwordListExcel.Worksheets("Sheet1").Range("A" & passwordRow).Formula = strFile

      ' WRITE THE PASSWORD INTO THE CELL IN THE REFERENCE SPREADSHEET

      passwordListExcel.Worksheets("Sheet1").Range("B" & passwordRow).Formula = strPassword

      'INCREMENT THE ROW COUNT BY 1 TO GO TO THE NEXT ROW

      passwordRow = passwordRow + 1

      strFile = Dir()

      Wend

      passwordListExcel.Close True

      Set passwordListExcel = Nothing

End Sub

6. Update the variables to desired values:

strFolder: Where the source files are (ensure that the trailing "\" is in the path)

strDestFolder: Where the destination password protected files should be

(ensure that the trailing "\" is in the path)

Set passwordListExcel = Workbooks.Open("C:\temp\WordFiles.xlsx"): Update the path to where the spreadsheet with the passwords is located as created in step #1.

7. Proceed to run the module by clicking on the play button or F5:

image

8. Reopening the reference Excel spreadsheet will now have the encrypted document name and password filled out:

image

9. Encrypted files should be present in the defined directory:

image