Best solution to zip a file from VBA (1 Viewer)

[Hello1]

I need to zip a .xml file after its creation, the name of the zip file would be the same as the xml file just without the .xml extension of course.

I have been researching a little and some are doing it with WinZip, others with 7Zip and there is also windows default which probably would be best because it doesnt require any 3rd software but looks like I need to have some pause in the code which I dont like that much.

Anyone who was working with this and whats your suggestion?
Thanks!

 

[theDBguy]

Hi. Just curious why you would need a pause, are you trying to do something else to the zip file after compressing it?

 

[Hello1]

Sub makeZipFile(pathToZipFolder As Variant, zippedFileName As Variant)

Dim ShellApp As Object

‘First we create an empty zip file
Open zippedFileName For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1

‘Next we copy the files & folders into the zip file
Set ShellApp = CreateObject(“Shell.Application”)
ShellApp.Namespace(zippedFileName).CopyHere ShellApp.Namespace(pathToZipFolder).items

‘We use loopng mechanism to process the zipping with a pause value since zipping the files will take some time
On Error Resume Next
Do Until ShellApp.Namespace(zippedFileName).items.Count = ShellApp.Namespace(pathToZipFolder).items.Count
Application.Wait (Now + TimeValue(“0:00:01”))
Loop
On Error GoTo 0

End Sub

Sub zipMyFiles()
Call makeZipFile(“C:\Files-To-Be-Zipped\”, “C:\My-Zipped-Files\my-zipped-files.zip”)
End Sub

The code I found, says it needs the pause because zipping takes some time. The code is for excel VBA, so I would need to replace "Application.Wait (Now + TimeValue(“0:00:01”))" with something else

 

[theDBguy]

The code I found, says it needs the pause because zipping takes some time. The code is for excel VBA, so I would need to replace "Application.Wait (Now + TimeValue(“0:00:01”))" with something else

I see. I can't test this now but I think you could just try using DoEvents rather than a Pause/Wait to let Windows finish with what it's doing.

 

[Hello1]

Thanks, I will try I a bit later.

 

[theDBguy]

Thanks, I will try I a bit later.

Hi. In re-reading the code you posted above, it looks like the author is zipping the whole folder with multiple files in it. Will you be doing the same thing or just trying to zip a single file?

 

[Hello1]

I actually want only one flie which I create before the zipping process.

 

[theDBguy]

I actually want only one flie which I create before the zipping process.

That's what I thought, which is why I wondered why you would need a pause at all. I say try out the code and see if it doesn't work for you.

 

[Hello1]

Alright, I will test it out a bit later. I did try it a little and the code didn't want to create a zip file but I will check later in more details.

Edit: now that I think about it probably because my file name had the xml extension, so I need to change it into zip.

 

[Hello1]

At the end I implemented this Zip files with 7zip
Not whole code tho

 

[sonic8]

That's what I thought, which is why I wondered why you would need a pause at all.

The issue with the Windows built in ZIP functionality is that it is working asynchronously. The method to add a file to the zip returns immediately but the actual ZIP-process is not yet complete then. - This is only an issue if you want to do something with the ZIP file right away, like emailing it.

I wrote a bit about ZIP-Archives with VBA/Shell32 mentioning some of the issues.