VBA use filter to exclude item in string array

Question

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!

Olle Sjögren · Accepted Answer · 2014-05-12 09:33:38Z

2

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.

edited May 12, 2014 at 9:33

answered May 12, 2014 at 8:23

Olle Sjögren

5,3923 gold badges35 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

trietsch Over a year ago

Thanks for your reply. When I extend the line, the macro does not seem to do anything anymore. I checked the result of InStr(1, UCase(wbName), "TEMPLATE") = 0 with a messagebox, but it didn't show anything.

trietsch Over a year ago

Ah, it does seem to work when it is used in a messagebox. If used in the while statement, it does not work.

Pallav Raj Over a year ago

@RobinTrietsch Take the name of file in a separate variable x=split(wbName,"."). Then run the loop to UBound of "x" and check for UCase(x)= "TEMPLATE" . I think this should work.

Olle Sjögren Over a year ago

Then you will have to step through the code to see where it hangs. I have run your code, but with the lines containing referecnes to wbList commented out, since I could not get them to compile. Your code with my changes works in my environment (Excel 2010).

trietsch Over a year ago

Somehow, it didn't work the first time. Now it works! Thanks a bunch!

|

Pallav Raj · Accepted Answer · 2014-05-12 09:53:56Z

2

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?

edited May 12, 2014 at 9:53

answered May 12, 2014 at 9:06

Pallav Raj

1,7443 gold badges18 silver badges29 bronze badges

13 Comments

Olle Sjögren Over a year ago

I don't think you need to remove the file ending, since the question says to exlude files where the name contains the string "TEMPLATE". The InStr function will take care of all those files.

Pallav Raj Over a year ago

Okay, That's what I want to know, will file name may be like this e.g. (1) Template XYz.xlsx , (2) Template_Xyz.xlsx?

Olle Sjögren Over a year ago

I think so, but OP would have to say.

trietsch Over a year ago

@Brain The template file name looks like: "SOMETEXT V2_1 TEMPLATE.xlsm"

trietsch Over a year ago

@Brain Unfortunately, the macro still uses the TEMPLATE file with your solution.

|

Apropos · Accepted Answer · 2014-05-12 09:20:51Z

0

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...

answered May 12, 2014 at 9:20

Apropos

8655 silver badges12 bronze badges

2 Comments

trietsch Over a year ago

Thanks Apropos. I implemented the code, but like with the solution from Olle Sjögren, the macro does not do anything anymore.

trietsch Over a year ago

Thanks for your effort Apropos. Eventually, Olle's solution worked.

Collectives™ on Stack Overflow

VBA use filter to exclude item in string array

3 Answers 3

11 Comments

13 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

13 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related