excel vba - VBA Regex Pattern: failing when underscore in my pattern -

excel vba - VBA Regex Pattern: failing when underscore in my pattern -

looking help macro loops through subfolders , brings info workbooks match filename pattern, because name changes each month.

it works seamlessly if pattern "[0-9][0-9][0-9][0-9][0-9][0-9] filename"

but fails if "[0-9][0-9][0-9][0-9]_[0-9][0-9] filename"

any ideas on how handle underscore please?

this fails "[0-9][0-9][0-9][0-9][_][0-9][0-9] filename"

thanks heaps gws

option explicit alternative base of operations 1 private const portfolio_code string = "g030" private sub extractdata() ' workbook list dim wblist collection set wblist = new collection application.displayalerts = false recursivefilesearch _ "o:\sales , marketing\monthly reports\", _ "[0-9][0-9][0-9][0-9][_][0-9][0-9] monthly report.xlsm", _ 'fails find workbooks '"[0-9][0-9][0-9][0-9][0-9][0-9] monthly report.xlsm", _ 'would work except file names contain underscores wblist dim resultoffset integer wsresult.name = result resultoffset = 1 dim wbname variant, wbopen workbook, wsfund worksheet each wbname in wblist ' loop through workbook list ' - open workbook, hidden application.screenupdating = false set wbopen = workbooks.open(filename:=wbname, readonly:=true) wbopen.windows(1).visible = false ' - worksheet fund set wsfund = wbopen.worksheets(portfolio_code) application.screenupdating = true ' - find top of info dim valuedate date valuedate = worksheetfunction.eomonth(dateserial(2000 + cint(left(wbopen.name, 2)), cint(mid(wbopen.name, 3, 2)), 1), 0) debug.print valuedate, wbopen.name thisworkbook.worksheets(portfolio_code).activate dim basedata excel.range set basedata = wsfund.range("aq:aq").find("currency") if not basedata nil ' - loop through info dim rowoffset integer rowoffset = 0 wsresult.range("a1").offset(resultoffset, 0).value = valuedate ' basedata.offset(rowoffset, 0).value wsresult.range("a1").offset(resultoffset, 1).value = basedata.offset(rowoffset, 0).value wsresult.range("a1").offset(resultoffset, 2).value = basedata.offset(rowoffset, 5).value resultoffset = resultoffset + 1 end if ' - close workbook wbopen.close savechanges:=false doevents next application.displayalerts = true end sub

recursivefilesearch

sub recursivefilesearch( _ byval targetfolder string, _ byref filepattern string, _ byref matchedfiles collection _ ) dim oregexp new vbscript_regexp_55.regexp oregexp.global = false oregexp.ignorecase = true oregexp.multiline = false oregexp.pattern = filepattern dim ofso scripting.filesystemobject set ofso = new scripting.filesystemobject 'get folder oect associated target directory dim ofolder variant set ofolder = ofso.getfolder(targetfolder) 'loop through files current folder dim ofile variant each ofile in ofolder.files if oregexp.test(ofile.name) matchedfiles.add ofile end if next 'loop through each of sub folders recursively dim osubfolders object set osubfolders = ofolder.subfolders dim osubfolder variant each osubfolder in osubfolders recursivefilesearch osubfolder, filepattern, matchedfiles next 'garbage collection set ofolder = nil set ofile = nil set osubfolders = nil set osubfolder = nil set ofso = nil set oregexp = nil end sub

perhaps:

\d{4}_\d{2}.*monthly report\.xlsm

regex excel-vba