VBA: Use Excel to rename files in a folder

Question

I’m thinking of using Visual Basic from Excel to standardize the name of 6000 files in a given folder. These files are mixed between photos and documents, so there are a variety of extensions (DOC, DOCX, PDF, TIF, TIFF, JPG, Jpgem BMP, PNG, MSG...).

My intention would be to go through the folder and make all the file names uppercase and the extension lowercase, as in the example:

• paramentro contabil.PDF will be renamed as PARAMETRO CONTABIL.pdf
• OPPO - finacials.tif will be renamed as OPPO - FINANCIALS.tif

I imagine I’d have to use a loop For Each Next to run the code, but do not know how to resize the variables and if there is any special function to rename files outside Excel.

What I’m trying to do is possible?

Answer 1

Below follows code that applies what you want to all files of a given folder, regardless of the extension, bypassing 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 to defining how the new name will look is this:

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

In case you want to modify how the file should be enough to manipulate this line. For example, if you want everything to be uppercase, write folder NewName = UCase(MyFile). And, as added by vc in the comments, this code only uses the VBA to be executed but does not even use Excel itself.

Answer 2

Assuming:

• One file per line
• Only one point in the file name (the point separating the extension from the file name)

---

Turn the following macro to list all the files in a directory in the cells in the A column of the sheet.

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 filenames:

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:

Answer 3

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

Ai just use variables and adapt to your code.