Using a wildcard asterisk to get a file and unzip it

svradjae

New Member
Joined
Apr 25, 2018
Messages
2
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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.

Rich (BB code):
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

New Member
Joined
Apr 25, 2018
Messages
2
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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...

Code:
Private Sub Workbook_Open()
    Module1.UnZip
End Sub
 
Last edited:

Forum statistics

Threads
1,186,179
Messages
5,956,400
Members
438,249
Latest member
georgebasalic3

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
Top