Note: The other languages of the website are Google-translated. Back to English
Registo de cliente  \/ 
x
or
x
Registe-se  \/ 
x

or

Como renomear vários arquivos de uma pasta no Excel?

Pode ser que a maioria de nós sofra com este problema que precisamos renomear vários arquivos em uma pasta, renomear os nomes de arquivos um por um vai nos deixar loucos se houver centenas ou milhares de arquivos nessa pasta. Existem boas funções para lidarmos com essa tarefa?

Liste todos os nomes de arquivo de uma pasta específica na planilha com o kutools para Excel

Renomear vários arquivos de uma pasta no Excel com código VBA


Se houver vários arquivos que você deseja renomear, primeiro, você pode listar os nomes dos arquivos antigos em uma coluna da planilha e, em seguida, inserir os novos nomes de arquivo que deseja substituir. Para listar rapidamente todos os arquivos na planilha, você pode usar o Kutools for Excel'S Lista de nomes de arquivo utilidade.

Depois de instalar Kutools for Excel, faça o seguinte :( Baixe grátis Kutools para Excel agora )

1. Clique Kutools Plus > Importar e Exportar > Lista de nomes de arquivo, veja a captura de tela:

2. No Lista de nomes de arquivo caixa de diálogo, clique em doc-rename-multiple-files-1botão para escolher a pasta de arquivo que você deseja listar os arquivos e, em seguida, clique Todos os arquivos de Tipo de arquivo, veja a captura de tela:

doc-rename-multiple-files-1

3. Então clique OK botão, todos os nomes de arquivos foram listados em uma coluna de uma nova planilha, assim como alguns atributos de arquivo, e agora, você pode excluir outras colunas indesejadas e apenas deixar o Nome do arquivo coluna, veja a captura de tela:

doc-rename-multiple-files-1

Clique para saber mais sobre este recurso Lista de nomes de arquivos ...

Baixe grátis Kutools para Excel agora


Depois de listar todos os nomes de arquivos originais em uma coluna A, você deve digitar os novos nomes de arquivos na coluna B, conforme mostrado a seguir:

doc-rename-multiple-files-1

E aqui vou falar sobre um código VBA que pode ajudá-lo a substituir os nomes de arquivos antigos pelos novos de uma vez. Siga os seguintes passos:

1. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para janela de aplicativos.

2. Clique inserção > Móduloe cole a macro a seguir no Janela do módulo.

Código VBA: renomear vários arquivos em uma pasta

Sub RenameFiles()
'Updateby20141124
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

3. Depois de colar o código, pressione F5 chave para executar este código, e no procurar janela, selecione a pasta na qual deseja alterar os nomes de arquivo, consulte a imagem:

doc-rename-multiple-files-1

4. E, em seguida, clique em OK, todos os nomes de arquivos antigos foram substituídos pelos novos nomes de arquivos imediatamente. Veja as capturas de tela:

doc-rename-multiple-files-1 2 doc-rename-multiple-files-1

notas:

1. Quando você lista seus nomes de arquivo novos e antigos, a extensão do arquivo deve ser incluída.

2. No código acima, a referência A: A indica a lista de nomes de arquivos antigos que você deseja renomear e faz referência B contém os novos nomes de arquivo que você deseja usar, você pode alterá-los conforme sua necessidade


Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

Artigos relacionados:

Como criar planilhas de sequência a partir de um intervalo de células no Excel?

Como renomear várias planilhas no Excel?


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jeje · 4 months ago
    wow, useful and very helpful. BIG THANKS :)
  • To post as a guest, your comment is unpublished.
    amrul · 5 months ago
    Sub RenameMultipleFiles()
    With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = -1 Then
    selectDirectory = .SelectedItems(1)
    dFileList = Dir(selectDirectory & Application.PathSeparator & "*")

    Do Until dFileList = ""
    curRow = 0
    On Error Resume Next
    curRow = Application.Match(dFileList, Range("A:A"), 0)
    If curRow > 0 Then
    Name selectDirectory & Application.PathSeparator & dFileList As _
    selectDirectory & Application.PathSeparator & Cells(curRow, "B").Value
    End If

    dFileList = Dir
    Loop
    End If
    End With
    End Sub

    this formula does not rename1st document
  • To post as a guest, your comment is unpublished.
    Jaryn · 1 years ago
    Love this so much! Is there a way to use Kutools to edit metadata tags for music similar to this with filenames? Would love being able to mass edit song tags through Excel rather than on iTunes or other third party song tag editors. Thank you!
  • To post as a guest, your comment is unpublished.
    Adnansoft · 1 years ago
    okay Thank you

  • To post as a guest, your comment is unpublished.
    XYZ · 1 years ago
    Hello. I did this for a couple of files and it worked successfully. The next few files i tried, did not work. There were a few formulas added to the new names, could that be the issue?
  • To post as a guest, your comment is unpublished.
    Avi · 1 years ago
    Very helpful. Thank you!
  • To post as a guest, your comment is unpublished.
    Damian · 2 years ago
    great, worked great for me
  • To post as a guest, your comment is unpublished.
    Sandy · 2 years ago
    Thank you so much for your information. It was really nice!
  • To post as a guest, your comment is unpublished.
    Ali · 2 years ago
    Hi there
    It does not work with unicode file names. How can it be done fine?
  • To post as a guest, your comment is unpublished.
    Jayakumar · 3 years ago
    Hi,

    I'm trying to use the code for renaming the non extension files to files with extension. it doesn't work. It works fine with files which have extension already.

    Thanks
    Jay
  • To post as a guest, your comment is unpublished.
    cda · 3 years ago
    Thank you so much...
  • To post as a guest, your comment is unpublished.
    Gee Kay · 3 years ago
    Hello, some of the files have same name. Scripts skip those files. Is there a way to modify the script so it adds a number 1, 2, 3 and so on for every duplicate name?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Kay,
      To rename the files based on duplicate new names with name-1,name-2, the following VBA code may help you, please try it.

      Note: After pasting this code, you should reference the Microsoft Scripting Runtime option in the References-VBAProject dialog box as below screenshot shown.

      Sub RenameFiles()
      Dim xDir As String
      Dim xFile As String
      Dim xVal As String
      Dim xRow As Long
      Dim xCount As Long
      Dim xTemp As Long
      Dim xDictionary As New Dictionary
      Application.ScreenUpdating = False
      On Error Resume Next
      With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      If .Show = -1 Then
      xDir = .SelectedItems(1)
      xFile = Dir(xDir & Application.PathSeparator & "*")
      Do Until xFile = ""
      xRow = 0
      xRow = Application.Match(xFile, Range("A:A"), 0)
      If xRow > 0 Then
      xCount = 0
      xVal = Cells(xRow, "B").Value
      If Not xDictionary.Exists(xVal) Then
      xDictionary.Add xVal, xCount
      Else
      xDictionary.Item(xVal) = xDictionary.Item(xVal) + 1
      xTemp = InStrRev(xVal, ".")
      Cells(xRow, "B").Value = Mid(xVal, 1, xTemp - 1) & "-" & CStr(xDictionary.Item(xVal)) & Mid(xVal, xTemp)
      End If
      Name xDir & Application.PathSeparator & xFile As _
      xDir & Application.PathSeparator & Cells(xRow, "B").Value
      End If
      xFile = Dir
      Loop
      End If
      End With
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    Hemant · 4 years ago
    I am following the same process but couldn't succeeded in renaming the file i am also changing ("A:A") and ("B"). Is there any other valuve which i need to change in the code
  • To post as a guest, your comment is unpublished.
    Dee · 4 years ago
    I cant understand with range ( A:A) or (B) how to define that, thank you
    • To post as a guest, your comment is unpublished.
      skyyang · 4 years ago
      Hi, Dee,

      As the note remind, the reference A:A indicates the old filenames list you want to rename, and reference B contains the new filenames that you want to use
      , as following screenshot shown:

      Hope this can help you, thank you!
  • To post as a guest, your comment is unpublished.
    Rasmus · 4 years ago
    I would like to rename multiple files from folders and subfolders. So im thinking a VBA code there looks at the path there is in the Column 'Containing Folder' (I Check the checkbox 'Include files in subdirectories') I am no where near good enough for coding VBA, but i look and try to understand. Thanks
  • To post as a guest, your comment is unpublished.
    Dave Koch · 4 years ago
    This is game changing. Thank you SO MUCH!!!
  • To post as a guest, your comment is unpublished.
    JonathanF · 4 years ago
    To rename the multiple files you can also check a software. Search on google- BatchRenameFiles Tool. Check the first search result.


    Thanks
    Jonathan F.
  • To post as a guest, your comment is unpublished.
    sanjit · 4 years ago
    hi, Amazing codes, has saved my hell lot of time.. thanks a ton..
  • To post as a guest, your comment is unpublished.
    aparna · 4 years ago
    i want to make combo of pictures for example i have 33 images and i want to create a combo of pictures with each other, please find attched i have 10000 more images so that i can create in a 1 click of multiple images.
    please call or email at caparnasingla@gmail.com
  • To post as a guest, your comment is unpublished.
    tulpan · 4 years ago
    I suggest to try KrojamSoft BatchRename
  • To post as a guest, your comment is unpublished.
    raju · 4 years ago
    I tried it for excel, it changes file type.Can you please provide me code for csv files rename.
  • To post as a guest, your comment is unpublished.
    Ronald · 4 years ago
    Dear all,

    Try KrojamSoft BatchRename for batch rename the files its very easy to use.
  • To post as a guest, your comment is unpublished.
    aparna · 4 years ago
    Thank you so much sir,
    God bless you!
  • To post as a guest, your comment is unpublished.
    yatin · 5 years ago
    Hi,

    I have more than 100 excel files which I have downloaded from a system and kept in the folder.
    All these files has a sheet named as [u][b]Content[/b][/u]. I want these excel files to be named using the text that is available in the Cell AA2 of the respective [b][u]Content[/u][/b] sheet. Such that the name in this Cell AA2 will be exactly the File name of that respective Excel file.
    all the files in the folder should get renamed in the same way.
    Please suggest a macro that can work in this way.
    • To post as a guest, your comment is unpublished.
      Franz Weber · 4 years ago
      Hi. Is your task already Solved. ? I can help if still unsolved. Pls mail me your task. Tx Franz
  • To post as a guest, your comment is unpublished.
    Dawnn · 5 years ago
    I successfully used the above instructions last year to rename files in half-a-dozen folders. It worked so well that I saved the instructions for future use. I've recently tried to use it again, but after pasting the macro above in the Module window when I hit F5 instead of getting a browser window to select the desired folder, I get a new dialog box prompting me for a Macro name. Any suggestions? I usually have about 3-4 times a year where I need to rename files this way, and I was very excited when it worked last year.
  • To post as a guest, your comment is unpublished.
    Nathan · 6 years ago
    Is it possible rename just a piece of a file name without declaring the full file name and extension in the excel sheet? i.e. change 123-yz.jpg to abc-yz.jpg but I only know that 123 needs changing to abc on every image in the folder?
  • To post as a guest, your comment is unpublished.
    |Richard · 6 years ago
    Surprised (a little) to see someone use the same syntax for naming vars (xDir, for example). It allows for exceptionally well named vars that don't conflict with system names (e.g., Dir or Date). I also use 'z' for global functions (e.g., zMsg, that returns true or false rather than vbOK or vbCancel) and 'y' for constants (e.g., yCR for vbNewline and yCR2 for two of them).
    Good work and good thinking on your part. You must be very smart!
  • To post as a guest, your comment is unpublished.
    Sushant Ghatage · 6 years ago
    How to rename files which contains chinese characters in name.
    Please help on it
  • To post as a guest, your comment is unpublished.
    removejoe · 6 years ago
    Nevermind, i figured it out. Thanks. " xRow = Application.Match(xFile, Range("C:C"), 11)"
  • To post as a guest, your comment is unpublished.
    removejoe · 6 years ago
    What do i need to modify in the code above if my old and new names do not start until Row 11. I changed this line "xRow = Application.Match(xFile, Range("C11:C5000"), 0)" but it didn't work.
  • To post as a guest, your comment is unpublished.
    Rafael · 7 years ago
    Dear all,

    How can I change FOLDERS the same way as shown above?

    Could you kindly help me with this issue?

    The question is: How to rename multiple FOLDERS of a folder in Excel?

    Kind regards!