How to open a file from an archive in vba without unzipping the archive

Question

I have a series of archives: C: /archive1.zip, C: /archive2.zip, etc.

I want to extract only one file from each archive. Each archive has the same structure and file, which can be found at:

C: /archive1.zip/folderlevel1/folderlevel2/folderlevel3/Myfile.csv C: /archive2.zip/folderlevel1/folderlevel2/folderlevel3/Myfile.csv

and etc.

How can I read the entire Myfile.csv file in vba?

Thanks!

Answer 1

You can do it as follows:

' ' UnZip 1 file from a zip file: ' Function entUnZip1File(ByVal strZipFilename, ByVal strDstDir, _ ByVal strFilename) ' Const glngcCopyHereDisplayProgressBox = 256 ' Dim intOptions, objShell, objSource, objTarget ' ' Create the required Shell objects Set objShell = CreateObject("Shell.Application") ' ' Create a reference to the files and folders in the ZIP file Set objSource = _ objShell.NameSpace(strZipFilename).Items.item(CStr(strFilename)) ' ' Create a reference to the target folder Set objTarget = objShell.NameSpace(strDstDir) ' intOptions = glngcCopyHereDisplayProgressBox ' ' UnZIP the files objTarget.CopyHere objSource, intOptions ' ' Release the objects Set objSource = Nothing Set objTarget = Nothing Set objShell = Nothing ' entUnZip1File = 1 ' End Function 

And anywhere in the macro, call the function to extract the file to the C: \ temp directory or to any destination folder instead of C: \ temp:

 entUnZip1File "C:\archive1.zip", "C:\temp", "folderlevel1/folderlevel2/folderlevel3/Myfile.csv"