Macro for Changing Files Names

[jerem]

I'd like to use the code below (with some modification help from you) to
alter the names of a bunch of files that exist in a folder, however I'd like
to be able (I'm thinking you can use a message box to gather information for
MyPath = "C:\", but don't exactly know how to do that) to get the varying
path names from the user by allowing them to navigate through Explorer and
once doing so have another Message Box come up asking what prefix do you want
to add to the previous existing name, then have all the filenames changed
(ignore this code below: NewName = Left(OldName, Len(OldName) - 4) & "A" & i
& ".xls"., what I need here is a statement that accomplishes adding the
prefix the user just typed in to the previously existing name -- so, if the
previously existing name was 1. Exhibits, and the user typed in Tab, then the
new name would be Tab 1. Exhibits and every other file in the folder would
get that same prefix (Tab) throughout the file list.

In a nutshell - I want to add a prefix to every file in the folder with the
help of user input (where the files reside and what is the prefix you want to
add to the filename). I hope this is not confusing. Thanks for your help.

Sub ChangeFileNames()

Dim fs, i As Integer, OldName As String, NewName As String, MyPath As String
Set fs = Application.FileSearch
MyPath = "C:\"
With fs
.NewSearch
.LookIn = MyPath
.SearchSubFolders = False
.FileName = "*.*"
.FileType = msoFileTypeAllFiles
.Execute

For i = 1 To .FoundFiles.Count
OldName = .FoundFiles(i)
NewName = Left(OldName, Len(OldName) - 4) & "A" & i & ".xls"
Name OldName As NewName
Next i
End With

End Sub

 

[Jay Freedman]

The code for a folder browser is at
http://www.word.mvps.org/FAQs/MacrosVBA/BrowsDialog.htm.

The Application.FileSearch object has been removed from VBA starting
with Office 2007, so you're better served to use the old Dir function
instead
(http://www.word.mvps.org/FAQs/MacrosVBA/ReadFilesIntoArray.htm).

Here's sample code:

Sub ChangeFileNames()
Dim MyPath As String
Dim OldName As String, NewName As String
Dim Prefix As String

Prefix = "abc"

MyPath = BrowseFolder
If Len(MyPath) > 0 Then
OldName = Dir$(MyPath & "*.*")
While Len(OldName) > 0
NewName = Prefix & OldName
Name OldName As NewName
OldName = Dir$()
Wend
End If
End Sub

Function BrowseFolder() As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show Then
BrowseFolder = .SelectedItems(1) & "\"
End If
End With
End Function

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[jerem]

Hey Jay,

Code works beautifully at home. Trying to use it at work and I'm getting a
runtime error 53 at the statement Name OldName As NewName. When I looked up
this error message it mentions something about not being able to find a file
- don't know what that's about. The folder is there (I'm able to select it),
the files are there, etc., etc. Know what's going on with this??

Thanks for your help.

 

[Jay Freedman]

I'm not quite sure what causes it to work in one place and not
another, but it seems to be related to having two or more files in
different (but nearby) folders with the same filename. The Name
statement seems to get confused about which folder it's supposed to
look at, because OldName and NewName are just filenames without paths.

To cure this, change the Name statement to this:

Name MyPath & OldName As MyPath & NewName

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[jerem]

Sweet! Fixed the problem. Was trying to tinker around with this but I can
be dangerous. Thanks for your help.

 

[jerem]

Jay,

I might have spoken too soon. After changing Name OldName As NewName to
Name MyPath & OldName As MyPath & NewName this worked for a few times. Now
the code has gone schizophrenic. Now it renames every file with 25 prefixes
(TabTabTabTab, etc. and then the old file name). Don't know what in the
world is going on with this???

 

[Jay Freedman]

Hm. Usually the Dir function seems to grab an internal list of the files
that are present when it first starts, and doesn't try to return the "new"
renamed files. But sometimes it seems to get lost or overloaded, and then it
just keeps going and going and... I wondered about that, but it seemed to
be behaving itself so I left it alone.

The permanent solution is to create an array of the filenames before any
renaming is done, and then only rename the files listed in the array. Try
this -- hey, I did say the original was "sample code"

Sub ChangeFileNames()
Dim MyPath As String, MyFilename As String
Dim OldName() As String, NewName As String
Dim Prefix As String
Dim i As Long

ReDim OldName(0)
Prefix = "abc"

MyPath = BrowseFolder
If Len(MyPath) > 0 Then
MyFilename = Dir$(MyPath & "*.*")
While Len(MyFilename) > 0
OldName(UBound(OldName)) = MyFilename
ReDim Preserve OldName(UBound(OldName) + 1)
MyFilename = Dir$()
Wend

For i = 0 To UBound(OldName) - 1
NewName = Prefix & OldName(i)
Name MyPath & OldName(i) As MyPath & NewName
Next i
End If
End Sub

Function BrowseFolder() As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show Then
BrowseFolder = .SelectedItems(1) & "\"
End If
End With
End Function

 

[jerem]

Yes, you did indeed say that it was sample code and I am grateful for sample
code, piecemeal code or any code I can get from you guys. When this
duplication was happening I would graze over New Name, Old Name, Dir$(), etc.
and see that the name was not being reset, however, I didn't know how to
correct that. Just FYI: what it sometimes takes for me to figure out what
each statement means for lines and lines of code is to copy that code, go
into Google or msdn, search the statement and hope something comes up that
enlightens me. This in and of itself can be quite time-consuming and a lot
of times I have to actually break down the statement word for word. Have you
ever looked up a word in the dictionary and read the definition and still not
get a good sense of what the word means? Well, that happens to me quite
frequently with code in Google and msdn so I just want to let you know it's
not for a lack of trying. And, again, I appreciate your help and patience
(because when grazing this site alone I have seen some responses from some
help individuals that can be more entertaining than watching cable TV) -- oh,
and the most important thing of all - this code seems to be working without a
hitch and it has saved me a tremendous amount of time. Thanks again.

 

[Jay Freedman]

You're welcome!

--
Jay

Yes, you did indeed say that it was sample code and I am grateful for sample
code, piecemeal code or any code I can get from you guys. When this
duplication was happening I would graze over New Name, Old Name, Dir$(), etc.
and see that the name was not being reset, however, I didn't know how to
correct that. Just FYI: what it sometimes takes for me to figure out what
each statement means for lines and lines of code is to copy that code, go
into Google or msdn, search the statement and hope something comes up that
enlightens me. This in and of itself can be quite time-consuming and a lot
of times I have to actually break down the statement word for word. Have you
ever looked up a word in the dictionary and read the definition and still not
get a good sense of what the word means? Well, that happens to me quite
frequently with code in Google and msdn so I just want to let you know it's
not for a lack of trying. And, again, I appreciate your help and patience
(because when grazing this site alone I have seen some responses from some
help individuals that can be more entertaining than watching cable TV) -- oh,
and the most important thing of all - this code seems to be working without a
hitch and it has saved me a tremendous amount of time. Thanks again.

 

[Karl E. Peterson]

The permanent solution is to create an array of the filenames before any
renaming is done, and then only rename the files listed in the array.

Repeated, for emphasis! Always, always, *always* do this in all but the very
simplest Dir loops, in particular if you're actually *doing* something to the files.
Gather the list. Operate on the list.