Get list of file names / Document Index

Hello all. I am fairly new to VBA but experienced with Excel.
We have a folder for each project that contains the documents for that project. Also in this folder is an XLS that lists all the documents in that project folder. A Document Index.

Currently one person has to manually type the names of the files contained in the project's document folder into this Document Index. I would like to have this task done automatically for her.
Here is a stripped down version of the Document Index XLS: Document Index.xls

Note I have a command button in there that says "Get File Names." I'd like for the user to be able to click that button and all the files that are in the folder (that also contains this workbook) are then listed in the appropriate column.
Some visual aid...
Before:
GetFileNames - Before.PNG

Contents of the folder that the Document Index is in:
GetFileNames - FolderContents.PNG

Click the Get File Names button...
After:
GetFileNames - After.PNG


Note I'd need the macro to get the file names of the documents in the folder that this Doc Index is in, and no other folder's contents. Also it would be very nice if the type of the document could be entered in the column to the right of the document name. (See example picture)
A folder such as this will exist for each of our projects, containing a Document Index, and PDF documents.

The user who has to fill out the document index has basic knowledge of computers and excel. So going the DOS command prompt isn't an option in this case.


I've been searching for a couple days now and the closest I got to something was code from this site:
http://www.vbaexpress.com/kb/getarti...da54213fdf7d82

It works, but it brings in the entire file path and separates it into columns, I don't need the entire path, just the name of the file. It also pastes the info starting at A1, and overwrites any cells that "get in the way"
I do not know what ALL the commands in that code mean, I suspect there are extra lines in it that aren't needed for what I'm after.
However I was able to figure out and read the instructions to change it to only pull fill names of the documents in the folder that the workbook is in. (partial win ) This code is in the XLS I attached. If I need to delete it and start from scratch please let me know.

One of the abilities I need is to be able to change where the file names get inserted.


Please be patient with my lack of experience in VB and with it being the weekend. I will check back on the thread as often as I can.
Any help in this matter will be greatly appreciated. Thank you.

Please Login or Register  to view this content.