Results 1 to 2 of 2

Thread: Rename File Folders

  1. #1

    Rename File Folders

    Firstly thank you for taking the time to read. Using the following formula folders were automatically created in windows explorer:

    Sub MakeFolders() 
        Dim Rng As Range 
        Dim maxRows, maxCols, r, c As Integer 
        Set Rng = Selection 
        maxRows = Rng.Rows.Count 
        maxCols = Rng.Columns.Count 
        For c = 1 To maxCols 
            r = 1 
            Do While r <= maxRows 
                If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then 
                    MkDir (ActiveWorkbook.Path & "\" & Rng(r, c)) 
                    On Error Resume Next 
                End If 
                r = r + 1 
        Next c 
    End Sub 
    Formatting tags added by mark007
    Unfortunately when this was first run it created the folders with employee surname first then employee number e.g. SMITH 01234567. There are numerous files in each folder of which there are over 500 folders. Is there an easy way to rename these folders so the employee number comes first e.g. 01234567 SMITH?

  2. #2
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    May 2004
    This should work. Check the output in the debug first, before uncommenting Name line
    Sub Test() 
        pth = "F:\" 'Change to suit
        Dim fso, f 
        Set fso = CreateObject("Scripting.FileSystemObject") 
        Set fld = fso.GetFolder(pth) 
        For Each f In fld.subfolders 
            x = Split(f.Name) 
            If UBound(x) = 1 Then 
                If IsNumeric(x(1)) Then 
                    OldFolderName = pth & f.Name 
                    NewFolderName = pth & x(1) & " " & x(0) 
                    Debug.Print NewFolderName 
                     'Name OldFolderName As NewFolderName
                End If 
            End If 
    End Sub 
    Formatting tags added by mark007
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts