Sub GetFileList() Dim folderPath As String, nextFile As String, i As Long
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then
Range("A:C").ClearContents
Range("A1") = "Path:"
folderPath = .SelectedItems(1)
Range("B1") = folderPath
nextFile = Dir(folderPath & "\*.*")
i = 3
Do While nextFile <> ""
Cells(i, "A") = nextFile
i = i + 1
nextFile = Dir
Loop
Columns(1).EntireColumn.AutoFit
Else
MsgBox "No folder selected"
End If
End With
End Sub
Sub ChangeFileName()
Dim folderPath As String, nextFile As String, i As Long, lr As Long
Dim oldName As String, newName As String
folderPath = Range("B1").Value
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 3 To lr
oldName = Cells(i, "A")
newName = Cells(i, "B")
If newName <> "" Then
oldName = folderPath & "\" & oldName
newName = folderPath & "\" & checkSuffix(oldName, newName)
If Not fileExists(newName) Then
Name oldName As newName
Cells(i, "C") = "Complete"
Else
Cells(i, "C") = "Failed"
End If
End If
Next i
Shell "Explorer " & folderPath, vbNormalFocus
End Sub
Function fileExists(ByVal str As String) As Boolean
fileExists = (Dir(str) <> "")
End Function
Function checkSuffix(ByVal o As String, n As String)
Dim f, s
f = Split(o, ".")
s = f(UBound(f))
If Right(n, Len(s)) = s Then
checkSuffix = n
Else
checkSuffix = n & "." & s
End If
End Function