excel - Extract Portion of File Name "MM-YYYY" in VBA? -

all filenames "ohiosalesrevenuedata1-2015" ohio's january, 2015 data or "alabamasalesrevenuedata12-2015" alabama's december, 2015 data. i'm putting corresponding month number in column large number of spreadsheets in macro. right macro is:

       range("x2").select        activecell.formular1c1 = "1" 

but works january spreadsheets. need replace "1" months months' spreadsheets. there way this? thank you.

i assume trying determine month particular filename.

if so, assuming filename in variable f, , filenames contain words revenuedata (and path not contain revenuedata), following should work:

f = "abcdefrevenuedata12-2015.xlsx" 'for demonstration purposes  if mid(f, instr(f, "revenuedata") + 12, 1) = "-"     range("x2").value = cint(mid(f, instr(f, "revenuedata") + 11, 1)) elseif mid(f, instr(f, "revenuedata") + 13, 1) = "-"     range("x2").value = cint(mid(f, instr(f, "revenuedata") + 11, 2)) else     msgbox "unrecognised filename - " & f     end end if