Split File Name Into N Parts via Delimiter

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:

Sub GetSharePointFiles()

    Dim SharepointAddress As String
    Dim InputMonth As Integer, getMonth As String, getYear As String
    Dim txtSplit As String, strNum As Long
    Dim objFolder As Object
    Dim objNet As Object
    Dim objFile As Object
    Dim FS As Object
    Dim fKey As Object
    Dim rng As Range
    Dim ws As Worksheet
    Dim objXMLHTTP As Object
    Dim FN As File

    Set ws = ThisWorkbook.Worksheets("Control Panel")
    
    InputMonth = ws.Range("myMonth")
    getYear = ws.Range("myYear")
    
    'Step 1: Replace the format so we can know which folder to look into
    Select Case InputMonth
        Case 1
            getMonth = "January"
        Case 2
            getMonth = "February"
        Case 3
            getMonth = "March"
        Case 4
            getMonth = "April"
        Case 5
            getMonth = "May"
        Case 6
            getMonth = "June"
        Case 7
            getMonth = "July"
        Case 8
            getMonth = "August"
        Case 9
            getMonth = "September"
        Case 10
            getMonth = "October"
        Case 11
            getMonth = "November"
        Case 12
            getMonth = "December"
    End Select
    
    'Step 2: Find the active workbook's SharePoint site so we can map it locally
    SharepointAddress = ActiveWorkbook.Path
    'MsgBox SharepointAddress
    
    'Step 3: Initialize mapped drive. Maybe later on make this dynamic. Static for now.
    'Set objNet = CreateObject("WScript.Network")
    Set FS = CreateObject("Scripting.FileSystemObject")
    Set fKey = CreateObject("Scripting.Dictionary")
    'objNet.RemoveNetworkDrive "Z:"
    'objNet.MapNetworkDrive "Z:", SharepointAddress
    
    'testing filedatetime for this file
    'MsgBox FileDateTime("Z:\GetFilesFromSPO.xlsm")
    
    'Testing to get files and folders from mapped drive.
    'Step 4: Somehow loop through all the files in here and return the latest created date
    Debug.Print ActiveWorkbook.Path & "\" & getYear & "_" & getMonth
    Set objFolder = FS.GetFolder("" & ActiveWorkbook.Path & "\" & getYear & "_" & getMonth & "")
    For Each FN In objFolder.Files
        txtSplit = Split(UCase(FN), "_")
        For strNum = 0 To UBound(txtSplit)
            'STUCK ON SPLITTING INTO ONLY TWO PARTS
        Next
    Next FN
    
    'Step 5: Open last created date and copy worksheet("Summary")
    'We paste into this workbook under new name: 2nd delimiter, ie, MS or Compensation
    
    
    'Step 6: Save file and remove networked drive
    'Disabling for now for testing
    'objNet.RemoveNetworkDrive "Z:"
    'Set objNet = Nothing
    'Set FS = Nothing

End Sub

Help would be greatly appreciated!

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:

    For Each FN In objFolder.Files
        Debug.Print FN.Name
        txtSplit = Split(UCase(FN.Name), "_")
        txtJoin = txtSplit(0) & "_" & txtSplit(1) & "_" & txtSplit(2)
        Debug.Print txtJoin
        If Not Category.Exists(txtJoin) Then
           Category.Add txtJoin, txtJoin & " | " & 1
        ElseIf Category.Exists(txtJoin) Then
            'Work in progress to update
            Category(txtJoin) = Category(txtJoin) + Split(txtJoin, " |")(1) + 1
        End If
    Next FN

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

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:

    Dim p1 As Integer
    Dim LatestDate As Date
    For Each FN In objFolder.Files
        p1 = InStr(1, FN.Name, getMonth)
        If p1 > 0 Then
            txtSplit = Right(FN.Name, Len(FN.Name) - p1)
            txtJoin = Left(UCase(FN.Name), p1 - 2)
            If Not Category.Exists(txtJoin) Then
                Category.Add txtJoin, txtJoin & "|" & FN.Name & "|" & FN.DateCreated
            ElseIf Category.Exists(txtJoin) Then
                LatestDate = Split(Category(txtJoin), "|")(2)
                If FN.DateCreated >= LatestDate Then
                    Category(txtJoin) = txtJoin & "|" & FN.Name & "|" & FN.DateCreated
                End If
            End If
          End If
    Next FN

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