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:
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.