Create .Zip Password file with Excel? Is it even possible?

So recently our Company requires a mechanism to store our files/ documents in a shared folder within our local network server. However, to enhance its access security, there was a requirement to zip and password the file for being opened/ edited. On top of that, we need to log the password files within a log book.

Well, so I thought, could Excel help me doing these whole things in one package?

Surprisingly, the answer is YES!

So we created a log book within Excel, that could automate the process of zipping file and protect it with a password.

One requirement here is your laptop/ PC must have the open source 7zip program to be installed. You can download the program here - https://www.7-zip.org/download.html

The VBA codes that I want to share with you are:

1. The core codes on how to perform the zipping and password the file.

2. Delete the file after it is being zipped and passworded.

3. The code to check if any of the file link is broken.

Here is a glance of the simple log book in Excel format:

Log book for protecting your files by Zipping and Password them.

So, the interesting part are the codes below:

1. This is the core codes to zip and password the file

Function passwordTheFile(filePath As String, filePassword As String)

strDestFileName = filePath & ".zip"

strSourceFileName = filePath

str7ZipPath = "C:\Program Files\7-Zip\7z.exe"

strPassword = filePassword

strCommand = str7ZipPath & " -p" & strPassword & " a -tzip """ & strDestFileName & """ """ & strSourceFileName & """"

Shell strCommand

End Function

2. Delete the file after it is being zipped and passworded

Kill GetFile("C:\yourFilePath\filename.txt")

3. The code to check if any of the file link is broken

Function fileExists(s_directory As String, s_fileName As String) As Boolean

Dim obj_fso As Object

Set obj_fso = CreateObject("Scripting.FileSystemObject")

fileExists = obj_fso.fileExists(s_directory & s_fileName)

End Function

=====================================================

So those are the codes that serves as fundamentals to run the program. Let me know your comments and thoughts.

To download the complete version of the file, check out this link - https://drive.google.com/open?id=17g9jLKAg4TyGEJLyXz4mfZ-h-j6wnuU1

Cheers.