Extract folders/files from dir using 7-zip

[Samadhan Gaikwad]

I have code which extracts files to hard coded path: H:\Study\NCERT_Books\tesst1
1) I want to unzip all files (listed in "A" column) and dump files creating folders from list in column "B".
or
2) vba code to loop through all unzip files from each folder and extract files in the same respective folder and delete zip files later on.

 

[YasserKhalil]

Have a look at this link
http://www.vbaexpress.com/forum/showthread.php?58898-How-to-unzip-with-VBA

 

[Samadhan Gaikwad]

@YasserKhalil, Thank you!

Sub CReateBatfile()

    Dim ColumnNum: ColumnNum = 4  ' Column K
    Dim RowNum: RowNum = 1          ' Row to start on
    Dim objFSO, objFile
    Sheets("SHeet1").Range("E2").Value = SaveLoc
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("C:\Users\" & Environ("UserName") & "\Desktop\newcurl.bat")    'Output Path

    Dim OutputString: OutputString = ""

    Do
        OutputString = OutputString & Replace(Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine
        RowNum = RowNum + 1
    Loop Until IsEmpty(Cells(RowNum, ColumnNum))

    objFile.Write (OutputString)

    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

SET PATH=%PATH%;C:\Users\hp\Documents\test
for /R "C:\Users\hp\Documents\test" %%I in ("*.zip") do ("C:\Program Files\7-Zip\7z.exe" x -y -o"%%~dpI" "%%~fI")
del /s "C:\Users\hp\Documents\test\*.zip"

I've got above 2 code snippet 1) which creates bat file 2) extracts files and also delete *.zip files later on.
Now I want, 1) to make zip files dir. variable (i.e. to take its value from the cell) in 2nd code.
2) later on add/print this code into bat file.
3) run the bat file.

 

[Samadhan Gaikwad]

I tried below code but it's not working. Please can tell me what is wrong?

Sub UnZipFiles()
         
    Dim File        As Object
    Dim Files      As Object
    Dim MainFldr    As Object
    Dim MainPath    As Variant
    Dim oShell      As Object
    Dim ZipFile    As Variant
    Dim ZipFldr    As Object
   
    'MainPath = Sheets("Sheet2").Range("A6").Value
    MainPath = "C:\Users\hp\Documents\test\"
    Set oShell = CreateObject("Shell.Application")
    Set MainFldr = oShell.Namespace(MainPath)
    Set Files = MainFldr.items
    Files.Filter 32, "*.zip"
   
    For Each File In Files
        Set ZipFldr = oShell.Namespace(File)
        For Each ZipFile In ZipFldr.items
            MainFldr.CopyHere ZipFile.Path
        Next ZipFile
    Next File
   
End Sub

 

[Samadhan Gaikwad]

Here is the code that creates bat. file taking dir. path from cell, run it to unzip 3 types of zip files and then delete then zip files.

Sub Unzip_files()
' print values in I coulmn
Sheets("Download_PRdata1").Range("I1").Value = "SET PATH=%PATH%;C:\Users\hp\Documents\test"
Sheets("Download_PRdata1").Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "for /R ""C:\Users\hp\Documents\test"" %%I in (""*.zip"") do (""C:\Program Files\7-Zip\7z.exe"" X -y -o""%%~dpI"" ""%%~fI"")"
Sheets("Download_PRdata1").Range("I3").Select
    ActiveCell.FormulaR1C1 = _
        "for /R ""C:\Users\hp\Documents\test"" %%I in (""*.7z"") do (""C:\Program Files\7-Zip\7z.exe"" X -y -o""%%~dpI"" ""%%~fI"")"
Sheets("Download_PRdata1").Range("I4").Select
    ActiveCell.FormulaR1C1 = _
        "for /R ""C:\Users\hp\Documents\test"" %%I in (""*.7z_encrpt"") do (""C:\Program Files\7-Zip\7z.exe"" X -y -o""%%~dpI"" ""%%~fI"")"
Sheets("Download_PRdata1").Range("I5").Select
    ActiveCell.FormulaR1C1 = _
        "del /s ""C:\Users\hp\Documents\test\*.zip"""
Sheets("Download_PRdata1").Range("I6").Select
    ActiveCell.FormulaR1C1 = _
        "del /s ""C:\Users\hp\Documents\test\*.7z"""
Sheets("Download_PRdata1").Range("I7").Select
    ActiveCell.FormulaR1C1 = _
        "del /s ""C:\Users\hp\Documents\test\*.7z_encrpt"""
               
        ' replace path with actual downloaded folder
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "SET PATH=%PATH%;C:\Users\hp\Documents\test"
    Range("E3").Select
    Selection.Copy
    Columns("I:I").Select
    Selection.Replace What:="C:\Users\hp\Documents\test", Replacement:= _
        "C:\Users\a3rgcw\Downloads", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("E3").Select
    Application.CutCopyMode = False
   
    'Print values from I coulmn in bat. file
    ' source: https://superuser.com/questions/1045707/create-bat-file-with-excel-data-with-vba
    Dim ColumnNum: ColumnNum = 9  ' Column I
    Dim RowNum: RowNum = 1          ' Row to start on
    Dim objFSO, objFile

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("C:\Users\" & Environ("UserName") & "\Desktop\newcurl.bat")    'Output Path

    Dim OutputString: OutputString = ""

    Do
        OutputString = OutputString & Replace(Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine
        RowNum = RowNum + 1
    Loop Until IsEmpty(Cells(RowNum, ColumnNum))

    objFile.Write (OutputString)

    Set objFile = Nothing
    Set objFSO = Nothing
    ' run bat file
    ' source: http://stackoverflow.com/questions/37919707/run-bat-file-from-excel-using-vba
    Shell "C:\Users\" & Environ("UserName") & "\Desktop\newcurl.bat", vbNormalFocus
    DoEvents
End Sub