进行编辑以反映文件md5.txt的名称,并将内容放在第二栏中. Sub GetData()Dim iRow As Integer 'row counterDim iCol As Integer 'column counterDim savePath As String 'place to save the extracted filesDim fileContents As String 'contents of the fileDim fso As FileSystemObject 'FileSystemObject to work with filesiRow = 1 'start at first rowiCol = 1 'start at frist column'set the save path to the temp foldersavePath = Environ("TEMP")'create the filesystem objectSet fso = New FileSystemObjectDo 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 + 1Loop'free the memorySet fso = NothingEnd SubFunction UnzipFile(savePath As String, zipName As String, fileName As String) As StringDim oApp As ShellDim strFile As String'get a shell objectSet 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 memorySet oApp = NothingEnd FunctionI 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 IfEnd SubI 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 counterDim iCol As Integer 'column counterDim savePath As String 'place to save the extracted filesDim fileContents As String 'contents of the fileDim fso As FileSystemObject 'FileSystemObject to work with filesiRow = 1 'start at first rowiCol = 1 'start at frist column'set the save path to the temp foldersavePath = Environ("TEMP")'create the filesystem objectSet fso = New FileSystemObjectDo 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 + 1Loop'free the memorySet fso = NothingEnd SubFunction UnzipFile(savePath As String, zipName As String, fileName As String) As StringDim oApp As ShellDim strFile As String'get a shell objectSet 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 memorySet oApp = NothingEnd Function 这篇关于从.zip文件读取.txt的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
09-05 17:44
查看更多