Rolly_Sefu
Board Regular
- Joined
- Oct 25, 2013
- Messages
- 149
Hello, i am generating a few pdf's from a worksheet, and i want to zip and password each file individually.
The code below works, but when i repeat the code multiple times it does not password protect all the zip files.
Any ideas why ? do i somehow need to reset zip parameters after each item ?
Also asked here Password protect a zipped file
Thank you
The code below works, but when i repeat the code multiple times it does not password protect all the zip files.
Any ideas why ? do i somehow need to reset zip parameters after each item ?
VBA Code:
Sub Test()
LastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Sheets("sheet1").Range("A2:A" & LastRow)
For Each cell In Rng
Sheets("Sheet2").Range("B2") = cell.Value
Sheets("Sheet2").Range("B3") = cell.Offset(0, 1).Value
Sheets("Sheet2").Range("B5") = cell.Offset(0, 2).Value
UserName = Sheets("Sheet2").Range("B2") & " " & Sheets("Sheet2").Range("B3")
Sheets("Sheet2").ExportAsFixedFormat Type:=xlTypePDF, _
FileName:="C:\Users\Roland\Desktop\Zip test\" & UserName & ".pdf"
strDestFileName = "C:\Users\Roland\Desktop\Zip test\" & UserName & ".zip"
strSourceFileName = "C:\Users\Roland\Desktop\Zip test\" & UserName & ".pdf"
str7ZipPath = "C:\Program Files\7-Zip\7z.exe"
strPassword = cell.Offset(0, 3)
strCommand = str7ZipPath & " -p" & strPassword & " a -tzip """ & strDestFileName & """ """ & strSourceFileName & """"
Shell strCommand
Application.Wait (Now + TimeValue("0:00:01"))
Kill strSourceFileName
Next cell
End Sub
Also asked here Password protect a zipped file
Thank you
Last edited by a moderator: