first of all, please excuse shortcomings in presenting issue haven't got knowledge in vba. kindly appreciated.
i working on project imply putting content of 3 different excel files 3 different sub-folders 1 excel file, , run macros in order process data contain. since i've set processing macros, issue relies in importing content correctly.
the problem i'm facing don't have exact names of files open, , change each month. therefore, can't use "workbooks.open" command requires precise name. however, files have predictable name formats. instance, 1 of sub-folders comprised of files named "xxx-jan2013.xls", 1 "january2013-xxx" , last 1 "xxx-01/2013".
my goal input month , year manually, instance “01/2013”, , open files containing "january”, “jan” or “01" in names.
here’s have far, comments:
sub chosendate() ‘it aims @ opening box in desired month written manually dim inputdate string ‘these indications user inputdate = inputbox(prompt:="please choose month.", _ title:="date", default:="mm/yyyy") ‘in case person forgets write he’s asked if inputdate = "mm/yyyy" or _ inputdate = vbnullstring exit sub ‘if correctly, call second sub else: call filesopening end if end sub ‘so far, works fine public sub filesopening() ‘this 1 aims @ opening chosen files dim thisfile string dim files string ‘defining folder in file is, can change computer thisfile = thisworkbook.path ‘here’s start struggling , macro doesn’t work anymore ‘if wanted open files of folder, write that: files = dir(thisfile & "\*.xls") ‘you never know… on error resume next ‘creating loop while files <> vbnullstring files = dir set wbbook = workbooks.open(thisworkbook.path & "\" & files) loop end sub ‘but doesn’t inside of sub-folders, neither consider date sub dataprocess() ‘this 1 fine, except can’t find way name files correctly. here’s beginning: windows("i don’t know name.xls").activate sheets("rapport 1").select cells.select selection.copy windows("the file want put data in.xlsm").activate sheets("where want put it").select range("a1").select activesheet.paste windows("i don’t know name.xls").close ‘how can name? i hope statement understandable.
thank in advance!
have nice day,
e.
you need build list of paths , expected file masks. can loop each matching file , stuff.
sub foo() dim request string: request = "01/2013" '//make date dim asdate date: asdate = "01/" & request dim dirs(2) string, masks(2) string dirs(0) = "c:\xxx\dir1\" masks(0) = "*" & format$(asdate, "mmmmyyyy") & "*.xls" dirs(1) = "c:\xxx\dir2\" masks(1) = "*" & format$(asdate, "mmmyyyy") & "*.xls" dirs(2) = "c:\xxx\dir3\" masks(2) = "*" & format$(asdate, "mmyyyy") & "*.xls" dim long = 0 ubound(dirs) getfiles dirs(i), masks(i) next end sub private function getfiles(path string, mask string) dim file string '//loop matching files file = dir$(path & mask) until len(file) = 0 '//process match process path & file file = dir$() loop end function sub process(filepath string) msgbox "processing " & filepath 'workbook.open end sub as "xxx-01/2013" not file name assumed "xxx-012013". if subdirectory just:
dirs(x) = "c:\xxx\dir3\" & format$(asdate, "mm") & "\" masks(x) = "*" & year(asdate) & "*.xls" 
Comments
Post a Comment