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

Hello Pink_Mtl,

Welcome to the Forum!

This macro can be used as a UDF, attached to a button, called from an event, or run manually. The argument is a reference to the cell that contains the Hyperlink to the folder. If the cell does not contain a hyperlink or the hyperlink is not a valid folder then the macro exits immediately.

The macro will only check files that have an .xls extension. These files are then matched against a pattern. Those that match have the digits extracted and converted into a number. The numbers are loaded into a 1-D array. After all the files have been examined, the greatest value in the array is returned by the function.

Please Login or Register  to view this content.

Hello Pink_Mtl,

Here is a macro to go through all the worksheets in the workbook and call the GetLastFileID macro. Not sure what you want to do with the returned value.

Please Login or Register  to view this content.

Hello Pink_Mtl,

So the HyperLink part of the macro needs to be removed then? Skipping the other worksheets is easy. Do you want the full file path or just the latest file number returned?

Nope, the hyperlink is always in H2, we use it to open the given folder directly to go take our reports so it's quite useful.

The numbers we fetch are representing a specific weeks. It's like a home code for a week, so instead of saying it's the report of the week of October 4th 2010, we just need to write it's week 1685. The goal for me is to know which is the most recent week's report that was produced. Each worksheet represents a specific customer for which there will be weekly reports generated. I need something visual to let me know something new has arrived to avoid opening the folder by clicking H2 and see that there is or not something new there.


H2: Holds the link to the folder
G3: result from VB code and gives us the number of the latest's week report.

A4:A57: indicates the code number of the week
G4:G57: include a formula IF(A4=G3:"O":"")

This way, if G3 got the value of 1693, we'll see something similar to this:

1689 data
1690 data
1691 data
1692 data
1693 ____ O
1694 ____
1695 ____

And then we visually know that the latest weekly report ready is from week 1693 so I should go fetch it's data. If the O was next to 1692, I'd know im all up to date since i already fetched the dada before.

This is simplified of course, but the idea is just that simple.

After that, under tab "Tout", I have a big table giving me the main stats of all of the 30+ worksheets in my workbook. I'll be able to use this G3 week code to identify which worksheet has a new report for which data has not yet been fetched, so in a single look i'll know where I'm up to date and where exactly I should go fetch the new data, instead of browsing each worksheet, opening the folder in H2, see if a new file is there, and close it. It's gonna be simply magical for our needs :D

Hello Pink_Mtl,

Copy this code into a standard VBA module. Run the main macro from the Macro Dialog by pressing Alt+F8. If this works out okay then well proceed to the next step.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Hello Pink_Ntl,

Nice troubleshooting on the problem and my apologies for the typo. The problem is most likely caused by the hyperlink being created using the =Hyperlink formula. Not all hyperlinks are created equal. These are not recognized by VBA. The solution was to parse the cell formula for the hyperlink and extract the folder location. Got to love Regular Expressions. I tested the macro and it worked for me. Replace the old macro with this one.

Please Login or Register  to view this content.

Hello Pink_Mtl,

I did not include the MainMacro with the previous post because no changes were made to its code. If you replaced the module the module code with only the code from the last post then MainMacro was deleted. Copy MainMacro into the same module as GetLatestFileID and everything should work.

Please Login or Register  to view this content.

Hello Pink_Mtl,

Would it be possible for you to post a copy of the workbook? It would be a big help in resolving the problem.