Split File Name Into N Parts via Delimiter

[huyza_0100]

Good afternoon All!

I am working on a macro that looks at a SharePoint site for files in a folder. The idea is to retrieve the last created date for each file category within a certain naming convention. I'd like to split the file name into two parts based on the 3rd delimiter. Create the dictionary and eventually loop through all the file names to see which ones inside the folder are the last created date.

Here are my steps I'm trying to create within this macro

1. Replace format with switch to know which folder to look into [DONE]
2. Find active workbook's path [DONE]
3. Map to temporary drive [DONE]
4. Loop through all files and return latest created date per category
5. Open last created date and copy worksheets("Summary") and paste into this workbook under new name by second delimiter, ie, MS or Compensation

Example files for step 4 looks like this:
Z:\2019_January
GM1_PL1_Compensation_January_2019_01.xlsb
GM1_PL1_Compensation_January_2019_02.xlsb
NAL_WL_MS_January_2019_01.xlsb
NAL_WL_MS_January_2019_02.xlsb

Example harvest would look like this:
GM1_PL1_Compensation
NAL_WL_MS

And finally, getting the latest created by date for each category:
GM1_PL1_Compensation_January_2019_01.xlsb
GM1_PL1_Compensation_January_2019_02.xlsb [LATEST FILE]
NAL_WL_MS_January_2019_01.xlsb
NAL_WL_MS_January_2019_02.xlsb [LATEST FILE]

Here's my current code so far:

Please Login or Register  to view this content.

Help would be greatly appreciated!

[Keebellah]

Hi, don't use split when you're looking inside the filename
in this part

Please Login or Register  to view this content.

Try something like this:

Please Login or Register  to view this content.

Another way is to get last date modified attribute of a file
Hope this helps a little

[huyza_0100]

Hi @Keebellah!

Thanks for looking into this for me! I'll definitely try your method. If I wanted to adjust the code to return the left split for everything before 'January' how would I achieve that?

I've been thinking and working on a different version and this is what I have currently that gets me really close to what I need:

Please Login or Register  to view this content.

I was having issues using the JOIN function but I thought about just joining them manually

[Keebellah]

Very simple everything before the month is just the left of the text, p1

Please Login or Register  to view this content.

[huyza_0100]

Thanks for the direction Keebellah!

I ended up using your method instead since it looks cleaner and added a check for latest date. Here's the working code if anyone else is looking for something similar:

Please Login or Register  to view this content.

PS. I can't find the option to harvest the Author's name of the file that was created. Is it possible?

[Keebellah]

You should try and use Google

For example here's one link that popped up
https://stackoverflow.com/questions/...ile-attributes
Another one
https://docs.microsoft.com/en-us/off...butes-property