Extract all .gz file in folder using VBA Shell command

Question

I have the following VBA code to extract all the files within a given directory.

Sub extractAllFiles()

    Dim MyObj As Object, MySource As Object, file As Variant
    Dim shellStr As String

    file = Dir("C:\Downloads\")
    While (file <> "")
        If InStr(file, ".gz") > 0 Then
            shellStr = "winzip32 -e C:\Downloads\" & file & " C:\Downloads\"
            Call Shell(shellStr, vbHide)
        End If
        file = Dir
    Wend

End Sub

When I execute this sub routine I get a Run-Time error 53, "File Not Found" error. When I copy the shellStr... Example: winzip32 -e C:\Downloads\file1.gz C:\Downloads\ and execute it from command prompt, it works perfectly! I get the text file within file1.gz extracted to the downloads directory. However running it from VBA doesn't seem to work.

Can anyone shed some light?

jacouh · Accepted Answer · 2013-11-26T17:45:52.137

4

You should try with the full path of the shell command, like this, that worked for me:

Sub extractAllFiles()

  Dim MyObj As Object, MySource As Object, file As Variant
  Dim shellStr As String

  file = Dir("C:\Downloads\")
  While (file <> "")
    If InStr(1, file, ".gz") > 0 Then
      shellStr = "C:\Program Files (x86)\WinZip\winzip32 -e C:\Downloads\" & file & " C:\Downloads\"
      Call Shell(shellStr, vbHide)
    End If
    file = Dir
  Wend

End Sub

My winzip is installed as C:\Program Files (x86)\WinZip\winzip32. You should use yours. Your install path may be:

C:\Program Files\WinZip\winzip32

edited Nov 26 '13 at 17:45

answered Nov 26 '13 at 17:40

jacouh

8,473
5
32
43

OMG I can't believe it. I was missing the path to winzip. Just tried it and worked like a charm. One question though, how do I prevent the windows explorer from popping up to show the contents of the directory after it has been extracted? – JTFRage Nov 26 '13 at 17:51
1

Nevermind, I got it. It was a setting in WinZip under decompress options. – JTFRage Nov 26 '13 at 18:00
This is rather a Winzip behavior. It's useful when I launched winzip from Access VBA, to see the directory. I don't know how. – jacouh Nov 26 '13 at 18:00

score 1 · Answer 2 · answered Dec 13 '13 at 18:31

1

WinZip path needs to be absolute path:

C:\Program Files\WinZip\winzip32'

answered Dec 13 '13 at 18:31

score 1 · Answer 3 · answered Dec 16 '13 at 19:57

1

Check your WinZip path. That needs to be fine fixed to the full path to your WinZip.

answered Dec 16 '13 at 19:57

score 0 · Answer 4 · edited May 23 '17 at 10:31

0

Using the logic from this SO post, try the below code:

Sub ExtractAllFiles()
    Dim FileName As String
    Dim ShellStr
    FileName = Dir("C:\Downloads\*.gz")
    Do While Len(FileName) > 0
        ShellStr = "winzip32 -e " & FileName & " C:\Downloads"
        Call Shell(ShellStr, vbHide)
        FileName = Dir
    Loop
End Sub

Let us know if this helps.

edited May 23 '17 at 10:31

Community

1
1

answered Nov 26 '13 at 16:57

WGS

13,969
4
48
51

Extract all .gz file in folder using VBA Shell command

4 Answers4

Linked