code to search files in folder then update dynamic link

[mcfadwmc]

i've got a set of files in a folder for each day of the month and they're all in the format like this

THURS 1.12.05
FRI 2.12.05
SAT 3.12.05
SUN 4.12.05
MON 5.12.05

What i want to do is have some code that will look for the most recent file for a particular day and get the data from cells in a sheet eg.

'AS400 EST!BB5:BB25

So for instance when i create a new file named THURS 8.12.05 it would disregard the data from THURS 1.12.05 and input from THURS 8.12.05 instead.
I've had a look at ways to do it using a code that gets the names of all the files in the folder then i've added to it to split up this output to different columns then ranks the dates, looks up the most recent occurance of a day (eg, THURS) then sticks the bits back together and try to INDIRECT this but as you can imagine it doesn't work (cause i'm not that clever!!)

Can anyone give me ideas on where to start or another way i should be looking at to solve this

Many thanks in advance

 

[bk]

hopefully, this will help you get started

but I'm not sure I understand enough that it will be all the information you need.

First, this is how I target a folder, I use this code:

With Application.FileSearch
     .NewSearch
     .LookIn = "my path and folder"
     If .Execute() > 0 Then
          For variable = 1 to .FoundFiles.Count
               my action(s)
          Next i
     End If
End With

I'm not sure how you are getting your date initially, but if it's in a cell, you should be able to use the MONTH(), YEAR() and DAY() functions to get each component of the date separated and then, compare that to the name of your files.

Getting that information from the filenames will be a little tricky, but should be doable. First, the day of the week is useless for what you want, so you ignore that. It looks like the dates are not in a consistent length when the weekday abbreviation varying in the number of characters it can have and no leading zeros for the numbers in the date itself.

So, I think you can use a combination of the FIND and/or MID functions to key off of the period and pull your month, day and year out of the filename. If you always pull 2 characters for the day and month, you can use the TRIM function to clean out the space when it's a single-number day/month.

Like I said, this is just a start for you. The inconsistencies on the filenames will make it more complicated than it could've been if you had been able to trust that the weekday abbrevation always took up 3 characters and could start with the 5th place for the month and then, have it always in 2 characters, etc. But it can still be done, I believe. Hope this is of some help to you.