Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

unzip

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

I want to extract only one file from each archive. Each archive has same structure and file can found under :

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

etc.

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

Thanks!

like image 974
LouInNY Avatar asked Oct 31 '13 21:10

LouInNY


People also ask

How do I open an entire archive file?

Open File Explorer and find the zipped folder. To unzip the entire folder, right-click to select Extract All, and then follow the instructions. To unzip a single file or folder, double-click the zipped folder to open it. Then, drag or copy the item from the zipped folder to a new location.

Can VBA extract zip?

So combining the PS_Execute() sub from VBA – Run PowerShell Command with either of these commands and we have the ability to create zip files or extract the content of a zip file with 1 line of code.

How do I unpack a downloaded archive?

Do one of the following: To unzip a single file or folder, open the zipped folder, then drag the file or folder from the zipped folder to a new location. To unzip all the contents of the zipped folder, press and hold (or right-click) the folder, select Extract All, and then follow the instructions.


1 Answers

You can do it as this:

'
' 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 any where in your macro, call the function to extract the file into C:\temp directory or to any destination folder instead of C:\temp:

entUnZip1File "C:\archive1.zip", "C:\temp", "folderlevel1/folderlevel2/folderlevel3/Myfile.csv"
like image 135
jacouh Avatar answered Nov 08 '22 05:11

jacouh