Open\Close all Files in a Folder based on Specific text in the name like "MTD" "YTD&-CodePudding

So I am trying to create two separate loops in VBA. One to open all files in a folder based on specific text such as "MTD", "YTD","MainFile" and "DataExtract." Then separately, when I want to, close all open files with those same text strings. I currently only have a functioning loop to open all files with .xlsx in a cell value on the active sheet. I do not have a working close loop for .xlsx even. This is the code I have so far:

    Public Sub Open_Files()
Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Application.ScreenUpdating = False
directory = Range("D2").Value
fileName = Dir(directory & "*.xlsx")
    Do While fileName <> ""
    Workbooks.Open (directory & fileName)
    fileName = Dir()
    Loop
Application.ScreenUpdating = True
End Sub

    Public Sub Close_Files()
Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Application.ScreenUpdating = False
directory = Range("D2").Value
fileName = Dir(directory & "*.xlsx")
    Do While fileName <> ""
    Workbooks(directory & fileName).Close SaveChanges:=True
    fileName = Dir()
    Loop
Application.ScreenUpdating = True
End Sub

Let me know what I can do to create/fix this code! I really appreciate any feedback thank you!

CodePudding user response：

Instead of looping through the directory, it would make more sense to loop through the Application.Workbooks collection. This is a collection of all currently open workbooks. You could then compare the Workbook.Name to your name pattern, and see if it needs to be closed.

Sub Example()
    Dim WB As Workbook
    For Each WB In Application.Workbooks
        If InStr(WB.Name, "MTD") _
        Or InStr(WB.Name, "YTD") _
        Or InStr(WB.Name, "MainFile") _
        Or InStr(WB.Name, "DataExtract") _
        Then
            WB.Close SaveChanges:=True
        End If
    Next
End Sub

If the list of name patterns is going to be expanded or edited frequently, it might be easier to have it declared separately. This avoids the issue of having a huge and cumbersome If Statement in the middle of the code. Instead, we can loop though an array and compare the name against each pattern individually.

Sub Example2()
    Const SEARCH_TEXTS As String = "MTD,YTD,MainFile,DataExtract"
    
    Dim WB As Workbook
    For Each WB In Application.Workbooks
    
        Dim SearchText As Variant
        For Each SearchText In Split(SEARCH_TEXTS, ",")
            If InStr(WB.Name, CStr(SearchText)) Then WB.Close SaveChanges:=True
        Next
        
    Next
End Sub

CodePudding user response：

Sub closeWorkbooks()

    Dim wb As Workbook
    Dim directory As String: directory = Range("D2").Value
    
    For Each wb In Application.Workbooks
        If wb.Path = directory And wb.name Like "*.xlsx" Then
            wb.Close
        End If
    Next

End Sub

Good Luck