Using a wildcard asterisk to get a file and unzip it

[svradjae]

Hello guys,

I'm completely new to VBA. My task is I have a certain zip file on my Downloads, I should unzip the file.
With file name known, I'm able to unzip it but the problem is : I don't know the entire file name ("xxx-065-xxx"). I've tried the code below. It indeed shows me that the file exists but doesn't show me the correct full path ("D:\Users\~~~\Downloads\test\*.zip") and hence don't unzip it (Fname is undefined). Could you please help me?

Sub Unzip()
Dim FSO As Object
Dim oApp As Object
Dim Fname As Variant
Dim FileNameFolder As Variant
Dim DefPath As Variant
Dim strDate As String
Dim myValue As Variant


Fname = "D:\Users\~~~\Downloads\test\*.zip"

If Len(Dir(Fname, vbDirectory)) > 0 Then
Range("A5") = "Exists!"
Else
Range("A5") = "Doesn't exist!"
End If
Range("A6") = Fname

If Fname = False Then
'Do nothing
Else
DefPath = "D:\Users\~~~\Downloads"
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
Range("A1") = Fname
Range("A2") = DefPath
Set oApp = CreateObject("Shell.Application")

oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items
On Error Resume Next

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

 

[Leith Ross]

Hello svradjae,

This macro will try to match the file name using wildcards characters * and ? in the filename. You don't need to add the ".zip" extension to the file name. It is assumed the only file type is a zip file.

In your file name you use an "x". I take this to mean the character is unknown and the "-065-" is the only known part. The macro uses the pattern "*???-065-???*.zip" to try an match the file. If no file match or more than 1 match is found then an error is displayed and the macro exits.

Sub UnZipFile()


    Dim n       As Long
    Dim oFiles  As Object
    Dim oFolder As Object
    Dim oShell  As Object
    Dim Path    As Variant
    Dim ZipFile As Variant
    Dim ZipDir  As Variant
    
        ' // Zip file name either complete or with wildcards.
        ZipFile = "*???-065-???*"
        
        ' // Check if file name includes the zip extension
        If LCase(Right(ZipFile, 4)) <> ".zip" Then ZipFile = ZipFile & ".zip"
        
        Set oShell = CreateObject("Shell.Application")
        
        ' // Path to the user's Downloads Folder.
        Path = oShell.Namespace(0).Self.Path
            n = InStrRev(Path, "\")
            Path = Mid(Path, 1, n) & "Downloads"
        
        ' // Folder to hold the unzipped files.
        ZipDir = Path & "\UnZip"
            If Dir(ZipDir, vbDirectory) = "" Then MkDir ZipDir
        
        ' // Return the folders in the Downloads folder.
        Set oFiles = oShell.Namespace(Path).Items
        
        ' // Filter the folders.
        oFiles.Filter 32, ZipFile
        
        ' // Check for errors.
        Select Case oFiles.Count
            Case 0
                MsgBox "No file matching """ & ZipFile & """ was found.", vbExclamation: Exit Sub
            Case Is > 1
                MsgBox "Multiple files matching """ & ZipFile & """ were found.", vbExclamation: Exit Sub
        End Select
        
        ' // Found the file - Unzip it.
        Set ZipFile = oShell.Namespace(oFiles.Item(0))
        Set ZipDir = oShell.Namespace(ZipDir)
        ZipDir.CopyHere ZipFile.Items
        
        MsgBox "The files are in " & ZipDir.Self.Path
        
End Sub

 

[svradjae]

Thanks a lot! It's working!

I would like to know what do these lines do :
"Path = oShell.Namespace(0).Self.Path"
"oFiles.Filter 32, ZipFile" ?


This macro is in modules as I guess any macro should be. I'd like to execute this macro when I open the Excel file. So, I wrote the below lines but I get an error announcing that the macro is a module and I can only call a tag or a procedure. Could you please help me out?

Private Sub Workbook_Open()
Sub Unzip()
End Sub

 

[Leith Ross]

Hello svradjae,

The Shell has quite a few predefined Namespaces that can be accessed using an index number. Zero points the User's Desktop. Since these Namespaces are folders, you must use "Self.Path"to return the folder's path.

The Filter method allows you to apply one or more wildcard filters to either the files or the folders. The number 32 indicates we are filtering Folders. If you want to filter only the files then you would use 64.

When calling a macro from the the workbook Open event, you must fully qualify your reference. This means you must include the module's name. For example, let's say the macro is in Module1. Your code would then look like this...

Private Sub Workbook_Open()
    Module1.UnZip
End Sub