password protect zip files through vba

[VinodN]

Hi,

I have some folders that have been zipped and saved on the drive. I need to protect these zipped folders using passwords mentioned in the database. Is this possible with the help of a macro? I have uploaded a sample excel database file.

Can someone please help?

Regards,
Vinod Nair

 

[Chihiro]

There's no way to PW protect zipped files or create zip files with default Windows ZIP. You need external program.

Not sure if you can password protect already created Zip, but you can use VBA to create Zip file with password through external program and using Shell.

Shell is used to run variable path with -p option.

Alternately use PowerShell script to do this instead of VBA.

Also found this.
http://www.excelforum.com/excel-pro...ord-protected-zip-file-with-excel-and-vb.html

 

[VinodN]

I have already created the zip folders through a macro just need a script to password protect the same. This script can then be added to the existing macro of creating zip folders.
Would it be possible for you to share the script using Shell.

Thanks,
Vinod

 

[Chihiro]

You need 3rd party software. Not through Windows Zip. Do you have one?

 

[Chihiro]

To give example of how Shell would be used via VBA, using 7zip as 3rd party software.

Dim oPath As String
Dim dPath As String
Dim pPath As String
Dim pw As String
Dim shellCommand as String

oPath = "Original file Path\file name"
dPath = "Desination path\file name"
pPath = "Zip Program path\file name"
pw = "your Password"

shellCommand = pPath & " -p" & pw & " a -tzip""" & dPath & """ """ & oPath & """
Shell shellCommand

Example of Shell command itself.

$cmd = 'C:\xxx\7z.exe'
$prm = '-p', 'password', 'a', '-tzip', 'C:\xxx\dest.zip', 'C:\xxx\orig.xlsx'

& $cmd $prm

 

[VinodN]

Thank you Chihiro for your help. Appreciate it.

I could achieve what I wanted with the help of the commands provided by you.

I was just trying another option also;
Instead of creating zip folder with pdf files in it. I was thinking if I can directly password protect the pdf files and avoid the zip process.

Is this anything that you can help?

 

[Chihiro]

PDF also needs 3rd party software to password protect it.

Refer to link below.
http://chandoo.org/forum/threads/ma...ssword-protected-pdf-files.25197/#post-151181

 

[VinodN]

Hi,

Based on my script below I am selecting the folders to be zipped based on the excel database. the records are from row 3 to 33 which is defined in the script. My challenge here is that I cannot keep the last record as 33 as it could be more or less than that. I need to define the range based on the data in my database. If I give a random range it creates blank zip folders if there is no data on a particular row.

Below is the script;
Dim i As Long
For i = 3 To 33 '***EDIT THIS TO SUIT YOUR NEEDS***
With Sheets("Data")
NameZipFile = .Range("f" & i).Value & ".zip"
FolderName = .Range("e" & i).Value
pw = .Range("g" & i).Value
End With

Can I please get some help on this?

 

[Chihiro]

It would be like this. For Range make sure to have Columns in CAP or it may give you error.

Dim i As Long
With Sheets("Data")
For i = 3 To .Range("F" & Rows.Count).End(xlUP).Row
NameZipFile = .Range("F" & i).Value & ".zip"
FolderName = .Range("E" & i).Value
pw = .Range("G" & i).Value
End With