Unzipping tar file using vba

Question

I am trying to unzip a .tar file using VBA. I have googled it to find the answer, but not many article talk about the unzipped . tar file.

The code I refer is below : from here: https://www.rondebruin.nl/win/s7/win002.htm because I want to unzipped the .tar file.

Step 1, I changed the below code to from .zip to .tar or .tar.gz

Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                MultiSelect:=False)

it found the file, but I failed in below line:

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

The error was:

run-time error'-214767259(80004005)':Method 'NameSpace' of object 'IsheLLdispatch6' failed.

Below the code I refer for unzipped zip file.

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

Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                    MultiSelect:=False)
If Fname = False Then
    'Do nothing
Else
    'Destination folder
    DefPath = "C:\Users\Ron\test\"    '<<< Change path
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    FileNameFolder = DefPath

    '        'Delete all the files in the folder DefPath first if you want
    '        On Error Resume Next
    '        Kill DefPath & "*.*"
    '        On Error GoTo 0

    'Extract the files into the Destination folder
    Set oApp = CreateObject("Shell.Application")
    oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

    MsgBox "You find the files here: " & FileNameFolder

    On Error Resume Next
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
End If
End Sub

Edit:

Sub ExtractAllFiles() 
    Dim File As String 
    Dim ShellStr 
    File = Dir("C:\test") 
    While (File <>"") 
        if Instr(1,File,".tar")>0 then 
            ShellStr = "C:\Program Files\PKWARE\PKZIPW -e C:\test\ " & File & _
            " C:\test\" 
            Call Shell(ShellStr, vbHide) 
        End if   
        File = Dir 
        DoEvents 
    Loop 
End Sub

It failed, run-time error '53' file not found. I located the root of pkzip.exe and wrote the code as example. I am not quite sure if i used the right path. — mei, Dec 06 '19 at 11:31
hi Tim, I don't know if i got the incorrect path or something wrong with the code. — mei, Dec 06 '19 at 16:10
Hi tim, care to type the line of executable name on comment? Thanks — mei, Dec 11 '19 at 01:18
See my edit below - that's as much as I can do since i don't have that tool to test. — Tim Williams, Dec 11 '19 at 01:32

Tim Williams · Answer 1 · 2019-12-11T01:27:45.320

0

You need to quote your file paths (and probably include the executable name)

Sub ExtractAllFiles() 
    Dim File As String 
    Dim ShellStr 
    File = Dir("C:\test\*.tar") 
    While (File <>"") 

        ShellStr = """C:\Program Files\PKWARE\PKZIPW\pkzipw.exe"" -e ""C:\test\" & _
                   File & """ ""C:\test\""" 
        Call Shell(ShellStr, vbHide) 

        File = Dir 
        DoEvents 
    Loop 
End Sub

edited Dec 11 '19 at 01:27

answered Dec 06 '19 at 16:10

Tim Williams

154,628
8
97
125

it shows error -5, do you mind explaining more the quote? i don't quite understand why we need many quotes ? thanks – mei Dec 06 '19 at 16:25
What is the text of the error message? It's best to include quotes around all file paths you pass to Shell - strictly speaking not required if your path does not include spaces, but best to always include them. To include a double quote inside a string in VBA you need to escape it by doubling it up. – Tim Williams Dec 06 '19 at 17:00
Run-time error 5:invalid procedure call or argument. I really don't know what's happened. – mei Dec 09 '19 at 16:02
On which line? Please update your question with your current code. – Tim Williams Dec 09 '19 at 18:07
Thanks tim.it didnt show any error message. But the tar file wasnt extracted it. Please refer to below photo – mei Dec 11 '19 at 16:13
Check for open processes in Task Manager which might have a lock on the archive. – Tim Williams Dec 11 '19 at 17:10
How could i check if there is a lock? – mei Dec 12 '19 at 05:45
Reboot your PC, then try your code again, but use `vbNormalFocus` in your Shell call instead of `vbHide` - that might give you a clue. – Tim Williams Dec 12 '19 at 06:32
Thank you Tim!! I will try next monday since i am off from work and will let you know how it goes. – mei Dec 12 '19 at 06:56
@mei Add`Debug.Print ShellStr`before you call Shell. That prints the ShellCommand to the immideate window, where you can copy it and then paste it to a manually opened command Shell. If it fails sth. wrong.Is the missing file type (.exe) of `pkzipw`missing because it doesn't show it to you? Then open explorer.exe ->options -> view -> disable the “Hide extensions for known file types” as this is a security risk (think of a fle like`Virus.pdf.exe`that shows only``Virus.pdf.`without extension). – ComputerVersteher Dec 12 '19 at 07:51

Unzipping tar file using vba

1 Answers1