Save as zip with a password

[Ang12345]

Hello,
I have an Excel VBA with the following code to save a file

Please Login or Register  to view this content.

It works fine, but I would like to know if there is a way I can get it to save as a zip file with a password.

Thanks

[royUK]

There's sample code here

[Ang12345]

Is there a more up to date example? This is not working on excel 2007. I am also trying to zip with secure zip with a password if that makes a difference. It is my windows default.

[Ang12345]

Hello,
I am using the code from the page as suggested

Please Login or Register  to view this content.

I am getting an error on the line

oApp.Namespace(FileNameZip).CopyHere FileNameXls

The error is:

Run-time error'-2147024894 (80070002)':
Method 'NameSpace' of object 'IShellDispatch4' failed

any idea what is wrong?

[Leith Ross]

Hello Ang12345,

This version has worked well for me. Try it out and let me know what happens.

Please Login or Register  to view this content.

Macro Example

Please Login or Register  to view this content.

[Ang12345]

Hi Leith,
I dont think that will work because it uses WinZip. I have to use securezip. I gave it a shot and I am getting an error for

Please Login or Register  to view this content.

sub or function undefined

[Leith Ross]

Hello Ang12345,

I will look into to this and see if it has command line capability. If it does then it maybe possible to initiate zipping the file with VBA.

[Rolly_Sefu]

Hello, i am trying a similar code with 7zip, but i'm having a trouble with consistency, first test is successful, i delete the zip file remake the test and then not all zipped files are password protected.
Any ideas why ? do i somehow need to reset zip parameters after each item ?

Please Login or Register  to view this content.

Thank you

[FDibbins]

Hello, i am trying a similar code with 7zip, but i'm having a trouble with consistency, first test is successful, i delete the zip file remake the test and then not all zipped files are password protected.
Any ideas why ? do i somehow need to reset zip parameters after each item ?

Please Login or Register  to view this content.

Thank you

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread