Macro lists files in folders & subfldrs - Need to add ability for user to select folder

Hello Everyone -

I have borrowed some code I found here:
http://www.exceltip.com/st/List_file...Excel/446.html
that lists all files in a given folder and also all files within any subfolders underneath the given folder. I would like to modify this code to:

1-Allow the user to select the folder from which to list all files using some sort of dialogue box
2-Allow the user to select a date and only list files created after that date

I am using Excel 2010. Here is the code (I have made a few slight modifications for my purposes):

Please Login or Register  to view this content.

I am guessing I need to utilize Application.FileDialog(msoFileDialogFolderPicker) in some way for the folder selection part, but I am not sure how to incorporate it.

I have also attached a workbook with my code including my attempt at adding the DialogFolderPicker.

Any help or guidance anyone could provide would be greatly appreciated!

Originally Posted by BeachBum08

1-Allow the user to select the folder from which to list all files using some sort of dialogue box

Yes, you could use Application.FileDialog. For a routine which encapsulates this function, see http://www.cpearson.com/excel/browsefolder.aspx (Using the FileDialog). You would then call BrowseFolder from ListFilesInFolder, and pass the returned string (if it isn't vbNullString) as the first argument to ListFilesInFinanceFolder (instead of the hard-coded file path).

2-Allow the user to select a date and only list files created after that date

You could use the Calendar Control on a userform - see http://www.fontstuff.com/vba/vbatut07.htm. Pass the date as another parameter to ListFilesInFinanceFolder, and use If FileItem.DateCreated > theDate Then, to list files created after that date.

Show the whole If statement:

Please Login or Register  to view this content.

But you said you have created a ".PDF" document, and compare the dates as shown in my first post; you don't need to use Format or Short Date because FSO file dates are the same as Excel dates - both are numbers. Therefore try:

Please Login or Register  to view this content.

If still no joy, try debugging the code in the VB editor.

See attached workbook "FINAL".

I named the sheet where the date is stored "Instructions" and created a named range "myDate" that includes only cell "F5". Then I adjusted my reference to the date to make it more specific.

Please Login or Register  to view this content.

Now it works great!

Thanks so much for all of your help!