All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

This assumes a 4-digit year, a "-" following the year, and removal of all
text to the right of the year.

Sub test()
Dim Mystr As String

Mystr = "Change 69231 Ticket2008-10-01 14.48.18.953.xls"
Mystr = Left(Mystr, InStr(Mystr, "-") - 5) & ".xls"
End Sub

Mike F
"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

Following will rename files that include "yyyy-" in the filename where is a
year in the loop, eg inlcudes "2008-".

Sub RenameFiles()
Dim i As Long, j As Long
Dim cnt As Long, pos As Long
Dim sPath As String, col As Collection
' be sure to close any "date" named files before running
Set col = New Collection

sPath = Application.DefaultFilePath ' << change to your path

If Right$(sPath, 1) < "\" Then sPath = sPath & "\"
cnt = FilesToCol(sPath, col)
If cnt Then

For i = 1995 To 2012 ' << change to potential years to cater for
For j = 1 To col.Count
pos = InStr(2, col(j), i & "-")
If pos Then
Name sPath & col(j) As sPath & Left$(col(j), pos - 1) &
".xls"
End If
Next
Next
End If
End Sub

Function FilesToCol(sPath As String, c As Collection) As Long
Dim sFile As String
Call Dir("nul")
sFile = Dir(sPath & "*.xls")
Do While Len(sFile)
c.Add sFile
sFile = Dir()
Loop
FilesToCol = c.Count
End Function

Regards,
Peter T

PS this is untested, best read "will" as "should" !


"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

Not sure of the OP's naming system, but you could get a false hit with a
name like this...

Mystr = "Change 69231-A Ticket2008-10-01 14.48.18.953.xls"

--
Rick (MVP - Excel)


"Mike Fogleman" wrote in message
...
This assumes a 4-digit year, a "-" following the year, and removal of all
text to the right of the year.

Sub test()
Dim Mystr As String

Mystr = "Change 69231 Ticket2008-10-01 14.48.18.953.xls"
Mystr = Left(Mystr, InStr(Mystr, "-") - 5) & ".xls"
End Sub

Mike F
"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

Assuming that the structure of the date portion of the filename is this
*fixed* pattern..

yyyy-mm-dd hh.mm.ss.sss

then, assuming we are talking about .xls files, this should work...

If FileName Like "*####-##-## ##.##.##.###.xls" Then
FileName = Left(FileName, Len(FileName) - 27) & ".xls"
End If

--
Rick (MVP - Excel)


"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

For any file extension use:

Mystr = Left(Mystr, InStr(Mystr, "-") - 5) & _
Right(Mystr, Len(Mystr) - InStrRev(Mystr, ".") + 1)

Mike F
"Mike Fogleman" wrote in message
...
This assumes a 4-digit year, a "-" following the year, and removal of all
text to the right of the year.

Sub test()
Dim Mystr As String

Mystr = "Change 69231 Ticket2008-10-01 14.48.18.953.xls"
Mystr = Left(Mystr, InStr(Mystr, "-") - 5) & ".xls"
End Sub

Mike F
"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

Here is the solution for any extension...

If FileName Like "*####-##-## ##.##.##.###.*" Then
Filename = Left(Filename, InStrRev(Filename, ".") - 24) & _
Mid(Filename, InStrRev(Filename, "."))
End If

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming that the structure of the date portion of the filename is this
*fixed* pattern..

yyyy-mm-dd hh.mm.ss.sss

then, assuming we are talking about .xls files, this should work...

If FileName Like "*####-##-## ##.##.##.###.xls" Then
FileName = Left(FileName, Len(FileName) - 27) & ".xls"
End If

--
Rick (MVP - Excel)


"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

The OP might want to verify his datestamps always include seconds to 3dp
before using this excellent suggestion, that is if indeed the OP has seen
any of these suggestions.

Regards,
Peter T


"Rick Rothstein" wrote in message
...
Here is the solution for any extension...

If FileName Like "*####-##-## ##.##.##.###.*" Then
Filename = Left(Filename, InStrRev(Filename, ".") - 24) & _
Mid(Filename, InStrRev(Filename, "."))
End If

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming that the structure of the date portion of the filename is this
*fixed* pattern..

yyyy-mm-dd hh.mm.ss.sss

then, assuming we are talking about .xls files, this should work...

If FileName Like "*####-##-## ##.##.##.###.xls" Then
FileName = Left(FileName, Len(FileName) - 27) & ".xls"
End If

--
Rick (MVP - Excel)


"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve

If the number of decimal places in the seconds can vary (that is, they are
not fixed at 3 decimal places), then something like this would be needed...

For X = 1 To Len(FN)
If Mid(FN, X) Like "####-##-## ##.##.##*" Then
FN = Left(FN, X - 1) & Mid(FN, InStrRev(FN, "."))
Exit For
End If
Next

where I changed the variable name from FileName to FN to make sure one of
the longer lines wouldn't word wrap in the newsgroup reader.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
The OP might want to verify his datestamps always include seconds to 3dp
before using this excellent suggestion, that is if indeed the OP has seen
any of these suggestions.

Regards,
Peter T


"Rick Rothstein" wrote in message
...
Here is the solution for any extension...

If FileName Like "*####-##-## ##.##.##.###.*" Then
Filename = Left(Filename, InStrRev(Filename, ".") - 24) & _
Mid(Filename, InStrRev(Filename, "."))
End If

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming that the structure of the date portion of the filename is this
*fixed* pattern..

yyyy-mm-dd hh.mm.ss.sss

then, assuming we are talking about .xls files, this should work...

If FileName Like "*####-##-## ##.##.##.###.xls" Then
FileName = Left(FileName, Len(FileName) - 27) & ".xls"
End If

--
Rick (MVP - Excel)


"Steve" wrote in message
...
All,
I'm working on a macro that works with the name of files in a certain
directory.

My problem is that some of the files may have a datetimestamp within
the filename.

example: "Change 69231 Ticket2008-10-01 14.48.18.953.xls"

Would like to change to "Change 69231 Ticket.xls"


How can I find and delete the timestamp within a string like the
above.

Steve