Rename a "Batch" of Files

Renaming files can be a tedious undertaking. In this Microsoft Word Tips & Microsoft Word Help page I have put together a basic VBA procedure that make this a far simpler task.

A prerequisite for renaming multiple files (i.e., a batch) using a VBA procedure is to put them all together in one place. In the example below, the place used is: "C:\Batch Folder"

Note: You can create a similar folder on your PC or change the path defined in the VBA procedure to match and existing folder on your PC.

Let's start with a batch of files that began with a meaningful name, but over time the naming convention became less than useful. For example, every day I created a Mission Log document. I started saving the files as Mission Log 1, Mission Log 2, etc. Ok, not very smart, but let's say that this is what I did anyway.

It won't take long for this naming convention to break down. If the boss asks for the Mission Log for a particular date I have a search on my hands.

It would be better if I renamed each Mission Log with its corresponding date. Fortunately each Mission Log document starts as shown below:

Using the existing document content, I can do this quickly by executing the following VBA procedure and entering "6" when prompted:

VBA Script:

Copy to clipboard

Option Explicit
Sub BacthFileRenamerFirstWords()
Dim strFile As String
Dim strPathtoUse As String
Dim lngWordToUse As Long
Dim lngCounter As Long
Dim oDoc As Document
Dim strNewName As String
Dim strOldName As String
Dim strExt As String
Dim oRng As Range
Dim i As Long
Dim arrDirectroryList() As String
  i = 1
  'Create a dynamic array variable, and then declare its initial size
  ReDim arrDirectroryList(1000) '1000 is arbitrary, but make is large enough to account for the number of batch files.
  lngWordToUse = InputBox("Enter the number of first words to use" _
                 & " for file naming. Maximum is 9.")
  'Specify folder containing files
  strPathtoUse = "C:\Batch Folder\"
  'Identify all of the files of *.doc type in the directory by using Dir$ function
  strFile = Dir$(strPathtoUse & "*.doc")
  'For each file found add to the array
  Do While strFile <> ""
    arrDirectroryList(lngCounter) = strFile
    'Get the next file name
    strFile = Dir$
    lngCounter = lngCounter + 1
  Loop
  'Reset the size of the array without losing its values by using Redim Preserve
  ReDim Preserve arrDirectroryList(lngCounter - 1)
  Application.ScreenUpdating = False
  For lngCounter = 0 To UBound(arrDirectroryList)
    Set oDoc = Documents.Open(FileName:=strPathtoUse & arrDirectroryList(lngCounter), _
    Visible:=False)
    With oDoc
      strOldName = .FullName
      'Call function to get extention
      strExt = GetExtension(.FullName)
      Set oRng = .Words(1)
      'Uses up to first nine words for file name
      On Error GoTo Handler
      oRng.End = .Words(lngWordToUse).End
ReEnter:
      strNewName = Trim(oRng.Text)
      strNewName = Replace(strNewName, "\", "")
      strNewName = Replace(strNewName, ":", "")
      strNewName = Replace(strNewName, """", "")
      strNewName = Replace(strNewName, vbCr, "")
      strNewName = Replace(strNewName, vbTab, "")
      .Close SaveChanges:=wdSaveChanges
    End With
    'ReName the file
    On Error GoTo Handler2
    Name strOldName As strPathtoUse & strNewName & "." & strExt
  Next lngCounter
  Application.ScreenUpdating = True
  Exit Sub
Handler:
  oRng.End = oDoc.Words(oDoc.Words.Count - 1).End
  Resume ReEnter
Handler2:
  Name strOldName As strPathtoUse & strNewName & i & ".doc"
  i = i + 1
  Resume Next
End Sub
Function GetExtension(ByRef strFileName As String) As String
Dim arrStrings() As String
  arrStrings() = Split(strFileName, ".")
  If UBound(arrStrings) > 0 Then
    GetExtension = arrStrings(UBound(arrStrings))
  End If
lbl_Exit:
  Exit Function
End Function

See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.

The result after running the procedure is a much more useful:

Now I can easily find the file for a specific date.

If you study the code , you may find ways you can adapt the code to develop additional renaming schemes.

That's it! I hope you have found this tips page useful and informative.

	Enter your search terms Submit search form
	Web gregmaxey.com

Rename a "Batch" of Files(A Microsoft Word Help & Tip page by Gregory K. Maxey)

Rename a "Batch" of Files
(A Microsoft Word Help & Tip page by Gregory K. Maxey)