Good Morning/Afternoon/Evening/Midnight SO,
This has become an increasingly frustrating issue - 48 hours in, I've looked at every article I could find. I'll first describe the problem and it's goal, and then what I've done thus far.
Objective
There is a drive with terabytes of archived (zipped) folders. Within each of these zipped folders is a folder hierarchy that may be one level deep, may be several layers (of sub-directories) deep. In each of these zipped archives lies a single file, call it "diamond.xls". Unfortunately, each archive may put it in a different sub-directory, so we'll have to crawl through every sub-directory looking for "diamond.xls". The challenge has been writing a VBA script that can explore a zipped folder, search through any/all sub-directories, find "diamond.xls", and if found, open and copy the data within to a new sheet.
Work Thus Far
There are many articles written about extracting a single file out of a zipped folder. However, I haven't seen one that is able to crawl through sub-directories in the zipped drive. One SO contributor stated that zipped drives are flat, however when I go through the .items() of a zipped drive, it seems to only return the first level, leading me to believe this may be incorrect. Here is my current code:
Public Function goThroughZip(ByRef strZipFilename, ByVal strDstDir, ByRef strFilename)
'Script based on http://stackoverflow.com/questions/19716587/how-to-open-a-file-from-an-archive-in-vba-without-unzipping-the-archive
Dim intOptions, objShell, objSourceItems, objTarget
' Create the required Shell objects
Set objShell = CreateObject("Shell.Application")
' Create a reference to the files and folders in the ZIP file
On Error Resume Next
Set objSourceItems = objShell.Namespace(strZipFilename).Items()
'Browse through Items
Dim objSourcCol As Collection
Dim i As Variant
Dim tempItem As Variant
For i = 0 To objSourceItems.Count - 1
tempItem = objSourceItems.Item(i).GetFolder.Items '<-- more on this below
Debug.Print (tempItem)
Next i
' Create a reference to the target folder
'Set objTarget = objShell.Namespace(strDstDir)
'intOptions = glngcCopyHereDisplayProgressBox
'Debug.Print ("objTarget " & objTarget)
' UnZIP the files
'objTarget.CopyHere objSource, intOptions
' Release the objects
Set objSource = Nothing
Set objTarget = Nothing
Set objShell = Nothing
goThroughZip = 1
End Function
This code is far from complete. I've been trying to understand how to play with the .nameSpace method and the folder items methods.
Set objSourceItems = objShell.Namespace(strZipFilename).Items()
returns an array of Folder2 type items, the first level in the archived folder (hooray!)
So now according to MS Dev Docs, I should be able to use the method .item(i as variant) to retrieve a folderItem object (and access to the folder).
Dim i as Variant
Dim tempItem as Variant
for i = 0 To objSourceItems.Count - 1
tempItem = objSourceItems.Item(i)
Debug.Print(TypeName(tempItem))
Next i
Returns a bunch of strings... Not FolderITem objects.. Hmm, well, let's pretend it is a folderitem and see what happens:
Dim i as Variant
Dim tempItem as Variant
for i = 0 To objSourceItems.Count - 1
tempItem = objSourceItems.Item(i).GetFolder().Items()
Debug.Print(TypeName(tempItem))
Next i
Now we are returning FolderItemVerbs.
Let's try modifying the code inside the loop to:
tempItem = objSourceItems.Item(i).Path()
Debug.Print (tempItem)
Returns a bunch of pathnames that look good, i.e. "C;\archive.zip\folder1"
What if we take that pathname and feed that into another NameSpace:
For i = 0 To objSourceItems.Count - 1
tempPath = objSourceItems.Item(i).Path()
tempSourceItems = objShell.Namespace(tempPath).Items()
Debug.Print (TypeName(tempSourceItems))
Next i
Again, FolderItemVerbs.. Not A folderItem. Let's make sure that these are indeed folders:
For i = 0 To objSourceItems.Count - 1
tempItem = objSourceItems.Item(i).isFolder()
Debug.Print (tempItem & " : " & objSourceItems.Item(i))
Next i
True - they are folders.
What if we just simply:
For i = 0 To objSourceItems.Count - 1
tempItem = objSourceItems.Item(i)
Debug.Print (tempItem.items())
Next i
Nothing.. returns nothing at all. I know that there are sub-folders and files in those folders. What's going on?
How do I access these sub-folders and find the diamond.xls in each archive?
Thank you all so much for your time and consideration. My manager has been demanding a solution soon - you've helped me a great deal!
With gratitude,
Zac
*EDIT: Strange observance:
For i = 0 To objSourceItems.Count - 1
tempItem = objSourceItems.Item(i).Size
Debug.Print (tempItem) '<-- Returns Longs that show size
Next i
For i = 0 To objSourceItems.Count - 1
tempItem = objSourceItems.Item(i)
Debug.Print (tempItem.size) '<-- Returns nothing
Next i
I think I'm missing some fundamental aspects of VBA...
