How do I extract a portion of a file name in VBA?
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
Related videos on Youtube
Matt Jones
Updated on June 05, 2022Comments
-
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 over 8 yearsUnfortunately, won't work for a file named
LS4102-104-01 Mr. Smith.slddrw
orArticle 3.1 Information.xls
or any other filename that may contain a period. -
Dan Donoghue over 8 yearsUpdated to work, purely playing around now though, your solution is by far the superior one.
-
Matt Jones over 8 yearsThanks.. will this work with different file paths? The macro is used on a variety of different projects!
-
Matt Jones over 8 yearsDoes 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 over 8 yearsWhatever 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 about 8 yearsThis will work for files containing periods, by the way. E.g.
LS4102-104-01 Mr. Smith.slddrw
orArticle 3.1 Information.xls
-
CBRF23 about 8 yearsI 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 about 8 yearsActually, while the code would work,
Article 3.1 Information.xls
would return "nformation" given what we've defined as the start point. -
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 likeDir
, 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 ofVBScript.RegExp
just to use regular expressions. -
CBRF23 about 8 yearsI'm not suggesting a rewrite - I can handle the requested function in a single line using
Mid
andInStrRev
- 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 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. Thehosts
file, for example. So your new function to replaceGetBaseName
just got more complicated. -
CBRF23 about 8 years1) 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 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 about 8 yearsYes 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!