Rename PDF File

Question

Boa Tarde Comunidade,

I am trying to create a macro that renames 125 PDF files that are in a specific folder C:... I want it to replace the name that is in my column B... Like to get the PDF in the folder then rename them as my spreadsheet someone knows how to do this I did several searches and so far I got nothing.

---

Thanks for the help

It is a little tense the practice of language change since I study Java and started doing VBA. I did what I knew, but I got enough data from google searches.

In my code, I’m having to inform the name of the old and new file, but I wouldn’t have a way to just put the new one, and another I don’t know how to leave the files with .pdf... when I run the code I have to put manual.

Sorry for the questions... I don’t have much contact with VBA.

Thank you so much for helping me.

Sub RenameFiles()

Dim xDir As String
Dim xFile As String
Dim xRow As Long
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
If .Show = -1 Then
    xDir = .SelectedItems(1)
    xFile = Dir(xDir & Application.PathSeparator & "*")
    Do Until xFile = ""
        xRow = 0
        On Error Resume Next
        xRow = Application.Match(xFile, Range("A:A"), 0)
        If xRow > 0 Then
            Name xDir & Application.PathSeparator & xFile As _
            xDir & Application.PathSeparator & Cells(xRow, "B").Value
        End If
        xFile = Dir
    Loop
End If
End With
End Sub

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\Users\AnaWill\Desktop\Holerites Folha\Nova pasta"
MyFile = Dir(MyFolder & "\*.*")
a = 0
Do While MyFile <> ""
    a = a + 1
    Cells(a, 2).Value = MyFile
    MyFile = Dir
Loop
End Sub

Answer 1

See if the way you are, I made small adjustments in your formula where I put comment.

To list the files of a particular folder in column "A":

Sub listarArquivos()

Dim MyFolder As String
Dim MyFile As String
Dim a As Integer
Dim dialogFile As Integer

    ' Limpar coluna
    Columns("A:A").Select
    Selection.ClearContents

    ' Busca pasta onde estão os arquivos
    Application.FileDialog(msoFileDialogFolderPicker).ButtonName _
    = "Selecionar pasta"
    dialogFile = Application.FileDialog(msoFileDialogFolderPicker).Show

    ' Verifica se foi selecionado
    If dialogFile <> 0 Then
        MyFolder =Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
    Else
        MsgBox "Pasta não selecionada", vbInformation, "Aviso!"
        Exit Sub
    End If

    MyFile = Dir(MyFolder & "\*.pdf")
    a = 0
    Do While MyFile <> ""
        a = a + 1
        Cells(a, 1).Value = MyFolder & "\" & MyFile
        MyFile = Dir
    Loop

End Sub

I adapted the function above your code by choosing the folder when performing the routine, but of course you can leave the folder by default as you did.

After listing the PDF files in column "A", I put the new names pattern for the files in cell "C1" and the following formula in column "B":

In "C1":

"novo_nome_do_arquivo"

In column "B" (with cell B1 as reference):

=SE(A1<>"";ESQUERDA(A1;PROCURAR("@";SUBSTITUIR(A1;"\";"@";(NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1;"\";"")))/NÚM.CARACT("\"))))&$C$1&LIN(A1)&".pdf";"")

This giant formula above only identifies the last occurrence of " (backslash) of column "A" and picks up the whole string, which is the path where the file is, plus the default name set in "C1" plus the extension ". pdf".

And finally renames the files that are in column "A" to the name defined in column "B":

Sub renomearArquivo()

Dim Row As Integer
Dim OldFile As String
Dim NewFile As String

    ' Somente o que estiver preenchido na coluna "A"
    For Row = 1 To Range("A" & Rows.Count).End(xlUp).Row
        OldFile = ActiveSheet.Cells(Row, 1)
        NewFile = ActiveSheet.Cells(Row, 2)

        ' Renomear Arquivo
        Name OldFile As NewFile

    Next
End Sub

Adapted from: https://stackoverflow.com/questions/7508605/renaming-files-with-excel-vba

Follow the spreadsheet picture to see how it looked here:

I hope I’ve helped!