Renaming Files: Take 2

[Dominique Feteau]

Sorry, I dont think I was too clear. Here's an easier explanation.

I have a folder with about 150 generic file names for ".tif" scanned images.

I have an excel sheet with 150 client names with dates and the ".tif"
extension on the end in column A and a list of the 150 generic files names
in column B.

I want a macro that will look at the rename each of those generic file names
in column B with the new one in column B.

Hope thats a little easier.

 

[Dominique Feteau]

I found this code, but it only seems to work on excel sheets. How can I
modify it so it works on any type of file.

Public Sub ReSave()

Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File

Application.DisplayAlerts = False

Set fso = New Scripting.FileSystemObject

'Change to refect correct path of source directory containing Excel files

Set fsDir = fso.GetFolder("C:\Source Directory")

For Each fsFile In fsDir.Files

Workbooks.Open Filename:= fsFile

'Use this line to save workbook with name equal to only the text in cell
"A1"
'Change Path to reflect correct save path

ActiveWorkbook.SaveAs "C:\Destination Directory\" & Range("A1").Value &
".xls"

'Use this line to save workbook with name equal to old name + text in cell
"A1"
'Change Path to reflect correct save path

ActiveWorkbook.SaveAs "C:\Destination Directory\" & _
Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xls") - 1) & " " &
Range("A1").Value

Next

End Sub

 

[Tom Ogilvy]

Sub D()
Dim sName As String
Dim sNewName As String

ChDir "c:\temp"
for each cell in Range("A1:A150")
sName = cell.offset(0,1).value
sNewName = cell.value
On Error GoTo NameAlreadyExists
Name sName As sNewName
On Error GoTo 0
Next
Exit Sub

NameAlreadyExists:
MsgBox "Cannot rename '" & sName & "' as '" & sNewName & "' already
exists"
Resume Next
End Sub

 

[Tom Ogilvy]

no real relation between opening a workbook and doing a saveas to rename it
and renaming an unopened .TIF file from excel. See answer to your original
post in this thread.

 

[Dominique Feteau]

Thanks a million Tom. Works likes a charm. Saved me a few hours a day. I
might have to do some actual work now.

Dominique