removing the file extension while listing files via foundfiles

[Jon]

I am trying to list a group of files using foundfiles. I am interested
only in the file name, not the path ofr the extension. I can remove
the path by using the split function and a "\" as a delimiter. I then
take the upper bound of this to get "filename.ext". How can I get the
file name alone? I cannot use "." as a delimiter because some of my
files have "." in their file name. (eg. house.pic.summer.jpeg) Anyone
have any ideas?

 

[Helmut Weber]

Hi Jon,

like this:

Dim s As String
s = "house.pic.summer.jpeg"
s = Left(s, InStrRev(s, ".") - 1)
MsgBox s

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Guest]

Try:

Function FileNameOnly(FileName As String) As String
Dim x As Integer, y As Integer
x = InStrRev(FileName, "\")
y = InStrRev(FileName, ".")
FileNameOnly = Mid(FileName, x + 1, y - x - 1)
End Function

Sub TestFileNameOnly()
Dim FNm As String
FNm = "C:\My Documents\Jon\house.pic.summer.jpeg"
MsgBox FileNameOnly(FNm)
End Sub

Regards,
Greg

 

[Jon]

Helmut,

Thanks, that is exactly what I wanted. Much appreciated.

 

[Jon]

Help!

This method fails if the file does not contain an extension. I need to
put it in an if statement and test if the file has a "." in it. How
can I do this?

 

[Chip Pearson]

Jon,

Dim Pos As Long
Dim S As String
S = "abc.def"
Pos = InStr(1, S, ".", vbTextCompare)
If Pos > 0 Then
Debug.Print "found"
Else
Debug.Print "not found"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jon]

That did it. Thank you Chip.

 

[Guest]

Jon,

I suggest you try this and forget using the Split function. You should stop
putting periods in your file name. This function will return the file name
only when the entire path and name is passed to it. If there is no extension
then it won't error. I am assuming that there are no instances where you have
no extension but still have periods in the file name. See the
"TestFileNameOnly" routine in my first post for how to use the function.

Function FileNameOnly(FileName As String) As String
Dim x As Integer, y As Integer
x = InStrRev(FileName, "\")
y = InStrRev(FileName, ".")
y = IIf(y = 0, Len(FileName) + 1, y)
FileNameOnly = Mid(FileName, x + 1, y - x - 1)
End Function

Regards,
Greg