Results 1 to 7 of 7

user form used to rename files repeatedly adding prefix to file name

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-23-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2013
    Posts
    116

    user form used to rename files repeatedly adding prefix to file name

    UPDATE:
    The macro seems to fail when there is a large number of files to rename (more than ~50)

    Hello,

    I have a user form that adds prefixes to file names and also has the option to subtract text from file names. This is a userform that I downloaded from a excel vba forum, and adapted to add the subtract text functionality. The userform works fine on my computer and one other computer, but the prefix function does something strange on the 3rd computer I tested this on:

    The prefix that is input repeatedly is added to the beginning of the file name. So, if I wanted to add JPM_ as a prefix, the file name would return something like JPM_JPM_JPM_JPM_JPM_JPM_JPM_JPM_JPM_file.jpg (it eventually gives an error and stops- not before adding the long prefix to the file)

    Below is the code, and attached is the workbook with userform.

    I believe what is happening is the Loop based off of tmpDIR under the "rename_files" sub is not ending and is for some reason repeating until it breaks. I was capable of adapting the code to my needs, but am not really capable of re-writing what I have , and so this problem has me stymied. Can anyone help me fix this?

    
    Private curFolder As String 'current folder
    
    Private Sub CommandButton1_Click()
    
    If filelist.ListCount > 0 Then
    prefix = InputBox("Prefix: ")
      rename_files curFolder, prefix ' rename initializing
    Else
     MsgBox "There is nothing to rename, select any folder with files"
    End If
    End Sub
    
    Private Sub CommandButton2_Click()
    read_dir
    End Sub
    
    Private Sub CommandButton3_Click()
    If filelist.ListCount > 0 Then
    subtract = InputBox("Text to remove from file name:")
        replace_files curFolder, subtract
    Else
        MsgBox "There is nothing to rename, select any folder with files"
    End If
    End Sub
    
    Private Sub dirlist_Click()
    If dirlist.ListIndex <> -1 Then 'check if any directory is seleceted
      curFolder = ThisWorkbook.Path & "\" & dirlist.List(dirlist.ListIndex) & "\"
      show_files
    End If
    End Sub
    
    Private Sub UserForm_Initialize()
    read_dir
    End Sub
    
    Sub read_dir()
    dirlist.Clear
    filelist.Clear
    
    cPath = ThisWorkbook.Path & "\"
    tmpDIR = Dir(cPath, vbDirectory)     ' Retrieve the first entry.
    Do While tmpDIR <> ""    ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        If tmpDIR <> "." And tmpDIR <> ".." Then
            ' Use bitwise comparison to make sure tmpDir is a directory.
            If (GetAttr(cPath & tmpDIR) And vbDirectory) = vbDirectory Then
                dirlist.AddItem tmpDIR     ' Display entry only if it
            End If    ' it represents a directory.
        End If
        tmpDIR = Dir    ' Get next entry.
    Loop
    End Sub
    
    Sub show_files()
    filelist.Clear
    
    cPath = curFolder
    tmpDIR = Dir(cPath, vbDirectory)     ' Retrieve the first entry.
    Do While tmpDIR <> ""    ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        If tmpDIR <> "." And tmpDIR <> ".." Then
            ' Use bitwise comparison to make sure tmpDir is not a directory.
            If (GetAttr(cPath & tmpDIR) And vbDirectory) <> vbDirectory Then
                filelist.AddItem tmpDIR
            End If
        End If
        tmpDIR = Dir    ' Get next entry.
    Loop
    End Sub
    
    Sub rename_files(ByVal cPath As String, ByVal prefix As String)
    Dim OldName, NewName
    tmpDIR = Dir(cPath, vbDirectory)     ' Retrieve the first entry.
    Do While tmpDIR <> ""    ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        If tmpDIR <> "." And tmpDIR <> ".." Then
            ' Use bitwise comparison to make sure tmpDir is a directory.
            If (GetAttr(cPath & tmpDIR) And vbDirectory) <> vbDirectory Then
            
            'rename
            OldName = cPath & tmpDIR: NewName = cPath & prefix & "" & tmpDIR    ' Define file names.
            Name OldName As NewName    ' Rename file.
    
            End If
        End If
        tmpDIR = Dir    ' Get next entry.
    Loop
    
    show_files
    End Sub
    
    Sub replace_files(ByVal cPath As String, ByVal subtract As String)
    Dim OldName, NewName
    tmpDIR = Dir(cPath, vbDirectory)     ' Retrieve the first entry.
    Do While tmpDIR <> ""    ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        If tmpDIR <> "." And tmpDIR <> ".." Then
            ' Use bitwise comparison to make sure tmpDir is a directory.
            If (GetAttr(cPath & tmpDIR) And vbDirectory) <> vbDirectory Then
            
            'rename
            OldName = cPath & tmpDIR
            NewName = cPath & Replace(tmpDIR, subtract, "")
            Name OldName As NewName    ' Rename file.
    
            End If
        End If
        tmpDIR = Dir    ' Get next entry.
    Loop
    
    show_files
    End Sub
    test.xlsm

    Note that all three computers are running the same version of excel (2013)
    Last edited by mcdermott2; 07-09-2014 at 06:34 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rename cell based on file name for multiple files
    By lmolokin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2012, 05:49 PM
  2. Replies: 0
    Last Post: 08-02-2012, 03:14 PM
  3. How Can The Macro Rename Files When The Original File Name Changes Everday
    By np123345 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2008, 06:59 PM
  4. [SOLVED] How do you rename Multiple files using text from a cell in the file?
    By RandyR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-16-2005, 03:05 PM
  5. [SOLVED] Adding text file to a user form?
    By Chip in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-29-2005, 03:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1