Remove DateTimestamp from String

[Steve]

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

 

[Peter T]

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" !

 

[Rick Rothstein]

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 Rothstein]

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 Rothstein]

Here is the solution for any extension...

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

 

[Peter T]

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]

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.