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

15,649

Solution 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.

Solution 2

I would recommend using built in functions (e.g. MID() and INSTRREV()) over creating external objects to do what you want.

The "answer" you posted is on the right track - though since you posted it in the form of a question, I think it would have been better served as an edit to your original question.


To answer your answer-question:
Is the approach valid? Yes.
Will you run into issues? For this application - probably not. But I would advise against hardcoding the number of characters in the extension. I don't see Solidworks changing their drawing extension anytime soon - but it's possible (e.g. look at Microsoft: .xls to .xlsx, etc.) and it limits your ability to deal with other extensions (e.g. .Slpdrt, .Sldasm, etc. )

Also, I would cast swDraw.GetPathName to a variable to lower the overhead of repeatedly calling that function on the swDraw COM object.

E.g.

Dim FilePath as String
FilePath= swDraw.GetPathName

You can do what you want in one line:

Mid(FilePath, InStrRev(FilePath, "\") + 14, InStrRev(FilePath, ".") - InStrRev(FilePath, "\") - 14)

Test:

Sub QuickTest()
    Const FilePath= "C:\Users\m.jones\Desktop\New folder (2)\LS4102-104-01 Fixed Table.slddrw"
    MsgBox Mid(FilePath, InStrRev(FilePath, "\") + 14, InStrRev(FilePath, ".") - InStrRev(FilePath, "\") - 14)

End Sub

You can make it easier to read using variables:

Sub QuickTest()
    Const FilePath= "C:\Users\m.jones\Desktop\New folder (2)\LS4102-104-01 Fixed Table.slddrw"

    Dim MidStart As Long
    MidStart = InStrRev(FilePath, "\") + 14

    Dim MidEnd As Long
    MidEnd = InStrRev(FilePath, ".")

    Dim MyText As String
    MyText = Mid(FilePath, MidStart, MidEnd - MidStart)

    MsgBox MyText
End Sub
Share:
15,649

Related videos on Youtube

Matt Jones
Author by

Matt Jones

Updated on June 05, 2022

Comments

  • Matt Jones
    Matt Jones over 1 year

    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)
    
  • Bond
    Bond over 8 years
    Unfortunately, won't work for a file named LS4102-104-01 Mr. Smith.slddrw or Article 3.1 Information.xls or any other filename that may contain a period.
  • Dan Donoghue
    Dan Donoghue over 8 years
    Updated to work, purely playing around now though, your solution is by far the superior one.
  • Matt Jones
    Matt Jones over 8 years
    Thanks.. will this work with different file paths? The macro is used on a variety of different projects!
  • Matt Jones
    Matt Jones over 8 years
    Does this mean I have to edit the macro for every project or can i get it to look at the path/filename of the active document using swDraw.getpath?
  • Bond
    Bond over 8 years
    Whatever method you use to get the file paths of your documents is up to you. Just replace strFile above with your actual file path variable to get the abbreviated file name.
  • CBRF23
    CBRF23 about 8 years
    This will work for files containing periods, by the way. E.g. LS4102-104-01 Mr. Smith.slddrw or Article 3.1 Information.xls
  • CBRF23
    CBRF23 about 8 years
    I upvoted because it's helpful and fairly elegant, but I would recommend using built in functions over relying on an external object (e.g. FSO). You can use string parsing to find files with periods in their name by using InStrRev to search from the right to left for the last period (the period before the extension).
  • CBRF23
    CBRF23 about 8 years
    Actually, while the code would work, Article 3.1 Information.xls would return "nformation" given what we've defined as the start point.
  • Bond
    Bond about 8 years
    @CBRF23 - I can certainly appreciate wanting to use "built-in functions", but the FileSystemObject has been installed by default on every version of Windows since 98. It's a COM object being used by a COM language. And frankly, it's much more convenient to use than VBA's file system features like Dir, which makes recursion (and many other things) a nightmare. No need to reinvent the wheel and muddy your application with VBA rewrites of features that already exist in a supplementary library. I mean, we wouldn't expect a VBA rewrite of VBScript.RegExp just to use regular expressions.
  • CBRF23
    CBRF23 about 8 years
    I'm not suggesting a rewrite - I can handle the requested function in a single line using Mid and InStrRev - can't do that using FSO. Using built in functions we get intellisense, you'd have to create a reference to the scripting.dictionary library and use early binding get it using an FSO. Plus, while it may have been included with all major windows releases - everything has a lifetime and there is no guarantee of future support (wait til 32 bit support is dropped, like 8 and 16 were). Lastly, we have the issue of disposal of COM objects in VBA - which is too vast to cover in comments.
  • Bond
    Bond about 8 years
    @CBRF23 - 1. You can do it in a single line using an FSO: Mid$(CreateObject("Scripting.FileSystemObject").GetBaseName(‌​strFile), 14). It's shorter and likely more readable than yours since it describes what it's doing. 2. Disposal of COM objects isn't an issue. End With disposes of it. In the one-line version, it's disposed of immediately. 3. Your version may work for this example, but relying on a period to chop off a file's extension isn't reliable. Some files don't have extensions. The hosts file, for example. So your new function to replace GetBaseName just got more complicated.
  • CBRF23
    CBRF23 about 8 years
    1) touché! 2) you may be right; in .NET the point an object goes out of scope is not necessarily the point at which it is disposed. The .NET GC is well documented, but I can't find anything on the VBA GC right now. 3) OP's question was not how to replace GetBaseName; he wants to extract part of a a file name from a path. Both our solutions do exactly what he wants based on the provided criteria.
  • Bond
    Bond about 8 years
    @CBRF23 - Just to be clear, you don't need 3 lines. The single-line version I posted in the previous comment works fine. I split it into 3 lines in my answer to make it more readable. My point about GetBaseName was that there is already a function that deals with the intricacies of getting the base filename. And you can leverage that. So when the next guy asks a question that involves getting the base filename, you don't have to rewrite your custom function that worked only for this answer. I like your answer. I considered doing it that way myself. But for the reasons mentioned, I chose FSO.
  • CBRF23
    CBRF23 about 8 years
    Yes you can! I edited my comment after re-reading yours - sorry for not reading it closer the first time through. And yes, both approaches are valid and have there own pros/cons. FSO is definitely more readable. For me, I tend to go with code that relies less on external libraries where I can. But that's exactly what's so great about stack overflow, you get all these different answers to chose from!