Find certain folder but don't know the full path

[elmnas]

Hello All Excel Members,

If you see on my picture below:

I got a certain folder structure,

The thing is I got a string I alert which can be any number.

then I need to open the correct folder in explorer and unzip a file called. test.zip

Could someone help me with a code to open correct folder example folder 34118

there can be xxxxx numbers of folders

Thank you in advance

 

[BarryL]

to search through your windows explorer you must add in the correct reference in VBA.

the below should get you to the right file but as far as opening the correct file id need more info. hopeully this is a good start for you.

Sub ListFilesInFolder()
     
     
    Dim FS As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    Dim filenumber As Long
    filenumber = InputBox("whats the file number you are looking for?")
    Set FS = New Scripting.FileSystemObject
    Set SourceFolder = FS.GetFolder("U:") & filenumber 'Place in here the directory of interest
    For Each FileItem In SourceFolder.Files
       If FileItem.Type = "Microsoft Excel Worksheet" Then
       FileItem.Copy FileItem.Name, True
       Application.Workbooks.Open (FileItem.Name)
       End If
    Next FileItem
End Sub