removing the file extension while listing files via foundfiles
 

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?

removing the file extension while listing files via foundfiles
 

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"

removing the file extension while listing files via foundfiles
 

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" wrote:

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?

removing the file extension while listing files via foundfiles
 

Helmut,

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

removing the file extension while listing files via foundfiles
 

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?

removing the file extension while listing files via foundfiles
 

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" wrote in message
oups.com...
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?

removing the file extension while listing files via foundfiles
 

That did it. Thank you Chip.

removing the file extension while listing files via foundfiles
 

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

"Jon" wrote:

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?