Creating a Zip File 1

[MikeC14081972]

Was wondering if anyone knows if it is possible, in VBA, to take a file and zip it using the FileSystemObject perhaps????

Thanks

 

[gwinn7]

One solution that comes to mind is using the VBA Shell command and run the Winzip.exe with the appropriate parameters. Something like...

Shell "c:\winzip.exe c:\myfile.doc c:\myfilecompressed.zip"

Hope that helps,
Gary
gwinn7

 

[Remou]

Another possibility:

http://www.tek-tips.com/viewthread.cfm?qid=1130420&page=1

 

[MikeC14081972]

Many Thanks Remou.

have tweaked your code to my requirements and it works a treat.

 

[MikeC14081972]

Remou,

One more question, Is there a way of halting the code whilst the file is added to the zip folder?

 

[stanrobbins]

Here is how I zip files. I use 7zip and loop the process while its still running.

Option Compare Database
Option Explicit
Const s7zipPrg As String = "\\mbs2\users\shared\software\7-zip\7z"

Public Function MBS_ZipFile(sfolder As String, sfile As String, szip As String, Optional spass As String = "") As Boolean

Dim bsuccess As Boolean
bsuccess = True

Dim scmd As String
If spass <> "" Then
scmd = s7zipPrg & " a -tzip -y " & sfolder & szip & " " & sfolder & sfile & " -p" & spass
Else
scmd = s7zipPrg & " a -tzip -y " & sfolder & szip & " " & sfolder & sfile
End If

Dim retval
retval = Shell(scmd)

''' Get the process handle from the task ID returned by Shell.
Dim lProcess, lExitCode, lResult
lProcess = OpenProcess(MBS_PROCESS_QUERY_INFORMATION, 0&, retval)

''' Check for errors.
If lProcess <> 0 Then
''' Loop while the shelled process is still running.
Do
''' lExitCode will be set to STILL_ACTIVE as long as the shelled process is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
DoEvents
Sleep (1000)
Loop While lExitCode = MBS_STILL_ACTIVE
End If

If Trim(Dir(sfolder & szip)) = "" Then
bsuccess = False
End If

MBS_ZipFile = bsuccess

End Function

Hope it helps.