MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Creating File Lists

Posted by Jason on November 13, 2001 10:58 AM

I'm trying to create a macro that lists all of the files in a directory. Right now the macro I'm using opens each file and then puts the application name in another sheet and closes the file and goes to the next one. Tis is very inefficiant. Also, if there is a file type that excel doesn't recognize it hangs up. Is there a better way of doing this?

Posted by bob Umlas on November 13, 2001 12:39 PM

Sub GetAllFilesInDirectory()
With Application.FileSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = CurDir()
.SearchSubFolders = True 'or false
For i = 1 To .FoundFiles.Count
something.AddItem .FoundFiles(i) 'where something is some listbox somewhere
End With
End Sub

Posted by faster on November 13, 2001 1:35 PM

good stuff bob eom