Hi, I was wondering if anyone can help?

A while ago someone was very helpful in answering a question for me on
here. I needed a macro which looked at the file names in column A and
changed them to the corresponding file name in column b.

The macro is a bit temperamental and now doesn't really seem to work at
all. I think its something to do with the way the names in column b are
presented i.e. using certain numbers or letters seems to mess it up.
(the problem is that excel claims the file name already exists after
changing about 10-15 names)

The code is below, if anyone has any idea on this it would be a great
help. I've put example file names below the code.

many thanks.



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:\TOCS\")
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


Eg File Name:

COL A [/b] [b] COL B
NewLink LS03101
Pevion LS03102
Interpharm LS03103


--
saybut
------------------------------------------------------------------------
saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949
View this thread: http://www.excelforum.com/showthread...hreadid=514423