regex - RunTime Error on VBA Macro -


i working on vba code grab text outlook email , place in excel sheet have set up. using excel 2010. email contains following information:

company: abc company
class period: 2013-10-29 through 2014-10-22

i have set loop go through email , insert company name in column a, first date (2013-10-29) in column b , other date (2014-10-22) in column c. when run code receive error states: run-time error 5: invalid procedure call or argument on below line of code: vtext2 = trim(m.submatches(2))

could please let me know doing wrong. part of code below. let me know if need provide additional information.

stext = olitem.body  set reg1 = createobject("vbscript.regexp")  = 1 3  reg1 select case case 1 .pattern(company\s[:]+\s(\w*\s*\w*\s*\w*\s*\w*\s*\w*\s*\w*\s*\w*\s*\w*\s*\w*\s*)\n)" .global = false case 2 .pattern = "(class period\s*[:]+\s*([\d-\s]*))" .global = false case 3 .pattern = "(through+\s*([\d-\s]*))" .global = false end select  end  if reg1.test(stext)  set m1 = reg1.execute(stext) each m in m1 vtext = trim(m.submatches(1)) vtext2 = trim(m.submatches(2)) vtext3 = trim(m.submatches(3)) next end if next  xlsheet.range("a" & rcount) = vtext xlsheet.range("b" & rcount) = vtext2 xlsheet.range("c" & rcount) = vtext3  xlwb.close 1 

the error saying argument gave (the index) invalid submatches collection because exceeds count of items in collection.

remember submatches index starts @ "0". i'm assuming intentionally skipped first item in favor of second through fourth items in collection.

i think expecting adding match collection each time runs through loop. that's not case. new match replacing old match ever have 1 result work @ time.

try instead:

private sub commandbutton1_click()     stext = "company: abc company" & vbnewline & "class period: 2013-10-29 through 2014-10-22" & vbnewline     set reg1 = createobject("vbscript.regexp")      vtext = "missing"     vtext2 = "missing"     vtext3 = "missing"      = 1 3          reg1             select case             case 1                 .pattern = "(company)\w*[:](.*?)\n"                 .global = false             case 2                 .pattern = "(class period\s*[:]+\s*([\d-\s]*))"                 .global = false             case 3                 .pattern = "(through+\s*([\d-\s]*))"                 .global = false             end select          end           if reg1.test(stext)             set m1 = reg1.execute(stext)             each m in m1                 if m.submatches.count > 0                     select case                     case 1                         vtext = trim(m.submatches(1))                     case 2                         vtext2 = trim(m.submatches(1))                     case 3                         vtext3 = trim(m.submatches(1))                     end select                 end if             next         end if          next          xlsheet.range("a" & rcount) = vtext         xlsheet.range("b" & rcount) = vtext2         xlsheet.range("c" & rcount) = vtext3          xlwb.close 1      end sub 

note had change company regex work too. original 1 wasn't working me.


Comments

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

jdbc - Not able to establish database connection in eclipse -

Kivy: Swiping (Carousel & ScreenManager) -