changing file names

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 __________________ Col B
NewLink __________________ LS03101
Pevion __________________ LS03102
Interpharm ___________________ LS03103