从.zip文件读取.txt [英] Read .txt from .zip files
问题描述
我需要打开一些.zip文件,查看特定的.txt并将此.txt文件中的内容写到Excel工作簿中,而.zip
的名称将在Excel中位于同一行. /p>
示例:
第一行是.zip文件的名称,第一行和第二列是.txt文件的内容.
我有部分代码.它显示代码错误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:\Users\Ron\test\"
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
我试图制作一个循环以打开我必须打开的每个zip文件中的每个文件md5.txt,并获取md5.txt中的内容
以下是循环遍历单元格并获取zip文件,提取内容并读取文件的示例.您可能需要调整zip文件的路径,或者它将默认使用excel文档开始的文件.如果将整个路径放在zip列的A列中,则无需进行调整.>
进行编辑以反映文件md5.txt的名称,并将内容放在第二栏中.
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
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:\Users\Ron\test\"
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
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
这篇关于从.zip文件读取.txt的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!