Unzipping files with VBA

[dugdugdug]

The following code by Ron De Bruin for unzipping files with VBA works well.

I have adapted it so that instead of choosing a particular file, it looks at a folder and unzips all files within it.

What I don't understand is this block:

Do

    'populate Exitcode variable
    GetExitCodeProcess hProcess, ExitCode
    DoEvents

Loop While ExitCode = STILL_ACTIVE

If I change it to:

Do While ExitCode = STILL_ACTIVE

    'populate Exitcode variable
    GetExitCodeProcess hProcess, ExitCode
    DoEvents
    
Loop

it won't unzip all files but a random slection within the folder.

Does anybody know why?

Thanks

Declare Function OpenProcess Lib "kernel32" _
                             (ByVal dwDesiredAccess As Long, _
                              ByVal bInheritHandle As Long, _
                              ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
                                    (ByVal hProcess As Long, _
                                     lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Sub

'With this example you browse to the zip or 7z file you want to unzip
'The zip file will be unzipped in a new folder in: Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'The name of the folder that the code create in this folder is the Date/Time
'You can change this folder to this if you want to use a fixed folder:
'NameUnZipFolder = "C:\Users\Ron\TestFolder\"
'Read the comments in the code about the commands/Switches in the ShellStr
'There is no need to change the code before you test it

Sub A_UnZip_Zip_File_Browse()
    Dim PathZipProgram As String, NameUnZipFolder As String
    Dim FileNameZip As Variant, ShellStr As String

    'Path of the Zip program
    PathZipProgram = "C:\program files\7-Zip\"
    If Right(PathZipProgram, 1) <> "\" Then
        PathZipProgram = PathZipProgram & "\"
    End If

    'Check if this is the path where 7z is installed.
    If Dir(PathZipProgram & "7z.exe") = "" Then
        MsgBox "Please find your copy of 7z.exe and try again"
        Exit Sub
    End If

    'Create path and name of the normal folder to unzip the files in
    'In this example we use: Application.DefaultFilePath
    'Normal if you have not change it this will be your Documents folder
    'The name of the folder that the code create in this folder is the Date/Time
    NameUnZipFolder = Application.DefaultFilePath & "\" & Format(Now, "yyyy-mm-dd h-mm-ss")
    'You can also use a fixed path like
    'NameUnZipFolder = "C:\Users\Ron\TestFolder"

    'Select the zip file (.zip or .7z files)
    FileNameZip = Application.GetOpenFilename(filefilter:="Zip Files, *.zip, 7z Files, *.7z", _
                                              MultiSelect:=False, Title:="Select the file that you want to unzip")

    'Unzip the files/folders from the zip file in the NameUnZipFolder folder
    If FileNameZip = False Then
        'do nothing
    Else
        'There are a few commands/Switches that you can change in the ShellStr
        'We use x command now to keep the folder stucture, replace it with e if you want only the files
        '-aoa Overwrite All existing files without prompt.
        '-aos Skip extracting of existing files.
        '-aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt).
        '-aot auto rename existing file (for example, name.txt will be renamed to name_1.txt).
        'Use -r if you also want to unzip the subfolders from the zip file
        'You can add -ppassword if you want to unzip a zip file with password (only 7zip files)
        'Change "*.*" to for example "*.txt" if you only want to unzip the txt files
        'Use "*.xl*" for all Excel files: xls, xlsx, xlsm, xlsb
        ShellStr = PathZipProgram & "7z.exe x -aoa -r" _
                 & " " & Chr(34) & FileNameZip & Chr(34) _
                 & " -o" & Chr(34) & NameUnZipFolder & Chr(34) & " " & "*.*"

        ShellAndWait ShellStr, vbHide
        MsgBox "Look in " & NameUnZipFolder & " for extracted files"

    End If
End Sub

 

[Jeffrey Smith]

I could be wrong, but it looks like the DO LOOP is making sure nobody is trying to stop the unzip process. It sits and waits, but is checking the unzip process continuously. Changing the line "While ExitCode = STILL_ACTIVE" to the beginning of the loop may prevent the unzip process from running because it hasn't recieved the "ExitCode" value.

 

[dugdugdug]

I could be wrong, but it looks like the DO LOOP is making sure nobody is trying to stop the unzip process. It sits and waits, but is checking the unzip process continuously. Changing the line "While ExitCode = STILL_ACTIVE" to the beginning of the loop may prevent the unzip process from running because it hasn't recieved the "ExitCode" value.

So is it correct for me to say these four variations of the Do Loop indeed have its subtle differences, despite books saying they all do the same thing?

Do Until ..... Loop, Do ..... Loop Until, Do While ..... Loop and Do ..... Loop While

 

[Jeffrey Smith]

Yes. It depends on the criteria that you use. If you want to test for Still_Active before the loop starts then you will need to retrieve the value first. DoEvents is there to allow operating system processes to work while the do loop is running, otherwise the do loop would take up all the cpu.

GetExitCodeProcess hProcess, ExitCode

Do Until ExitCode <> STILL_ACTIVE
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
Loop

 

[arpankarmakar]

I have some 10 zip files and each zip file contains one .csv file. I want to rename the .csv file after unzip. Please help. Thanks.

 

[Jeffrey Smith]

You should try looking in Excel Help. This is what I found using RENAME as the search criteria:
Name Statement


This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010.
Renames a disk file, directory, or folder.


Syntax


Name oldpathname As newpathname


The Name statement syntax has these parts:


Part
Description

oldpathname
Required. String expression that specifies the existing file name and location — may include directory or folder, and drive.

newpathname
Required. String expression that specifies the new file name and location — may include directory or folder, and drive. The file name specified by newpathname can't already exist.



Remarks


The Name statement renames a file and moves it to a different directory or folder, if necessary. Name can move a file across drives, but it can only rename an existing directory or folder when both newpathname and oldpathname are located on the same drive. Name cannot create a new file, directory, or folder.


Using Name on an open file produces an error. You must close an open file before renaming it. Name arguments cannot include multiple-character (*) and single-character (?) wildcards.


example
This example uses the Name statement to rename a file. For purposes of this example, assume that the directories or folders that are specified already exist. On the Macintosh, "HD:" is the default drive name and portions of the pathname are separated by colons instead of backslashes.



Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName