Retrieve textfile properties of file in zipfolder

Dato

New Member
Joined
Feb 23, 2020
Messages
1
Office Version
2013
Platform
Windows
Dear all,

I wrote a VBA macro (partly mine and partyl snippets from the Internet) that lists all files that are contained in all zip files the macro can find in the starting path:

VBA Code:
folder_path = "C:\Users\[MYNAME]\Desktop\prod".
This works. It runs through every Folder down to zipArchive which contains multiple zip files with multiple files (no further Folders) within those zip file. I started to let the macro write down some Information about those files such as Name, size,...
NOW the reason I am doing this is that I am interested in the creation date/last modification date of the files within each zip file. So I used

VBA Code:
Range("B" & i).Value = fileNameInZip.DateCreated
which returns an error saying that the object does not support the method or property…
Thinks like Name and size work somehow

Can someone help out on this?

VBA Code:
Private Sub Get_Information3()   
 folder_path = "C:\Users\[MYNMAME]\Desktop\prod"   
 Dim sh As Worksheet   
 Set sh = ThisWorkbook.Sheets("Tabelle1")  
  Dim oFSO As Object    Dim oFolder As Object 'prod 
   Dim oSubFolderSupplier As Object 'Supplier    Dim oSubFolderCountry As Object  
  Dim oSubFolderDatabase As Object   
 Dim oSubFolderZipArchive As Object    
Dim oFileinZipArchive As Object   
 Dim last_row As Integer   
 Dim oApp As Object    

Set oFSO = CreateObject("Scripting.FileSystemObject")  
Set oFolder = oFSO.GetFolder(folder_path)   
 Set oApp = CreateObject("Shell.Application")  
 
Dim i, r As Integer    Dim fileNameInZip As Variant   
Dim Fname As Variant    Dim sh1 As Worksheet    

Set sh = ThisWorkbook.Sheets("Tabelle1")    

Dim Stringtest As Integer    
Dim test1 As String   

 i = 2    
For Each oSubFolderSupplier In oFolder.SubFolders        
    For Each oSubFolderCountry In oSubFolderSupplier.SubFolders
            For Each oSubFolderDatabase In oSubFolderCountry.SubFolders 
               For Each oSubFolderZipArchive In oSubFolderDatabase.SubFolders
                    For Each oFileinZipArchive In oSubFolderZipArchive.Files
                         Fname = Dir(oFileinZipArchive)
                        Stringtest = InStrRev(oFileinZipArchive, "\")
                        test1 = Left(oFileinZipArchive, Stringtest)
                        If Right(Fname, 4) = ".zip" Then
                              For Each fileNameInZip In oApp.Namespace(test1 & Fname).Items
                             Range("A" & i).Value = fileNameInZip.Name
                             Range("B" & i).Value = fileNameInZip.DateCreated
                            i = i + 1
                              Next
                         End If
                    Next 
               Next
            Next 
       Next
    Next 
End Sub
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Dan_W

Board Regular
Joined
Jul 11, 2018
Messages
225
Office Version
365
Platform
Windows
Does it absolutely have to be the Creation Date? I checked, and the ModifyDate property is accessible. I suppose it depends on what you need the date for - most of my work uses the Modification Date more than any other.

If it does need to be the dateCreated property, then - it's a bit of a hack - copy the individual file out of the ZIP file, and then check the dateCreated property with FileSystemObject.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,101
Messages
5,509,244
Members
408,718
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top