0

I'm trying to unzip 589 PDF files from one .zip file but upon checking the unzipped files in my destination folder, only 100+ are unzipped. I guess the code does not wait.

I think I got this code from Ron de Bruin but I cannot find the code on how can I make my script to wait for all the files to be extracted before continuing to the next step.

Sub UnzipFile_Click()
Dim FSO As Object
Dim oApp As Object
Dim Fname As Variant
Dim FileNameFolder As Variant
Dim DefPath As String
Dim strDate As String

Application.ScreenUpdating = False


Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                        MultiSelect:=False)
If Fname = False Then
    'Do nothing
Else
    'Root folder for the new folder.
    'You can also use DefPath = "C:\Users\Ron\test\"
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    'Create the folder name
    strDate = Format(Now, " mm-dd-yy")
    FileNameFolder = DefPath & "DFM Invoices " & strDate & "\"

    'Make the normal folder in DefPath
    MkDir FileNameFolder

    'Extract the files into the newly created folder
    Set oApp = CreateObject("Shell.Application")

    oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).Items


    MsgBox "You may find the unzipped files here: " & FileNameFolder
    TextBox1.Value = FileNameFolder

    On Error Resume Next
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
End If
End Sub
girlvsdata
  • 1,596
  • 11
  • 21
Unica delos Reyes
  • 1
  • 2
  • 2
    Possible duplicate of [Wait for Shell to finish, then format cells - synchronously execute a command](https://stackoverflow.com/questions/8902022/wait-for-shell-to-finish-then-format-cells-synchronously-execute-a-command) – Comintern Sep 20 '18 at 23:16
  • After calling `CopyHere` you could go into a loop which checks the count of files in the destination folder: exit the loop when the count is the same as the count of objects in the zip. Add a "timeout" check in the loop so you don't get stuck in the event something goes wrong. Also maybe look here: https://www.ozgrid.com/forum/forum/help-forums/excel-general/68041-extract-zip-files-without-winzip – Tim Williams Sep 21 '18 at 00:22
  • Simple. Just unzip the files in a 'Macro1' or whatever and call 'Macro2' from that. Done. https://www.excelcampus.com/library/vba-call-statement-run-macro-from-macro/ – ASH Sep 30 '18 at 17:22

0 Answers0