Change names of files in a folder to match names in Excel Column

[saybut]

Hi,

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

In column a in excel I have a list of the current file names and i
column b I have a corresponding list of what I need the file name
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 writ
short macros, and have discovered a lot by recording a new macro fo
various events and looking at the code. At the moment thought I can'
really deal with files and file names.

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

Regards,

Mark

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

sPath = "C:\Myfolder\"
for each cell in Range(Cells(1,2),Cells(1,2).End(xldown)
name sPath & cell as sPath & cell.Offset(0,1)
Nextd

Would be a start.

 

[saybut]

Hi Bob, thanks very much for showing me that. it seems to work perfect
the only problem I have is that it doesnt seem to know when to finis
so I get a Run-time error '58' File Already exsists. It appears that i
can't rename the last file for some reason.

I'mm sure if I play about with it enough I should be able to figure i
out.

Thanks a lot for the help.

Regards,

Mark

 

[Tom Ogilvy]

Probably because you have a name conflict and it is finding the wrong file
or you have a duplicate name in your data.

If it is the first thing, the simple code I gave you would not have that
problem. If the latter, then you need to clean up your data.