Does VBA have built-in functions for extracting just the filename
from a path string? It would also be nice if I could extract the
filename minus the Windows extension. For example, I
might have this string:

"C:\tmp\files\mydata\yearly.docx"

I would need to extract the "yearly" portion. Extracting the whole
filename of "yearly.docx" would be useful too.

Hi Robert,

Am Mon, 11 May 2015 13:06:56 -0700 schrieb Robert Crandal:

"C:\tmp\files\mydata\yearly.docx"

I would need to extract the "yearly" portion. Extracting the whole
filename of "yearly.docx" would be useful too.

look for Instr and InstrRev:

mystr = "C:\tmp\files\mydata\yearly.docx"
mystr = Mid(mystr, InStrRev(mystr, "\") + 1)
mystr = Left(mystr, InStr(mystr, ".") - 1)
MsgBox mystr


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Robert,

Am Mon, 11 May 2015 13:06:56 -0700 schrieb Robert Crandal:

Does VBA have built-in functions for extracting just the filename
from a path string? It would also be nice if I could extract the
filename minus the Windows extension. For example, I
might have this string:

in Word you can use
ThisDocument.FullName 'Path and name with extension
ThisDocument.Path 'Path only
ThisDocument.Name 'Name with extension

The same in Excel:
ThisWorkbook.FullName
ThisWorkbook.Path
ThisWorkbook.Name


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Il giorno lunedì 11 maggio 2015 22:07:02 UTC+2, Robert Crandal ha scritto:
Does VBA have built-in functions for extracting just the filename
from a path string? It would also be nice if I could extract the
filename minus the Windows extension. For example, I
might have this string:
"C:\tmp\files\mydata\yearly.docx"
I would need to extract the "yearly" portion. Extracting the whole
filename of "yearly.docx" would be useful too.

Hi,
try:

Option Explicit

Const s = "C:\tmp\files\mydata.old\yearly.1.docx"

Sub Test()
Test1
Test2
Test3
End Sub

Sub Test1()
Dim p() As String
Dim q() As String
Dim b As String
Dim e As String

p = Split(s, "\")
q = Split(p(UBound(p)), ".")
e = q(UBound(q))
b = Replace(p(UBound(p)), "." & e, "")

Debug.Print s
Debug.Print b, e
Debug.Print
End Sub

Sub Test2()
Dim d As Long
Dim p As String
Dim b As String
Dim e As String

d = InStrRev(s, ".")
p = Left$(s, d - 1)
b = Mid$(p, InStrRev(p, "\") + 1)
e = Mid$(s, d + 1)

Debug.Print s
Debug.Print b, e
Debug.Print
End Sub

Sub Test3()
Dim fso As Object 'Scripting.FileSystemObject
Dim b As String
Dim e As String

Set fso = CreateObject("Scripting.FileSystemObject")
With fso
b = .GetBaseName(s)
e = .GetExtensionName(s)
End With
Set fso = Nothing

Debug.Print s
Debug.Print b, e
Debug.Print
End Sub

--
Ciao! :)
Maurizio

Does VBA have built-in functions for extracting just the filename
from a path string? It would also be nice if I could extract the
filename minus the Windows extension. For example, I
might have this string:

"C:\tmp\files\mydata\yearly.docx"

I would need to extract the "yearly" portion. Extracting the whole
filename of "yearly.docx" would be useful too.

I'd go with Claus' 2nd reply because those fit your request for
'built-in functions'! I'd just add...

Dim vTmp
vTmp = Split(ThisWorkbook.Name, ".")

...so vTmp(0) contains the filename and vTmp(1) contains the extension.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

I'd go with Claus' 2nd reply because those fit your request for 'built-in
functions'! I'd just add...

Dim vTmp
vTmp = Split(ThisWorkbook.Name, ".")

..so vTmp(0) contains the filename and vTmp(1) contains the extension.


Thank you everyone for your help. Having multiple different solutions
was exactly what I preferred.