CODEWITHSUNDEEP
excelvba
user14934649
user14934649

Reputation:

Merging files, with certain text in filename; from a folder directory into a single Excel file

I have a folder directory with several Excel files. I have written code that copies every file in that directory to a single Excel worksheet.

This folder has a few different categories of files.

I need to add a condition that states the file must contain "Marios" in the filename.

Option Explicit
    
Sub grabdata()
    Dim FSO         As Object
    Dim fsoFol      As Object
    Dim fsoFile     As Object
    Dim wb          As Workbook
    Dim wksSource   As Worksheet

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fsoFol = FSO.GetFolder("C:\Desktop" & "\")
    
    For Each fsoFile In fsoFol.Files
        If fsoFile.Type Like "Microsoft*Excel*Work*" _
          And Not fsoFile.Path = ThisWorkbook.FullName Then

            On Error GoTo 10

            Set wb = Workbooks.Open(fsoFile.Path, False, True)
            Set wksSource = Nothing

            On Error Resume Next

            Set wksSource = wb.Worksheets("Summary")
            
            If Not wksSource Is Nothing Then
                wksSource.Range("A1:i100").Copy _
                  ThisWorkbook.Worksheets("Nintendo").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End If
            
            On Error GoTo 0
            
            wb.Close False
        End If
    Next
10
End Sub

Upvotes: 1

Views: 37

Answers (1)

Timothy Alexis Vass
Timothy Alexis Vass

Reputation: 2705

Simply add the check to your If statement.

If fsoFile.Type Like "Microsoft*Excel*Work*" _
   And Not fsoFile.Path = ThisWorkbook.FullName _
   And fsoFile.Name Like "Marios*" Then

Upvotes: 1

Related Questions