Unzip Multiple Files in Directory

macomac

New Member
Joined
Feb 9, 2010
Messages
21
In Access, am needing to unzip all .zip files located in a particular folder (30+ files), without having winzip installed. After unzipping them, will need to delete some of the files that will contain a specific word in their file names, but that is round 2. First, need to unzip them. Been trying to modify code found here, http://www.rondebruin.nl/windowsxpzip.htm, but no luck yet from within Access, getting compile error on GetOpenFielname, and not sure if I'm missing a library reference or if Access doesn't use that. Any tips would be appreciated, thank you.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Note: The sample code you gave is for Excel VBA not Access VBA. You will might have to modify it some.

What version of Access are you suing?

If you are wanting to process all the file in a folder, then you do not even need the Application.GetOpenFilename ...

use the Dir() command to get the all the file names to process.
 
Upvote 0
Thank you, am using Access 2007. I don't know what I'm doing much as to how to modify for Access so I tried it in Excel and it works nice except I don't want a dialog box to open, it will always be the same folder name, it'll just have new zips in it from time to time, and I'm trying to modify it to just open that folder instead of open dialog box, needing no human intervention. If I create a template in Excel with the code on the file open event, and call that workbook from Access, it should accomplish the same thing unless it's fairly understandable for a newbie at this to convert the Excel code to Access. Otherwise, it does seem to work very nicely from Excel and I'd be happy to use the Excel template to do to the job. Do you see any pitfalls with that method, or can you provide any pointers on converting the Excel code to Access? Thank you again, just moving it to Excel and seeing it in action and successful is a huge relief factor, wouldn't have thought of that if you didn't mention the Excel angle.
 
Upvote 0
I am using version 4 from the code found at the above link, it is pasted below. I have moved it to Excel and will call it from the workbook open event, opening the template from Access. Am having trouble getting rid of the dialog box and simply running the code on a specific directory. Tried inserting my folder/path instead of the red below, but no luck. What am I doing wrong? Thank you.

Code:
Sub Unzip4()
    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String
    Dim I As Long
    Dim num As Long

    [COLOR=Red]Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                        MultiSelect:=True)[/COLOR]
    If IsArray(Fname) = False Then
        'Do nothing
    Else
        'Root folder for the new folder.
        'You can also use DefPath = "C:\Users\Ron\test\"
        DefPath = Application.DefaultFilePath
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If

        'Create the folder name
        strDate = Format(Now, " dd-mm-yy h-mm-ss")
        FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"

        'Make the normal folder in DefPath
        MkDir FileNameFolder

        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.Application")

        For I = LBound(Fname) To UBound(Fname)
            num = oApp.Namespace(FileNameFolder).items.Count

            oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname(I)).items

        Next I

        MsgBox "You find the files here: " & FileNameFolder

        On Error Resume Next
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
    End If
End Sub
 
Upvote 0
[quolte]
... I am using version 4 from the code found at the above link ...
[/quote]
The link you proved does not have a version 4. It does show how to create a ZIP, noit unzip.


To do what you want you need to use some method to get the name of each ZIP file in the target fiolder.

I woud use the built in VBA Dir() function. Use the DIr() to get the fiel names unsing a Loop to process each file one at a time.

Have you cehcked the help file for Dir() function?
 
Upvote 0
Sorry, the link had a link to the unzip method - http://www.rondebruin.nl/windowsxpunzip.htm, I should have posted that link instead, since it is where I found the version #4.

Modified the red below to this, after studying the Dir function, but am not understanding what I'm doing, as nothing happens.

Fname = Dir("d:\My Docs\")

How would I incorporate to replace the GetOpenFilename part of the code? Thank you again.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top