excel - Read .txt from .zip files

Question

I need to open a few .zip files, view a specific .txt and write what's inside of this .txt file to an Excel workbook, and the name of the .zip will be in the same row in Excel.

Example:

The first row is the name of the .zip file and in the first row and second column will be the content of the .txt file.

I have part of the code. It says code error 91.

Sub Text()
    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String
    Dim I As Long
    Dim num As Long

    Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                        MultiSelect:=True)
    If IsArray(Fname) = False Then
        'Do nothing
    Else
        'Root folder for the new folder.
        'You can also use DefPath = "C:UsersRonest"
        DefPath = Application.DefaultFilePath

        If Right(DefPath, 1) <> "" Then
            DefPath = DefPath & ""
        End If

        For Each fileNameInZip In oApp.Namespace(Fname).Items
            If LCase(fileNameInZip) Like LCase("md5.txt") Then

                'Open "md5.txt" For Input As #1
                'Do Until EOF(1)
                'Line Input #1, textline
                 '   text = text & textline
               ' Loop
               ' Close #1

               ' Range("B1").Value = Mid(text, 1, 32)
               ' Range("A1").Value = Dir(Fname)
            End If
        Next
    End If
End Sub

I tried to make a loop to open every file md5.txt in every zip that I have to open and take what's inside of the md5.txt

Answer 1

Here is an example of looping through your cells and getting the zip file, extracting the contents, and reading the file. You may need to adjust the path to the zip file or it will default to what ever file the excel document is started in. If you put the whole path to the zip in column A then you would not need to make an adjustment.

Edit was made to reflect the name of the file md5.txt and place contents in second column.

Sub GetData()
Dim iRow As Integer 'row counter
Dim iCol As Integer 'column counter
Dim savePath As String 'place to save the extracted files
Dim fileContents As String 'contents of the file
Dim fso As FileSystemObject 'FileSystemObject to work with files
iRow = 1 'start at first row
iCol = 1 'start at frist column
'set the save path to the temp folder
savePath = Environ("TEMP")
'create the filesystem object
Set fso = New FileSystemObject

Do While ActiveSheet.Cells(iRow, iCol).Value <> ""
    fileContents = fso.OpenTextFile(UnzipFile(savePath, ActiveSheet.Cells(iRow, iCol).Value, "md5.txt"), ForReading).ReadAll
    ActiveSheet.Cells(iRow, iCol + 1).Value = fileContents
    iRow = iRow + 1
Loop


'free the memory
Set fso = Nothing
End Sub



Function UnzipFile(savePath As String, zipName As String, fileName As String) As String
Dim oApp As Shell
Dim strFile As String
'get a shell object
Set oApp = CreateObject("Shell.Application")
    'check to see if the zip contains items
    If oApp.Namespace(zipName).Items.Count > 0 Then
        Dim i As Integer
        'loop through all the items in the zip file
        For i = 0 To oApp.Namespace(zipName).Items.Count - 1
            'check to see if it is the txt file
            If UCase(oApp.Namespace(zipName).Items.Item(i)) = UCase(filename) Then
                'save the files to the new location
                oApp.Namespace(savePath).CopyHere oApp.Namespace(zipName).Items.Item(i)
                'set the location of the file
                UnzipFile = savePath & "" & fileName
                'exit the function
                Exit Function
            End If
        Next i
    End If
'free memory
Set oApp = Nothing

End Function