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

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

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

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

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/

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

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.

--
Regards,
Tom Ogilvy



"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/

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

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

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

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

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.

--
Regards,
Tom Ogilvy

"saybut " wrote in message
...
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 finish
so I get a Run-time error '58' File Already exsists. It appears that it
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 it
out.

Thanks a lot for the help.

Regards,

Mark.


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