List Contents of a zip(TAR) file using VBA

Question

I trying to write much larger code to email a list of files in a TAR file and then send it in an email but the last thing i am struggling with is the actual listing of the contents of the TAR file. the code I have tried so far is:

Public r As Long
Sub Test()

Dim strPath As String
Dim sh, n, x, i


'Change Path To Suit
'strPath = ThisWorkbook.Path & "\"
strPath = "H:\99 - Temp\"


Set sh = CreateObject("Shell.Application")
x = GetFiles(strPath, "*.TAR", True)
r = 7

For Each i In x
Set n = sh.NameSpace(i) <----------
Recur sh, n
Next i
End Sub

Sub Recur(sh, n)
Dim i, subn, x As Long, p As Long


For Each i In n.Items
If i.isfolder Then
Set subn = sh.NameSpace(i)
Recur sh, subn
Else
p = LastPos(i.Path, "\")

Debug.Print Mid(i.Path, p + 1)
'Cells(r, 1) = Mid(i.Path, p + 1)
r = r + 1
End If
Next i
End Sub


Function GetFiles(StartPath As String, FileType As String, SubFolders As Boolean) As Variant
StartPath = StartPath & IIf(Right(StartPath, 1) = "\", vbNullString, "\")
GetFiles = Split(Join(Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & StartPath & 
FileType & """ " & IIf(SubFolders, "/S", vbNullString) & " /B /A:-D").StdOut.ReadAll, vbCrLf), ":"), 
"#"), "#")
End Function


Function LastPos(strVal As String, strChar As String) As Long
LastPos = InStrRev(strVal, strChar)
End Function

But I can a runtime error '-2147467259 (80004005)':

Method 'NameSpace of Object 'IShellDispatch6' failed

so I tried this one:

Sub test99()
Dim n As Variant

Set sh = CreateObject("shell.application")
Set n = sh.NameSpace("H:\99 - Temp\test.TAR")
For Each i In n.Items <-------------
Debug.Print i.Path
Next
End Sub

which returns another run-time error 91: Object variable or with block variable not set.

I am comfortable with VBA but really struggling to integrate shell.

ideally the end goal is to get the file open window, select the TAR file that I need read (it's not always in the same folder so need it flexible) and then list the files in the TAR.

Thank you

Tim Williams · Accepted Answer · 2021-01-22T23:41:15.667

Using ShellRun concept from here: Capture output value from a shell command in VBA?

Working on Win10

Sub tester()
    Dim p, s, col, e
    
    p = "C:\Blah\Temp\Temp.tar"
    Set col = ShellOutput("tar -tf """ & p & """")
    
    Debug.Print col.Count; " entries returned"
    Debug.Print "--------------------"
    For Each e In col
        Debug.Print e
    Next e
    
End Sub

'Run a shell command, returning the output as a collection of lines
Public Function ShellOutput(sCmd As String) As Collection
    Dim sLine, col As Collection
    Set col = New Collection
    With CreateObject("WScript.Shell").Exec(sCmd).StdOut
        While Not .AtEndOfStream
            sLine = .ReadLine
            If sLine <> "" Then col.Add sLine
        Wend
    End With
    Set ShellOutput = col
End Function

List Contents of a zip(TAR) file using VBA

1 Answers1

Linked