VBA: Use Excel to rename files in a folder

Below is a code that applies what you want to all files in a certain folder, regardless of extension, ignoring subfolders:

Sub Renomear()

'Dimensionar variáveis
    Dim MyFolder, MyFile, NewName As String, i As Integer
        MyFolder = "H:\My Documents\Certificates\"
        MyFile = Dir(MyFolder & "*.*")

'Passar por cada arquivo na pasta e executar ação
    Do While MyFile <> ""
        i = InStr(1, MyFile, ".", 1)
        NewName = UCase(Left(MyFile, i)) & LCase(Right(MyFile, Len(MyFile) - i))
        Name MyFolder & MyFile As MyFolder & NewName
        MyFile = Dir
        Loop

End Sub

The key line to define how the new name will look is this:

NewName = UCase(Left(MyFile, i)) & LCase(Right(MyFile, Len(MyFile) - i))

If you want to change how the file should look, just manipulate this line. For example, if you want everything to be in uppercase, folder type NewName = UCase(MyFile) . And, as added by you in the comments, this code only uses VBA to run but it does not even use Excel itself.

Assuming that:

• One file per line
• Only one point in the file name (the point that separates the file name extension)

Rotate the following macro to list all files in a directory in cells in column A of the worksheet.

Code1:

Sub lista_arquivos()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer

    'cria uma instancia do FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'pega o objeto
    Set objFolder = objFSO.GetFolder("C:\Users\User-1\Desktop\")
    i = 1

    'loop em cada arquivo da pasta
    For Each objFile In objFolder.Files

        'salva nome do arquivo na linha i+1, coluna A
        Cells(i + 1, 1) = objFile.Name

        i = i + 1

    Next objFile

End Sub

And after that, run the following macro to change the name of the files:

Code2:

Sub renomear_arqs()

    'para todas as celulas não vazias da coluna A, faça o seguinte:
    For i = 1 To Range("A:A").End(xlDown).Row
        'pega nome original do arquivo
        arq_antigo = ActiveSheet.Cells(i, 1)

        'procura ponto no nome do arquivo
        pos_ponto = WorksheetFunction.Find(".", arq_antigo)

        'coloca maiusculo tudo à esquerda do ponto
        esq_arq_novo = UCase(Left(arq_antigo, pos_ponto))

        'coloca minusculo tudo à direita do ponto
        dir_arq_novo = LCase(Right(arq_antigo, Len(arq_antigo) - pos_ponto))

        'une a parte a esquerda com a parte a direita
        arq_novo = esq_arq_novo + dir_arq_novo

        'cola o nome novo na mesma linha do arq em questão, na coluna B
        ActiveSheet.Cells(i, 2).Value = arq_novo

        'renomeia os arquivos
        Name arq_antigo As arq_novo

    'pula pra proxima linha, que deve conter o nome do outro arquivo..
    Next

End Sub

Image Example:

Name "Caminho\nome do arquivo.pdf" as "Caminho\nomedoarquivo.pdf"

Then just use variables and adapt to your code.