CODEWITHSUNDEEP
excelvba
Cairl Dacar Borstnik
Cairl Dacar Borstnik

Reputation: 45

IF statement not being evaluated correctly excel 2010 vba

I'm trying to find files with specific text in the file name and run a macro on only those. Everything is fantastic but the IF statement that is supposed to pick out the specific files isn't evaluating correctly. I can see the file name stored in the variable, I've even tried converting the variable from a variant to a string - but the IF statement doesn't seem to be reading the name. It runs the macro on all files in the folder. 

Function Recurse(sPath As String) As String

    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder
    Dim myFile As Variant
    Dim file As String

    Set myFolder = FSO.GetFolder(sPath)

    For Each mySubFolder In myFolder.SubFolders
        For Each myFile In mySubFolder.Files

            file = myFile.Name

            If file <> "*CONSOLIDATED SUMMARY.xls*" Then
                Application.Workbooks.Open fileName:=myFile
                Call UnmergeAndResize
                ActiveWorkbook.Close SaveChanges:=True
            End If
        Next
        Recurse = Recurse(mySubFolder.path)
    Next

End Function

Upvotes: 1

Views: 76

Answers (1)

Hauke P.
Hauke P.

Reputation: 2823

You cannot use the * character to perform partial string matches in VBA.

Instead, you should replace the line containing the If statement with:

    If InStr(file, "CONSOLIDATED SUMMARY.xls") = 0 Then

Upvotes: 1

Related Questions