Formula to browse a specific folder, and return (display) the file with highest number???

I'm not sure if this is even possible; but if anyone were to know, it'd be you folks!


I have a specific folder (C:\xxx\xxx\xxx\Folder) that contains several .xlsm files (among other types).

The last 6 characters of each filename is a number (IE: TEST_NAME 010203.xlsm, TEST_NAME 011403.xlsm, TEST_NAME 112112.xlsm, etc)



I'm looking for a formula that will search within that specific folder; determine which ".xlsm" file has the highest last 6 characters, and display those characters in the cell.



Is that even possible with a formula? If not, would it be possible wtih VBA?

Thanks for any light you can shed!

I doubt the possibility to do it with formulas, but it is not my style to say it is not possible. Here is a code how you could do it with a macro. Note that the code loops now through all the files in the folder.

Please Login or Register  to view this content.

Hi,
I figured it was a long shot via formula, but it was worth a shot. VBA it is...

I tried the code you posted, and it seems to only return the value of 0.

Perhaps we might be able to simply things a bit then...


Say I change up the folder so that there is only one .xlsm file in the folder, and I wanted to paste that filename (filename only) in cell A1. How would I type that up?

You may debug the code by setting a break point and check, if the variable file stays null. I guess that the backslash after the directory is missing therefore it doesn't find any files and returns 0.
It must be C:\xxx\xxx\xxx\Folder\.
If you have just one file in the folder and want to paste the whole name in cell A1, then the code would be something like

Please Login or Register  to view this content.

Hi,
After some testing, I came to the same conclusion.

Thanks for the follow up!!!