Change name in Save As?
 

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

Change name in Save As?
 

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

"Bob Phillips" wrote in message
...
Ed,

How about this? It does depend on all files being MMDDYY, not MDYY or
whatever.

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
.Name = Mid(.Name, 5, 2) & Left(.Name, 4) & ".xls"
End If
End If
End With
Next
End If

End Sub

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Ed" wrote in message
...
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

Change name in Save As?
 

), 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


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Ed" wrote in message
...
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

"Bob Phillips" wrote in message
...
Ed,

How about this? It does depend on all files being MMDDYY, not MDYY or
whatever.

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
.Name = Mid(.Name, 5, 2) & Left(.Name, 4) &
".xls"
End If
End If
End With
Next
End If

End Sub

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Ed" wrote in message
...
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

Change name in Save As?
 

Bob:

This is running well. Thanks so much!

Ed


"Bob Phillips" wrote in message
...
), 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


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Ed" wrote in message
...
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

"Bob Phillips" wrote in message
...
Ed,

How about this? It does depend on all files being MMDDYY, not MDYY or
whatever.

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
.Name = Mid(.Name, 5, 2) & Left(.Name, 4) &
".xls"
End If
End If
End With
Next
End If

End Sub

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Ed" wrote in message
...
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