x5225642
asked on
Automatically identifying the most recent file name
Hello,
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.
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.
Ryan Chong
to visualise this, can you upload a sample here?
ASKER
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
Records.xlsm
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
Records.xlsm
I got this error when trying to click the "search" button with my own valid local path.
ASKER
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?
Did it not return any of the file names?
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"
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"
ASKER
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.
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.
is that mean in your folder, there are files like in this format?
Aimee_English_Test_Example
Aimee_English_Test_Example
Aimee_English_Test_Example
Aimee_English_Test_Example
ASKER
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.
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.
Do you still need help with this...
If you can use Access for this, ...the solution is fairly straightforward...
If you can use Access for this, ...the solution is fairly straightforward...
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Ejgil,
Many thanks for this. I'll have a go with your suggestions tomorrow.
Thanks again
Many thanks for this. I'll have a go with your suggestions tomorrow.
Thanks again
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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.