CODEWITHSUNDEEP
vbaexcel
Robin Trietsch
Robin Trietsch

Reputation: 1842

VBA use filter to exclude item in string array

First of all, excuse me if the code below does not contain an array but a list. I am new to VBA so I don't know all the basics yet. The code below retrieves an array (or a list) of all Excel files in a certain folder. The code is used to retrieve data from each file and is pasted in a master file.
Okay, here is my MWE:

    Sub ReadDataFromAllWorkbooksInFolder()
    Dim FolderName As String, wbName As String, wbCount As Integer
    FolderName = "C:\Users\Robin\Desktop\Test"
        wbCount = 0
        wbName = Dir(FolderName & "\" & "*.xlsm")
        While wbName <> ""
            wbCount = wbCount + 1
            ReDim Preserve wbList(1 To wbCount)
            wbList(wbCount) = wbName
            wbName = Dir
        Wend
        If wbCount = 0 Then Exit Sub
    End Sub

Now here is the problem: The array contains a template file, and the name of the file contains the word TEMPLATE. How do I filter out (or exclude) the file which file name contains the string "TEMPLATE"?

Any help would be much appreciated! Thanks in advance!

Upvotes: 2

Views: 1695

Answers (3)

Pallav Raj
Pallav Raj

Reputation: 1744

Dim fNameList As Variant
Dim storFName As String
Dim i As Integer
Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbName As String, wbCount As Integer
FolderName = "C:\Users\Robin\Desktop\Test"
    wbCount = 0
    wbName = Dir(FolderName & "\" & "*.xlsm")
    While wbName <> ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        fNameList = Split(wbName, ".")
        For i = 0 To UBound(fNameList) '<- Here i is being use
            If (UCase(InStr(fNameList(i), "TEMPLATE"))) Then
               UCase(storFName) = fNameList (i)
            End If
        Next i '<- Here i is being increase
        If storFName <> "TEMPLATE" Then
            wbList(wbCount) = wbName
            wbName = Dir
        End If
    Wend
    If wbCount = 0 Then Exit Sub
End Sub

Will it work for you?

Upvotes: 2

Olle Sj&#246;gren
Olle Sj&#246;gren

Reputation: 5385

The variable wbName contains the filenames from the Dir command. Check to see if the string contains what you want to exlude.

Extend the line

While wbName <> ""

into

While wbName <> "" And InStr(1, UCase(wbName), "TEMPLATE") = 0

to exlude any filenames that contain TEMPLATE, regardless of case.

EDIT

The logic in my suggestion above was wrong, it stopped when it encountered the first filename containing "TEMPLATE". Try the following instead:

    While wbName <> ""
        '***** Only add to wbCount if wbName does not contain TEMPLATE
        If InStr(1, UCase(wbName), "TEMPLATE") = 0 Then
            wbCount = wbCount + 1
            ReDim Preserve wbList(1 To wbCount)
            wbList(wbCount) = wbName
        End If
        wbName = Dir
    Wend

EDIT 2

Some general tips on finding errors:

  • Always use Option Explicit at the top of your modules. It will force you to declare your variables, minimizing the risk of typos etc introducing bugs.
  • Step through your code using F8 to identify errors and checking vaules of variables and functions
  • If possible, remove code to simplify it, only keeping the logic like While loops and If statements to make sure the basics work. Then add the details back.

Finally, the key to my solution is finding the files that contain "TEMPLATE" in the name. The statement InStr(1, UCase(wbName), "TEMPLATE") = 0 will be False for all files containing the string "TEMPLATE" anywhere, regardless of case, and True otherwise. (The Ucase part is what makes the statement case insensitive.)

Try to add it to your logic.

Upvotes: 2

Apropos
Apropos

Reputation: 866

Sub test()
    Dim x() As String, i As Integer
    x = ReadDataFromAllWorkbooksInFolder("C:\Users\Robin\Desktop\Test")
    For i = 0 To UBound(x)
        Debug.Print x(i)
    Next i
End Sub

Private Function ReadDataFromAllWorkbooksInFolder(FolderName As String) As Variant
    Dim wbName As String
    Dim wbCount As Integer
    Dim wbList() As String
    wbCount = 0
    ReDim wbList(0)
    wbName = Dir(FolderName & "\*.xlsm")
    While wbName <> vbNullString
        If Not (UCase(wbName) Like "*TEMPLATE*") Then
            ReDim Preserve wbList(wbCount)
            wbList(wbCount) = wbName
            wbCount = wbCount + 1
        End If
        wbName = Dir
    Wend
    ReadDataFromAllWorkbooksInFolder = wbList
End Function

Once you get comfortable with VBA you'll probably switch to Scripting.FileSystemObject and Scripting.Dictionary...

Upvotes: 0

Related Questions