excel - Loop through folder, renaming files that meet specific criteria using VBA? -

excel - Loop through folder, renaming files that meet specific criteria using VBA? -

i new vba (and have bit of training in java), assembled bit of code help of other posts here , have nail wall.

i trying write code cycle through each file in folder, testing if each file meets criteria. if criteria met, file names should edited, overwriting (or deleting prior) existing files same name. copies of these newly renamed files should copied different folder. believe i'm close, code refuses cycle through files and/or crashes excel when run. help please? :-)

sub renameimages() const filepath string = _ "c:\\currentpath" const newpath string = _ "c:\\aditionalpath" dim strfile string dim freplace string dim fprefix string dim fsuffix string dim propfname string dim fileexistsbol boolean dim fso object set fso = vba.createobject("scripting.filesystemobject") strfile = dir(filepath) while (strfile <> "") debug.print strfile if mid$(strfile, 4, 1) = "_" fprefix = left$(strfile, 3) fsuffix = right$(strfile, 5) freplace = "page" propfname = filepath & fprefix & freplace & fsuffix fileexistsbol = fileexists(propfname) if fileexistsbol kill propfname end if name filepath & strfile propfname 'fso.copyfile(filepath & propfname, newpath & propfname, true) end if strfile = dir(filepath) loop end sub

if it's helpful, file names start abc_mm_dd_hh_page_#.jpg , goal cutting them downwards abcpage#.jpg

thanks much!

i think it's thought first collect of filenames in array or collection before starting process them, particularly if you're going renaming them. if don't there's no guarantee won't confuse dir(), leading skip files or process "same" file twice. in vba there's no need escape backslashes in strings.

here's illustration using collection:

sub tester() dim fls, f set fls = getfiles("d:\analysis\", "*.xls*") each f in fls debug.print f next f end sub function getfiles(path string, optional pattern string = "") collection dim rv new collection, f if right(path, 1) <> "\" path = path & "\" f = dir(path & pattern) while len(f) > 0 rv.add path & f f = dir() 'no parameter loop set getfiles = rv end function

excel vba excel-vba excel-2013