open excel files with specific name in every sub folder

[bigme]

hi everybody,
i need help and let me explain the situation :
1. i have a folder ABC (example)
2. in this folder ABC contain sub folders (Folder A, Folder B, Folder C)
3. in each sub folder contain more sub folder (Folder A contain folder A1, A2 etc)
4. in each sub sub folder contain files.
what i need is how to open files in every sub folder with filename that contain specific word (example the word "Sales") so every files in the sub sub folder that contain Sales will be open. thank you very much for the help.

regards,
bigMe

 

[Seemanto Saha]

Hello bigme,

Thanks for sharing your problem with us. I understand that you want to open files with a target string from the sub-subfolders of a given folder. I have prepared a folder on my computer according to your specifications. The folder consists of subfolders and sub-subfolders. The following video will provide you with an overview of the folder.​

Folder Overview​

Now, to open all files with a target string, I opened a workbook (from any location) >> went to the Developer tab >> clicked the dropdown icon of Insert option >> selected the Command Button option from ActiveX Controls.

​

​

I dragged my mouse pointer over the worksheet to draw the command button. Then I right-clicked over it and selected the Property option.​

I changed the Caption to “Click here to open files from sub-folder” and Changed the Font style.

​

Afterward, right-click over the command button again and select the View Code option.

​

Then, I inserted the following Code and Saved the Workbook.

Private Sub CommandButton1_Click()
 
    Dim folder_path As String
    Dim file_name As String
    Dim file_extension As String
    Dim target_string As String
    Dim fso As Object
    Dim source_folder As Object
    Dim sub_folder As Object
    Dim subsub_folder As Object
    Dim file As Object
 
    folder_path = "C:\Users\Seemanto\Desktop\FQ 212\Folder ABC"
    target_string = InputBox("Enter a string for opening files:")
 
    If target_string = "" Then
        MsgBox "You did not enter anything"
        Exit Sub
    End If
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set source_folder = fso.GetFolder(folder_path)
 
    For Each sub_folder In source_folder.Subfolders
        For Each sub_SubFolder In sub_folder.Subfolders
            For Each file In sub_SubFolder.Files
                If InStr(1, file.Name, target_string, vbTextCompare) > 0 Then
                    If InStr(1, file.Name, "xlsx", vbtexcompare) > 0 Or InStr(1, file.Name, "xlsm", vbTextCompare) > 0 Then
                        Workbooks.Open sub_SubFolder & "\" & file.Name
                    End If
                End If
            Next file
        Next sub_SubFolder
    Next sub_folder
 
    Set fso = Nothing
    Set source_folder = Nothing
    Set sub_folder = Nothing
    Set sub_SubFolder = Nothing
    Set file = Nothing
End Sub

Now, close the workbook and re-open the workbook. Click on the Command Button. An input box like the following will appear.
Input the target string and click the OK button. All files with the target string from the sub-folders will open automatically.

​

The following video shows an overview of the process.​

Overview of Opening Files​

I hope this resolves your problem. Don’t forget to change the folder path of your required file. The workbook used for this reply is attached below.

Let us know your feedback.

Regards,
Seemanto Saha

 

[bigme]

dear Seemanto,
huge thank you for youe help, i just need to confirm about this...., in the declaration it said "Dim subsub_folder As Object" but the syntax said
"For Each sub_SubFolder In sub_folder.Subfolders".
is it just a typo or not, thank you.

best regards,
bigMe

 

[Seemanto Saha]

Dear bigme,
Thanks for your feedback. Yes, you are right. the Dim subsub_folder as Object is a typing mistake. However, the above code would still work as expected. Although the variable sub_SubFolder is not declared as an Object, Excel VBA will take it as an Object based on the properties used for this variable. And, as the subsub_folder is not used in any operation, it will just keep space in the computer memory.

Being said that it is bad practice to declare unused variables. Hence, the modified code is given below:​

Private Sub CommandButton1_Click()
    
    Dim folder_path As String
    Dim file_name As String
    Dim file_extension As String
    Dim target_string As String
    Dim fso As Object
    Dim source_folder As Object
    Dim sub_folder As Object
    Dim sub_SubFolder As Object
    Dim file As Object
    
    folder_path = "C:\Users\Seemanto\Desktop\FQ 212\Folder ABC"
    target_string = InputBox("Enter a string for opening files:")
    
    If target_string = "" Then
        MsgBox "You did not enter anything"
        Exit Sub
    End If
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set source_folder = fso.GetFolder(folder_path)
    
    For Each sub_folder In source_folder.Subfolders
        For Each sub_SubFolder In sub_folder.Subfolders
            For Each file In sub_SubFolder.Files
                If InStr(1, file.Name, target_string, vbTextCompare) > 0 Then
                    If InStr(1, file.Name, "xlsx", vbtexcompare) > 0 Or InStr(1, file.Name, "xlsm", vbTextCompare) > 0 Then
                        Workbooks.Open sub_SubFolder & "\" & file.Name
                    End If
                End If
            Next file
        Next sub_SubFolder
    Next sub_folder
    
    Set fso = Nothing
    Set source_folder = Nothing
    Set sub_folder = Nothing
    Set sub_SubFolder = Nothing
    Set file = Nothing
End Sub

Best Regards,
Seemanto Saha
ExcelDemy

 

[bigme]

Dear Seemanto,
kindly help me with your code above, which part do i have to modified if i want to copy every sheets that contains data from every single files that already opened and joint all that sheets into 1 workbook (in this case need to add a new workbook), really appreciate your help, thank you very much.

regards,
bigMe

 

[Seemanto Saha]

Hello bigme,

Thanks for sharing your problem with us. I understand you want to copy every sheet from the workbooks opened for the given target_string and paste it into a new workbook. We have to loop through each sheet of each opened workbook and copy each sheet to the new workbook. Here is the modified code.​

Private Sub CommandButton1_Click()
   
    Dim folder_path As String
    Dim file_name As String
    Dim file_extension As String
    Dim target_string As String
    Dim fso As Object
    Dim source_folder As Object
    Dim sub_folder As Object
    Dim sub_SubFolder As Object
    Dim file As Object
   
    folder_path = "C:\Users\Seemanto\Desktop\FQ 212\Folder ABC"
    target_string = InputBox("Enter a string for opening files:")
   
    If target_string = "" Then
        MsgBox "You did not enter anything"
        Exit Sub
    End If
   
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set source_folder = fso.GetFolder(folder_path)
   
    Set target_workbook = Workbooks.Add
    target_workbook.SaveAs Filename:=target_string & " Copied Data"
    Set target_sheet = target_workbook.Sheets(1)
   
    For Each sub_folder In source_folder.Subfolders
        For Each sub_SubFolder In sub_folder.Subfolders
            For Each file In sub_SubFolder.Files
                If InStr(1, file.Name, target_string, vbTextCompare) > 0 Then
                    If InStr(1, file.Name, "xlsx", vbtexcompare) > 0 Or InStr(1, file.Name, "xlsm", vbTextCompare) > 0 Then
                        Set source_workbook = Workbooks.Open(sub_SubFolder & "\" & file.Name)
                        For Each sht In source_workbook.Sheets
                            sht.Copy After:=target_sheet
                        Next sht
                        source_workbook.Close SaveChanges:=False
                    End If
                End If
            Next file
        Next sub_SubFolder
    Next sub_folder
   
    Set fso = Nothing
    Set source_folder = Nothing
    Set sub_folder = Nothing
    Set sub_SubFolder = Nothing
    Set file = Nothing
End Sub

If I enter “Report” as target_string then all sheets from the opened workbooks will be copied to the new workbook titled “Report Copied Data”. The following video shows an overview of the process.​

Copy Sheets from Target Workbooks to New Workbook​

Note: Make sure the sheet names in all the workbooks for the target_string are unique.

Regards,
Seemanto Saha
ExcelDemy

 

[bigme]

thank you and more thank you......

regards,
bigMe