CODEWITHSUNDEEP
excelvbazip
Paul Gibson
Paul Gibson

Reputation: 634

VBA Read a Zip File

I have seen some code to do this, but for me it is not working. I am running Windows 7, and excel 2010 (from office 2010). My code:

Public Sub GetZipContents()
    Dim oApp As Shell32.Shell
    Set oApp = New Shell32.Shell
    Dim strFile As String
    Dim xFname
    Dim xRow As Long
    Dim newRow As Long
    Dim rNew As Range
    Dim fileNameInZip
    Dim oFolder As Variant
    Dim i As Integer
    i = 1
    xRow = 0
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Select the zip to get file names from"
    fd.Filters.Clear
    fd.Filters.Add "Zip Files", "*.zip"
    fd.FilterIndex = 1
    If fd.Show = -1 Then
        strFile = fd.SelectedItems(1)
        oFolder = oApp.Namespace(strFile).Items
        Range("A" & i).Value = strFile
        i = i + 1
        For Each fileNameInZip In oFolder
            Range("A" & i).Value = fileNameInZip
            i = i + 1
        Next
        Set oApp = Nothing
    End If

End Sub

I have also used fileNameInZip as a Variant, but the output is the same. Regardless of the zip file I chose, my output (text version attached, the screen shot is better but I can't attach images as this is my first post ... the first line is the name of the zip file, the next are the Items from the Namespace call) is always the same. I am at a loss because every site I have seen has similar code as the answer. Any ideas what is going on (the files within are generally pdfs, not &Open etc.)?

C:\Users\PGibson\Downloads\CW985786-T-00136.zip

&Open

Cu&t

&Copy

&Delete

P&roperties

Upvotes: 2

Views: 5616

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Missing Set on the marked line...

Public Sub GetZipContents()
    Dim oApp As Shell32.Shell
    Set oApp = New Shell32.Shell
    Dim strFile As String
    Dim xFname
    Dim xRow As Long
    Dim newRow As Long
    Dim rNew As Range
    Dim fileNameInZip, fd
    Dim oFolder As Variant
    Dim i As Integer

    i = 1
    xRow = 0
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Select the zip to get file names from"
    fd.Filters.Clear
    fd.Filters.Add "Zip Files", "*.zip"
    fd.FilterIndex = 1
    If fd.Show = -1 Then
        strFile = fd.SelectedItems(1)
        Set oFolder = oApp.Namespace(strFile).Items '< Set!
        Range("A" & i).Value = strFile
        i = i + 1
        For Each fileNameInZip In oFolder
            Range("A" & i).Value = fileNameInZip
            i = i + 1
        Next
        Set oApp = Nothing
    End If

End Sub

Upvotes: 3

Related Questions