提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中重命名文件夹的多个文件?

可能是我们大多数人都遇到了这个问题,我们需要重命名文件夹中的多个文件,如果文件夹中存在数百或数千个文件,逐个重命名文件名会使我们变得疯狂。 我们有什么好的功能来处理这个任务吗?

使用kutools for Excel从工作表中的特定文件夹列出所有文件名

使用VBA代码在Excel中重命名文件夹的多个文件



如果有多个要重命名的文件,首先,可以在工作表的一列中列出旧文件名,然后输入要替换的新文件名。 要快速列出工作表中的所有文件,可以使用 Kutools for Excel文件名列表 效用。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

安装后 Kutools for Excel,请按照以下步骤操作:( 免费下载Kutools for Excel )

1。 点击 企业服务 > 导入/导出 > 文件名列表,看截图:

DOC-重命名,多文件,1

2。 在 文件名列表 对话框,单击 DOC-重命名,多文件,1 按钮选择要列出文件的文件夹,然后单击 所有文件 来自 文件类型,看截图:

DOC-重命名,多文件,1

3。 然后点击 OK 按钮,所有文件名都列在新工作表的一列中,以及一些文件属性中,现在,您可以删除其他不需要的列,只留下 文件名 列,请参阅屏幕截图:

DOC-重命名,多文件,1

点击了解更多关于此文件名单功能...

免费下载Kutools for Excel


在列A中列出所有原始文件名后,您应在列B中输入新文件名,如下面的屏幕截图所示:

DOC-重命名,多文件,1

在这里我将讨论一个VBA代码,它可以帮助你一次性用新文件名替换旧文件名。 请遵循以下步骤:

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications窗口.

2。 点击 插页 > 模块,并粘贴在下面的宏 模块窗口.

VBA代码:重命名文件夹中的多个文件

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。 粘贴代码后,请按 F5 运行这个代码的关键,并在 浏览 窗口中,选择要更改文件名的文件夹,请参阅屏幕截图:

DOC-重命名,多文件,1

4。 然后点击 OK,所有旧文件名已被立即替换为新文件名。 查看屏幕截图:

DOC-重命名,多文件,1  2 DOC-重命名,多文件,1

备注:

1。 当您列出旧文件和新文件名时,必须包含文件扩展名。

2。 在上面的代码中,引用 答: 表示要重命名的旧文件名列表以及引用 B 包含您要使用的新文件名,您可以根据需要更改它们


Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

如何从Excel中的一系列单元格创建序列工作表?

如何重命名Excel中的多个工作表?


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Ali · 4 months 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 · 1 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 · 1 years ago
    Thank you so much...
  • To post as a guest, your comment is unpublished.
    Gee Kay · 1 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 · 1 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 · 1 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 · 1 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 · 1 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 · 1 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 · 2 years ago
    This is game changing. Thank you SO MUCH!!!
  • To post as a guest, your comment is unpublished.
    JonathanF · 2 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 · 2 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 · 2 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 · 2 years ago
    I suggest to try KrojamSoft BatchRename
  • To post as a guest, your comment is unpublished.
    raju · 2 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 · 2 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 · 2 years ago
    Thank you so much sir,
    God bless you!
  • To post as a guest, your comment is unpublished.
    yatin · 3 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 · 2 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 · 3 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 · 3 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 · 4 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 · 4 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 · 4 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 · 4 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 · 4 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!