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)
Bookmarks