search a folder and return the most recent file's name

Hi! I'm new here and rather new to excell. I am however kindda able with programming logic, although it's my first time with VBA and Excel (I think I've come to the limitation of cell formulas and perhaps UDF for what I want to do)

Objective:
I want Excel to check in a folder on an intranet, find the latest file added in it and return me part of its name.

I got an excel file with many sheets. Each sheet has a hyperlink to a specific folder, different for each sheet.

H2: Holds a link to a folder, which always look like this:
\\server.net\a\b\c\program\report\1234567-report_on_Cie_X

Each folder has a list of weekly reports named like this:
1234567CieX__1696_VO.xls
1234567CieX_1693_VO.xls
1234567CieX_1694VO.xls
1234567CieX_1695_VO.xls

Usually the last file in the folder will be the most recent one, but as you see here, sometimes files are not named properly and since I'm not responsible of that, I'm looking for an idiot proof solution that would work even if the file I'm searching for has been wrongly named.

I need a function that will do this:

1- Search within the folder located in cell H2
2- For each file, extract the 4 digits number of the week (ex: 1694)
(in a cell i'd do mid(A1;find(folder.file(x);"_1";4))
3- Put that number aside (in a table?)
4- From these numbers, return the one that has the highest value (in this ex: 1696)

Of course I don't expect anyone to build the code for me, but if anyone could help me by directing me to the right function/properties to do each step, I could manage to build the rest, or come back with a closer to complete code.

For the steps above, here are my questions, pick one :D

In an ideal world, I'd like this function to process itself without being prompted, to occasionally search for the newest file in each folder of each sheet. Problem is: I got 30-40 sheets, and having excel search in files for each of these folder will eat lots of computer resources so i don't think/know if having this being done too often is a feasible. Would it be more realistic to have the code be triggered by a button?

1- What function would I use to search a folder. I read a bit on the function Files("address/*.*"), but I don't know how to access the list of files found or how to use Index() properly to do so. Also, it seems like a slow function.

2- Not sure how to extract the 4 digit (ex:1694) from a filename
3- No idea how to create table array
4- No idea how to search for the highest value within a table array

Thanks for your help, sorry for such a rookie post... If you got any spare time, this would be most appreciated!

I'm on a Excel 2003