How to extract a string between brackets... i.e. [This is the text I need] in VBA

I am trying to automate the process of changing the source of a link to another workbook and I'm running into some trouble.

I managed to get the desired result for finding the latest version of the Fleet Count file, but I need to find a way to set the Name variable at the bottom to the actual file that's currently linked.

One way of doing this would require RegEx... Here's my idea:

Within the active workbook, there is a cell that contains the workbook path that is currently linked: ='S:\US\Projections\2022\Fleet Count[Fleet Count Projection 07Act08Fv2.xlsm]!Sheet$HZ4

I would just need to extract the bold string between the brackets in order to get the functionality I desire.

Sub Update_FleetCount_Link()

    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    
    'Specify the path to the folder
    MyPath = "S:\US\Projections\2022\Fleet Count\"
    
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.xlsm", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
    
    'Change link to latest file in folder
    ActiveWorkbook.ChangeLink Name:= _
        MyPath & **???**, _
        NewName:=MyPath & LatestFile, Type:=xlExcelLinks
End Sub