Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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        
    Next
End Sub
share|improve this question
add comment

1 Answer

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
1  
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
1  
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
add comment

Your Answer

 
discard

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.