Automatically identifying the most recent file name


I've got a macro which returns the names of all the files within a folder into sheet 2. In sheet 1 is the names of the original files in that folder. All the file names have a standardized file name structure (e.g. 123456-Test-Exp-V1). I need a macro that will identify the latest file names. For instance 123456-Test-Exp-V1 may be in sheet 1 but if the macro returns 123456-Test-Exp-V2 in sheet 2 then I need the record in sheet 1 to be replaced by this new version. Also if no matching record is found I need those file names to be put into a new row in sheet 1.

I really hope this makes sense. I appreciate any suggestion on this topic.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
to visualise this, can you upload a sample here?
x5225642Author Commented:
Sorry for the delay work has been hectic!

I've attached a rough example... in the yellow box in sheet 1 you will need to change the file path to where you save the files. Unfortunately I was unable to include part of the file structure the macro reads but you can see the file name structure I'm using.

As I said the main issue I'm having is populating the summary table as described above.

If you need any further information please let me know.

Thank you for your help
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I got this error when trying to click the "search" button with my own valid local path.
error 400
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

x5225642Author Commented:
I sometimes get that error too but it still works for me even when this happens.

Did it not return any of the file names?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, now I'm managed to get some results in Sheet1. And I have also tried to execute the "Extract" button.

so, it seems that Sheet2's content is remaining the same?

below is what I have for Sheet1, Sheet2 and Sheet3 after running "Search" and "Extract"

x5225642Author Commented:
The extract function only splits up file names by the delimiters "-" and "." as this works with the file name structure I am using (i.e. 123456-Test-Exp-V1.txt). The file names contain all the information I need to populate the summary table in sheet 2.

As it currently stands the extract function only breaks down the file name and displays it in sheet 3. The macro I need is what puts that information into the summary in sheet 2. However, I need to make sure the versions are correctly recorded. So old versions can be greyed out, the new version is displayed on a new row, and if the record doesn't already exist at all it is added to the summary sheet.

Please also note that all file names have the same number of sections broke up by delimiter.

Apologies if I have provided a poor example but I am unable to publish the original or attach a mock file structure.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
is that mean in your folder, there are files like in this format?

x5225642Author Commented:
Yes and all files follow the same type of format. It was the only way I could manage all the data considering that it is in over 200 main folders each containing 50+ sub folders.

That's why the first macro returns folder names in a different colour so the extract function can just pull out the file names to sheet 3.
Jeffrey CoachmanMIS LiasonCommented:
Do you still need help with this...

If you can use Access for this, ...the solution is fairly straightforward...
x5225642Author Commented:
Hi Jeffrey,

Yes I do still need help with this please. I do have Access but to be honest I'm not very confident with that programme. Is there no way of this being achieved in excel?

Thank you for your help
Ejgil HedegaardCommented:
Try this file
Use the "Make summary" button on Sheet1.
Macro is in Module2.
The split on Sheet3 is not used.
The Name in column A did not exist in the filenames, so I have used the first part (=Student).
It is assumed there is only one letter in the version.
Finding last file fails if file version number exceeds 9.
Then you should use T01 instead of T1.
The result are sorted to get the latest version first.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
x5225642Author Commented:
Hi Ejgil,

Many thanks for this. I'll have a go with your suggestions tomorrow.

Thanks again
x5225642Author Commented:
Hi Ejgil,

I've had a go with the macro you have created and it's really close to what I need. I only have two little problems with it. Firstly, it appears to duplicates rows - is there anyway of tweaking it to recognize if the same combination already exists in the summary sheet so it doesn't unnecessarily create more rows? Secondly, one of the file name sections with my real data needs to be split down (e.g. STM14 would mean the file relates to 2014 so I need the 2014 to appear in the summary table) is there any way of achieving this?

Thank you so much for your help so far. I really appreciate it.
Ejgil HedegaardCommented:
The function check if the file split already exist, but it is difficult to see other problems, as the sample only had one example in 3 versions, and there it works ok, and does not duplicate.
The specification was that all file names used the same syntax, like the example "Aimee - English - Test - Example - T1.txt"

Could you upload a sample with more file types, especially the types that duplicate and including some other with the year relation.
Where do you want the year specification in the summary table.
x5225642Author Commented:
The files that was duplicating earlier were excel files. I'll try to pull together an example tomorrow to upload so you can see it. Thanks
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for hgholt's comment #a41241326

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.