Rename files from a list in Excel

[PhoenixMM]

I really need help,

I need two Macros. On running the first one, asks the user where the folder is. Then it should list all the files from a folder and any files from sub folders within the main folder. It puts all the names in column A.

TestFile.pdf
TestFile2.pdf
TestFile.xsl
TestFile3.doc

The second Macro, Then looks in column B and renames the files to whats entered init.

TestRecords.pdf
TestReport.pdf
TestSheet.xsl
TestFile.doc

I am a newbie and have no idea what i need to do.

Please help

 

[Weaver]

This has now solved all my problems.

Thank you for all the help you have given me. Let me know if there is away i can say thanks.

Next time you see someone struggle with Excel, if you can, help them. If you can't, spread the word of this forum!

 

[Waqar]

hi

is it possible that a code read the pdf file and then change the file name?

for example in the pdf file heading in xyz so that file name should be xyz.pdf.

Regards

 

[Weaver]

hi

is it possible that a code read the pdf file and then change the file name?

for example in the pdf file heading in xyz so that file name should be xyz.pdf.

Regards

I think you would need some sort of file converter software to do this - Excel doesn't appear to open pdfs easily.

 

[Biz]

When you say "Newbie", just how much do you know? Hopefully this will be as simple as possible.

In the vb window, insert a new module and paste all of the following code

Option Compare Text
 
Sub macro1()
    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 macro2()
    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)
    f = Split(o, ".")
    s = f(UBound(f))
    If Right(n, Len(s)) = s Then
        checkSuffix = n
    Else
        checkSuffix = n & "." & s
    End If
End Function

Create a clean tab and run macro1. This will create your initial list. You can run the code by pressing alt +F8 and choosing the relevant macro, or you can create buttons and call the macros from these.

In column B, add your new file names. You can omit the suffix's (.doc, .xls etc) as the code will add these back in if they're not there or wrong.

The you can run macro2 and the files will be renamed. Best to test on copies of your files. Column C gets a comment which will say 'failed' if the filename you supplied already exists. Anything with a blank in B gets ignored.

HTH

Thanks m8 saved me boring task of renaming.

Biz