Scanning via Zip files
I am trying to scan through a specific drive and extract data from specific .xls files that are buried in subdirectories. The drive is over TB, and the folders don't all have the same hierarchy, so I go through them all. So far, the script works fine.
The problem is there are compressed files in the drive. At least half of the files are in zip format. How can I scan these files?
Here is a piece of my code that will scan through subdirectories. There is another "TrailingSlash" function that simply adds "\" to a string if it doesn't already have one. I comment on the author in my comments.
Public Function recursiveDir(colFiles As Collection, strFolder As String, strFileSpec As String, bIncludeSubfolders As Boolean) as Collection
'From Ammara.com/access_image_faq/recursive_folder_search.html
'Recursive function to search document tree from specific file extension
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim colFiles As New Collection
Dim counter As Integer
'Add files in strFolder matching strFileSpec to colFiles
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
On Error Resume Next
Do While strTemp <> vbNullString
colFiles.Add (strFolder & strTemp)
counter = counter + 1
Debug.Print ("files found: " & counter)
strTemp = Dir
Loop
If bIncludeSubfolders Then
'Fill colFolders with list of subdirectories of strFolder
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call recursiveDir for each subfolder in colFolders
For Each vFolderName In colFolders
Call recursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
Next vFolderName
End If
recursiveDir = colFiles
End Function
The function adds all the path lines to the "colFolders" collection, which I then use to open and retrieve data. Now I think there cannot be an easy way to return the string path of a file in a zip folder. It might need a separate function that gets called when this function encounters a zip, which in turn bypasses the mail folder and extracts the specific file to a local address (if I don't need to extract the entire folder, we should be good). In the meantime, there is no need to know about it. ”
I kind of lost what I have to do. Google around points me to using shell.Application. I don't know anything about shells, is this the path I should take?
Thanks for being great!
source to share
Try using this code to search through subfolders:
Sub SO()
Dim x, i
x = GetFiles("C:\Users\SO\Folder", "*.xls*", True) '// x becomes an array of files found
For Each i In x
Debug.Print i
Next i
End Sub
'-------------------------------------------------
Function GetFiles(StartPath As String, FileType As String, SubFolders As Boolean) As Variant
StartPath = StartPath & IIf(Right(StartPath, 1) = "\", vbNullString, "\") 'Sanity check
GetFiles = Split(Join(Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & StartPath & FileType & """ " & _
IIf(SubFolders, "/S", vbNullString) & " /B /A:-D").StdOut.ReadAll, vbCrLf), ":"), "#"), "#")
End Function
But there is really nothing native to Windows for zip files that will allow you to do it differently than the method CreateObject("Shell.Application").Namespace(zipName).Items
.
I prefer to use 7-zip, which is free, open source and has a great command line utility, which means you can access it via VBA using the method too CreateObject("WScript.Shell")
(like above)
source to share