Rename PDF file [closed]

Question

Rename PDF file [closed]

Navigation

#1 by (2 votes)

1

Good Afternoon Community,

I'm 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 ... Type get the PDFs in the folder then rename them accordingly my spreadsheet does anyone know how to do this I did several searches and so far I have not achieved anything.

Thanks for the help anyway

It is a bit tense to practice language change since I study Java and started doing VBA. I did what I knew, but I got a lot of search data on google.

In my code, I am having to inform the name of the old and new files, but I would not have a way to just put the new one, and another one I do not know how to leave the files with .pdf ... when I execute the code I have to put manual.

Sorry for the questions ... I do not have much contact with VBA.

I thank you very 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

pdf vba excel-vba

asked by anonymous 27.07.2017 / 18:55

1 answer

Laravel SQL returning with interrogation? Form validation in select with angular

score 2 · Answer 1

See if it suits you, I've made minor adjustments to your formula where I put comments.

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

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 have adapted the function above your code, choosing the folder at the time of executing the routine, but of course you can leave the folder by default as you did.

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

In "C1":

"novo_nome_do_arquivo"

In column "B" (having 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 just identifies the last occurrence of "\" (backslash) of column "A" and takes all the string , which is the path where the file is, plus the default name defined in "C1" plus the extension ".pdf".

And finally rename 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: link

Follow the image of the worksheet to see how it was done here:

I hope I have helped!