Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I just wrote my first VBA script for Excel because I have to write many "*.txt" files from a folder into an excel spreadsheet. But when I run this script, I get the error '5018'. It is invoked by the line

If reg.Test(file.Name) Then

Any idea what I am doing wrong? Here is the complete script:

Sub get_filenames()
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Files = fso.GetFolder("C:\myfolder").Files
    Set reg = CreateObject("vbscript.regexp")

    reg.IgnoreCase = True
    reg.MultiLine = False
    reg.Pattern = "*.txt"

    For Each file In Files
        If reg.Test(file.Name) Then
            i = i + 1
            Cells(i, 1) = file.Name
        End If        
End Sub
share|improve this question
up vote 2 down vote accepted

Just fixed it. Apparently my regular expression is wrong. 5018 stands for "Unexpected quantifier in regular expression". So I changed it to

reg.Pattern = "^.+\.txt$"
share|improve this answer
Indeed, * as a wildcard is not valid regex, it means "match the previous character zero or more times" – Phoshi Oct 29 '09 at 10:57
consider "^.+\.txt$" to match any text file, assuming VBA uses the same syntax as I'm used to. – Phoshi Oct 29 '09 at 11:00
@Phoshi: Yes, I think your solution is superior. I am going to edit my post. – Lucas Oct 29 '09 at 11:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.