Change name in Save As?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I've just been given a set of about 330 Excel files, all saved as MMDDYY.
That sorts great - until you change the year! What I really have is a
jumble of MMDD01, MMDD02, MMDD03 all the way down! Is there a way to rename
each file to Save As YYMMDD? Without having to do it 330 times?

Ed
 
Bob:

Copied code into new module in Personal.XLS. When ran, got "Run-time error
'58': file already exists" on this line:
..Name = Mid(.Name, 5, 2) & Left(.Name, 4) & ".xls"

What did I do wrong?

Ed
 
), Ed,

You didn't do anything wrong, just an obvious possibility with the code I
gave you.

Take as an example, a file of 09 Feb 2003. It would be named
090203 in your previous naming format. The m acro will change it to 030902,
but if you already have a file for 03 Sep 2002, that will already be called
030902, hence the rename fails. Unforytunately I have no way of knowing
which is DD or MM or YY.

Easiest way is to copy the files already done off to another directory (so
they don't get renamed again), and use this modified code. It will leave
some unchanges, but if you again copy the modified ones off and away, you
can just re-run on the remainder.

Sorry I missed the obvious.


Sub RenameDates()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set objFolder = objFSO.GetFolder("c:\MyTest\")
If Not objFolder Is Nothing Then
On Error GoTo 0
For Each objFile In objFolder.Files
With objFile
If .Type = "Microsoft Excel Worksheet" Then
If IsNumeric(Left(.Name, InStr(.Name, ".") - 1)) Then
On Error Resume Next
.Name = Mid(.Name, 5, 2) & Left(.Name, 4) & ".xls"
On Error GoTo 0
End If
End If
End With
Next
End If

End Sub
 
Back
Top