Macro to change files names from a list in Excel 1

[richiwatts]

I have an Excel sheet with a list of files with full path

a1 = C:\Documents and Settings\rich\My Documents\test1.doc
a2 = C:\Documents and Settings\rich\My Documents\new\test1.gif
a3 = C:\Documents and Settings\rich\My Documents\new\people.gif

...and so on. I have 600 rows like this and I need to change the file names by adding DONOT_ in front of all the file name.

So "test1.doc" would become "DONOT_test1.doc"

Could this be done with a Macro? If so does anyone have a macro to do this

Rich

 

[PHV]

What have you tried so far and where in your code are you stuck ?
Have a look at the Left, InStrRev and Mid functions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

At your yesterday thread707-1426221, change the Kill statement inside the If ... End If block with the Name.

 

[WinblowsME]

Sub Change_File_Names()
   Dim i As Integer, full_path As String, dir_name As String, file_name As String
   
   For i = 1 To 600
      full_path = Trim(Cells(i, 1))
      
      If full_path = "" Then Exit For
      
      If FileExists(full_path) Then
         dir_name = Left(full_path, InStrRev(full_path, "\") - 1)
         file_name = Mid(full_path, InStrRev(full_path, "\") + 1)
      
         Name full_path As dir_name & "\DONOT_" & file_name
      Else
         Cells(i, 2) = "file not found"
      End If
   Next i
End Sub

Function FileExists(ByVal FName As String) As Boolean
   FileExists = (Dir(FName) <> "")
End Function