Mark,

Try this little macro.

Put the existing names in one column, the new names in the next column.
Don't put file extensions.

Sub RenameMyData()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim c As Range
Dim sOld As String, sNew As String, sExt As String

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("c:\MyTest\")
For Each oFile In oFolder.Files
sOld = Left(oFile.Name, InStr(1, oFile.Name, ".") - 1)
sExt = Right(oFile.Name, Len(oFile.Name) - InStr(1, oFile.Name,
"."))
On Error Resume Next
Set c = Cells.Find(what:=sOld, LookIn:=xlValues)
On Error GoTo 0
If Not c Is Nothing Then
oFile.Name = c.Offset(0, 1).Value & "." & sExt
End If
Next
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"saybut " wrote in message
...
Hi,

I currently have a folder of word documents which have generic names
i.e. 040201-1126.doc, 040201-1127.doc and so on, but I need to change
the files names to match those on our system.

In column a in excel I have a list of the current file names and in
column b I have a corresponding list of what I need the file names
changed too. So from 040201-1126.doc need changed to OHEC1100.doc etc.

I have good experience with Excel, but not so much with VB. I can write
short macros, and have discovered a lot by recording a new macro for
various events and looking at the code. At the moment thought I can't
really deal with files and file names.

Any help would be greatly appreciated as it will save me lots of time
this way rather than editing the file names manually as we have 40 or
so new files coming out each week.

Regards,

Mark P


---
Message posted from http://www.ExcelForum.com/