Zip File Shell Creation Problem 3

[Sech]

I am currently updating a database to allow exported Excel files to be automatically zipped using Winzip and the shell command. I have run various tests using the below lines:

Call Shell("c:\Program Files\WinZip\Winzip32.exe -A " & """" & sZipName & """" & " " & """" & sFileName & """")
Kill sFileName

This seems to work absolutely fine, zipping each file after it has been created, and then deleting the original file. However I have ran the process out of office hours using the form timer to set it off and this morning I have found that Winzip errored on the first file with the following error:

No files were found for this action that match your criteria - nothing to do (T:\Customer MI\Trending Reports\Customer\00000982 Trending 150404.zip)

Does anyone know why this error has occured? Is it because it is trying to zip the file before it has finished saving it to the drive? If I include the DoEvents command will that fix it?

 

[Sech]

Ok I tried running the code with the DoEvents command in before the Shell line. And this time it brings up the error even before the file has been created (the creation line is before the DoEvents command). Does anyone know what is going on and how to fix this?

 

[Sech]

Ok, the process works fine when you step through it line by line, but if you run it properly it falls over. DoEvents does not seem to make any difference. Has anyone experienced this before?

 

[Sech]

Ok I worked this one out... so I'll put the solution down in case anyone else does a search for it. The reason why the error was occuring is not due to it attempting to zip the file before it is fully created, it is in fact the opposite. It is because it sets off Winzip to zip the file but then the next line of code deletes the file. Winzip does not have time to finish zipping before the file ceases to exist, and therefore errors. So I will have to delete the original files using a seperate process ran after creating and zipping all the files.

 

[jbpelletier]

hi

here some code that "dbsquared" gave me. The code unzip a file and wait till the process isn't finish.

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Const SYNCHRONIZE = &H100000
Private Const INFINITE = -1&

Sub unzip(Tempstr As String, File As String)
Dim sPWZipPath, sCmdLine, sTargetPath, sSourceFile As String
Dim iRunTaskID As Integer
Dim hProc As Long

sPWZipPath = "C:\Program Files\winzip\winzip32.exe" 'Setup the needed paths
sTargetPath = "C:\" 'Path to unzip ZIP contents to
sSourceFile = Tempstr 'Source ZIP file
sCmdLine = sPWZipPath & " -min -e " & sSourceFile & " c:\" ' here is the winzipsetup and commands -min = minimized, -e =extract
iRunTaskID = Shell(sCmdLine, vbHide)
DoEvents
hProc = OpenProcess(SYNCHRONIZE, 0, iRunTaskID)
If hProc <> 0 Then
WaitForSingleObject hProc, INFINITE
CloseHandle hProc
End If

End Sub

for other winzip cldline option visit :

http://www.winzip.com/xcmdline.htm

hope this help
jb

 

[PHV]

Try something like this:
Set sh = CreateObject("WScript.Shell")
rc = sh.Run("Your Shell Command Here", 1, True)
The 3rd argument (True) is for waiting completion of the command, so the file you want to delete is no longer open.
rc will be the return code of the command

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Dimandja]

Use Winzip switches to tell it to delete a file after zipping it.

look into -m or rather -k

Dimandja

 

[Sech]

Thanks for all your suggestions. Dimandja, where do I find out about the available switches for Winzip? Also do I need the special Command Prompt Add On or are the switches present in the latest version?