Function to return last modified file

First of all, I don't really know VBA more than just piecing together other people's work (so please dumb down your responses as much as possible).

So I would like to have a function that displays a filename within a cell. The filename I need to display will start with "cost" and have a date at the end (not always the same number of characters). I need to search a folder for all files that contain that text and return the last modified one.

I found this VBA code that returns the last modified file, but only filters based on the extension (which I would like to keep in the code as well).

Please Login or Register  to view this content.

I found a code that would filter the files but don't know how or where exactly to put it in:

Please Login or Register  to view this content.

Any help would be greatly appreciated.


EDIT: By the way, I would like to have the part of the filename used to filter be taken from a cell. In other words, if the beginning of the filename I am searching for changes, I would like for users to be able to update the search criteria in a cell rather than having to open the VBA code. This isn't necessary but would be a plus.

dissonanceUS,

See:
http://www.ozgrid.com/forum/showthread.php?t=21485

http://vbaexpress.com/forum/showthre...t=10829&page=2

http://vbaexpress.com/forum/showthread.php?t=10829

http://www.ozgrid.com/forum/showthread.php?t=74025

Have a great day,
Stan

Originally Posted by Leith Ross

Hello dissonanceUS,

After looking at the other examples you posted, I hope you can clear up a few questions I have, so I can write something that will work for you.

1. The cell you are referring to will contain the file to be search for, yes?
2. Can you provide an example of one or more file names?

It will contain the part of the filename that will be used to search, yes.

For example, the cell being referenced will contain "cost" and will be searching for one of the following files:
cost_12dec2008.xls
cost_13april2008.xls
cost_8may2008.xls
cost_6may2008.xls

Originally Posted by Leith Ross

3. Will these files all have the same extension or do you want to match only the file name?

All of the files I care about will be Excel files (.xls) but the folders will probably have other files in it with similar names so it would be nice to have it filter out extensions as well.

Originally Posted by Leith Ross

4. Do you want to select the folder using a file dialog or have it hard coded into the macro?

I would like for the directory path to be referenced from a cell. So if the directory changes, which usually happens at least yearly, it would be simple for any user to correct.

Originally Posted by Leith Ross

5. Where do you want the results to be logged, on another worksheet or somewhere else?

Sincerely,
Leith Ross

I don't need a log output, just the single file name to be inserted into a cell (cell E2 in the example screenshot). I already have a macro that uses a filename contained in a cell and a directory path in another cell to fill in the rest of the worksheet by drawing the link from those 2 cells.

An example of what the top of the worksheet looks like is attached as "vba-cost.jpg"

The code I am using to fill in the worksheet is like this:

Please Login or Register  to view this content.