Get list of Excel files in a folder using VBA -

i need names of excel files in folder , make changes each file. i've gotten "make changes" part sorted out. there way list of .xlsx files in 1 folder, d:\personal , store in string array.

i need iterate through list of files , run macro on each of files figured can using:

filepath = "d:\personal\" each in filearray     workbooks.open(filepath+i) next 

i had @ this, however, wasn't able open files cause stored names in variant format.

in short, how can use vba list of excel filenames in specific folder?

ok might work you, function takes path , returns array of file names in folder. use if statement excel files when looping through array.

function listfiles(byval spath string)      dim vaarray     variant     dim           integer     dim ofile       object     dim ofso        object     dim ofolder     object     dim ofiles      object      set ofso = createobject("scripting.filesystemobject")     set ofolder = ofso.getfolder(spath)     set ofiles = ofolder.files      redim vaarray(1 ofiles.count)     = 1     each ofile in ofiles         vaarray(i) = ofile.name         = + 1     next      listfiles = vaarray  end function 

it nice if access files in files object index number that seems broken in vba whatever reason (bug?).