Zip Files using VBA

[rkgrkg999]

I have 50 excel files in folder and I want only 3 files to zip(winzip), let the file name say abc1.xls,xyz1.xls, abc2.xls .

I need the VBA code to zip three different zip file with password "pass".

Can anyone help me.

 

[Andrew Poulsom]

Welcome to MrExcel.

Look here:

Zip file or files with the default Windows zip program (VBA)

 

[rkgrkg999]

Thanks Andrew for your reply. I already have the coding of Ron, but this code either will zip all the files in folder or manually you have to select the files. This code will the save files in current date.
I need the code that zip only 3 files ( with same name)from a folder instead of all file and add the password to zip files. Don't need to select the files.

Welcome to MrExcel.Look here:Zip file or files with the default Windows zip program (VBA)

 

[Andrew Poulsom]

Why don't you amend Ron's code so that FName is a constant array?

FName = Array("abc1.xls", "xyz1.xls", "abc2.xls")

 

[rkgrkg999]

I am not the VBA expert, can you modify the code, so that three different zip files are create in same folder with password protected.

Why don't you amend Ron's code so that FName is a constant array?

FName = Array("abc1.xls", "xyz1.xls", "abc2.xls")

 

[Andrew Poulsom]

What did you try?

 

[rkgrkg999]

I have wrote following code

Private Sub ZipFile_FX(ZipFileName As String, fileToBeZipped As String)
Const ZIPEXELOCATION = "c:\program files\winzip\winzip32.exe"
Shell ZIPEXELOCATION & " -a " & Chr(34) & ZipFileName & Chr(34) & _
" " & Chr(34) & fileToBeZipped & Chr(34), vbNormalFocus
End Sub

sub zips()
Call ZipFile_FX("c:\a.zip", "c:\a.xls")
end sub

Problem with with code is that when I run the program then winzip program is open asked me to run, then I have to click on agree. I don't want winzip ask me to run. Second I add password and add more files in this.

 

[bapcki1]

Why don't you amend Ron's code so that FName is a constant array?

FName = Array("abc1.xls", "xyz1.xls", "abc2.xls")

Hi Andrew,

I am quite desperate to automate compression of .psr files using Ron's script and the ARRAY adjustment you mentioned above.

When I make the ARRAY adjustment the code hangs on:

Application.Wait (Now + TimeValue("0:00:01"))

The Zip folder is created but then I don't think it copies the files I want into the newly created ZIP folder. When I exit the code manually, there are no files in the ZIP folder.


I eventually want to ZIP .psr's but I am trying to adjust Ron's code so that I can specify the file names and file locations in cells...


Can you help me please?


Thank you,


Paddy

 

[Andrew Poulsom]

Sorry I don't even know what a .psr is, never mind how to zip one.

 

[bapcki1]

Sorry I don't even know what a .psr is, never mind how to zip one.

- ennit, that makes 2 of us there! I'll figure all that out once I manage to adjust Ron's code so that I can state the files I want to zip in cells.

I am currently trying to adjust Ron's code and using .xls files to practice at the moment.

Any ideas?


Many thanks,

Paddy