How do I extract a portion of a file name in VBA?

Question

I need to extract a portion of a file name from the filepath. My macro needs to be able to handle paths/names of varying length, but the porition of the file name I want always starts at the same place; I need to extract the portion of just the filename starting 14 characters in from the beginning and ending before the file extension (excluding the ".").

For example, I want my macro to extract the text "Fixed Table" from the following path name:

C:\Users\m.jones\Desktop\New folder (2)\LS4102-104-01 Fixed Table.slddrw

---

EDIT:

I just experimented with this, and the code below seems to work. Is this a valid approach, or am I going to run in to issues?

PartNoDes = Mid(swDraw.GetPathName, InStrRev(swDraw.GetPathName, "\") + 1)
PartNoDes = Right(PartNoDes, Len(PartNoDes) - 14)
PartNoDes = Left(PartNoDes, Len(PartNoDes) - 7)

Answer 1

You can use a FileSystemObject to get the base filename (i.e., the filename without extension) and then use Mid() to extract a portion of it.

Const strFile As String = "C:\Users\m.jones\Desktop\New folder (2)\LS4102-104-01 Fixed Table.slddrw"

With CreateObject("Scripting.FileSystemObject")
    Debug.Print Mid$(.GetBaseName(strFile), 14)    ' => "Fixed Table"
End With

This method should be preferred over string parsing that looks for \ and . because filenames may contain periods that aren't part of the extension.