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)

Please define exactly what you mean by "end of the file name". Your example is far from definitive. — DeanOC, Jul 27 '15 at 23:54
Look at the following [Link](http://stackoverflow.com/questions/5932909/extract-filename-from-path) — 0m3r, Jul 27 '15 at 23:58
I have no trouble extracting the path, the file name, subtracting characters off the end but I cant work out how to extract the text 14 characters in from the left?? — Matt Jones, Jul 28 '15 at 00:02

Bond · Answer 1 · 2015-07-28T00:11:05.580

4

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.

edited Jul 28 '15 at 00:11

answered Jul 28 '15 at 00:04

Bond

16,071
6
30
53

Thanks.. will this work with different file paths? The macro is used on a variety of different projects! – Matt Jones Jul 28 '15 at 01:08
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? – Matt Jones Jul 28 '15 at 02:35
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. – Bond Jul 28 '15 at 02:49
1

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 Aug 27 '15 at 19:19
@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. – Bond Aug 27 '15 at 20:09
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. – CBRF23 Aug 27 '15 at 20:51
1

@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. – Bond Aug 27 '15 at 21:09
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. – CBRF23 Aug 27 '15 at 21:43
@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. – Bond Aug 27 '15 at 21:49
1

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! – CBRF23 Aug 27 '15 at 21:52

CBRF23 · Answer 2 · 2015-08-27T19:14:35.033

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

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, Aug 27 '15 at 19:11
Actually, while the code would work, `Article 3.1 Information.xls` would return "nformation" given what we've defined as the start point. — CBRF23, Aug 27 '15 at 19:41

Dan Donoghue · Answer 3 · 2015-07-28T00:35:52.973

0

Perhaps this will do what you want, uses Split then takes portions of the resulting array, uses ubound to make sure we have the last instance of a slash and full stop, this negates any issues with full stops being in the name or any levels of directory tree:

Sub FileChop()
Dim MyString As String, FileChop As String
'MyString = "C:\Users\m.jones\Desktop\New folder (2)\LS4102-104-01 Fixed Table.slddrw"
MyString = "LS4102-104-01 Mr. Smith.slddrw"
FileChop = Mid(Split(MyString, "\")(UBound(Split(MyString, "\"))), 14, 100) 'Including extension
MsgBox FileChop
FileChop = Left(FileChop, (Len(FileChop) - 1) - Len(Split(FileChop, ".")(UBound(Split(FileChop, "."))))) 'Excluding extension
MsgBox FileChop
End Sub

edited Jul 28 '15 at 00:35

answered Jul 28 '15 at 00:22

Dan Donoghue

6,056
2
18
36

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. – Bond Jul 28 '15 at 00:30
Updated to work, purely playing around now though, your solution is by far the superior one. – Dan Donoghue Jul 28 '15 at 00:36

score 0 · Answer 4 · answered Jul 29 '15 at 12:50

I know this does not directly answer your question, but SolidWorks provides access to VSTA which uses .NET libraries, which in turn are significantly more powerful than VBA. When you create your macro, there is an option from the pulldown menu for VSTA in VB or C#.

In that case, you could simply use

Imports System.IO

Dim PathName as string = Path.GetFilePath(swModelDoc2.GetPathName)
or
Dim PathName as string = Path.GetFileNameWithoutExtension(swModelDoc2.GetPathName)

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

4 Answers4