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

or

如何基于Excel中的列表将文件从一个文件夹复制或移动到另一个文件夹?

如果您在工作表的一列中有一个文件名列表,并且这些文件位于您的计算机的一个文件夹中。 但是,现在,您需要将这些名称列在工作表中的文件从原始文件夹移动或复制到另一个文件,如下面的截图所示。 你怎么能尽快完成这项任务,你可以在Excel中?

使用VBA代码基于Excel中的列表将文件从一个文件夹复制或移动到另一个文件夹


使用VBA代码基于Excel中的列表将文件从一个文件夹复制或移动到另一个文件夹


要根据文件名列表将文件从一个文件夹移动到另一个文件夹,以下VBA代码可能会对您有帮助,请按照以下步骤操作:

1。 按住 Alt + F11键 在Excel中的键,它打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下VBA代码粘贴到模块窗口中。

VBA代码:基于Excel中的列表将文件从一个文件夹移动到另一个文件夹

Sub movefiles()
'Updateby Extendoffice
    Dim xRg As Range, xCell As Range
    Dim xSFileDlg As FileDialog, xDFileDlg As FileDialog
    Dim xSPathStr As Variant, xDPathStr As Variant
    Dim xVal As String
    On Error Resume Next
    Set xRg = Application.InputBox("Please select the file names:", "KuTools For Excel", ActiveWindow.RangeSelection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xSFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    xSFileDlg.Title = " Please select the original folder:"
    If xSFileDlg.Show <> -1 Then Exit Sub
    xSPathStr = xSFileDlg.SelectedItems.Item(1) & "\"
    Set xDFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    xDFileDlg.Title = " Please select the destination folder:"
    If xDFileDlg.Show <> -1 Then Exit Sub
    xDPathStr = xDFileDlg.SelectedItems.Item(1) & "\"
    For Each xCell In xRg
        xVal = xCell.Value
        If TypeName(xVal) = "String" And xVal <> "" Then
            FileCopy xSPathStr & xVal, xDPathStr & xVal
            Kill xSPathStr & xVal
        End If
    Next
End Sub

3。 然后按 F5 键运行这个代码,弹出一个提示框提醒你选择包含文件名的单元格,看截图:

4。 然后点击 OK 按钮,在弹出的窗口中,请选择包含您要移动的文件的文件夹,请参阅屏幕截图:

5。 然后点击 OK,继续选择要在另一个弹出窗口中查找文件的目标文件夹,请参阅屏幕截图:

6。 最后点击 OK 关闭窗口,现在,文件已经被移动到您指定的另一个文件夹中,该文件夹是基于工作表列表中的文件名,请参见屏幕截图:

注意:如果您只是想将文件复制到另一个文件夹,但保留原始文件,请使用下面的VBA代码:

VBA代码:基于Excel中的列表将文件从一个文件夹复制到另一个文件夹

Sub copyfiles()
'Updateby Extendoffice
    Dim xRg As Range, xCell As Range
    Dim xSFileDlg As FileDialog, xDFileDlg As FileDialog
    Dim xSPathStr As Variant, xDPathStr As Variant
    Dim xVal As String
    On Error Resume Next
    Set xRg = Application.InputBox("Please select the file names:", "KuTools For Excel", ActiveWindow.RangeSelection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xSFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    xSFileDlg.Title = "Please select the original folder:"
    If xSFileDlg.Show <> -1 Then Exit Sub
    xSPathStr = xSFileDlg.SelectedItems.Item(1) & "\"
    Set xDFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    xDFileDlg.Title = "Please select the destination folder:"
    If xDFileDlg.Show <> -1 Then Exit Sub
    xDPathStr = xDFileDlg.SelectedItems.Item(1) & "\"
    For Each xCell In xRg
        xVal = xCell.Value
        If TypeName(xVal) = "String" And xVal <> "" Then
            FileCopy xSPathStr & xVal, xDPathStr & xVal
        End If
    Next
End Sub

Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
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.
    ADOLFO · 12 days ago
    buenas noches, alguien sabe que tengo que modificar para que me mueva carpetas y no solo archivos?
  • To post as a guest, your comment is unpublished.
    jonathan · 1 months ago
    thanks so much !
  • To post as a guest, your comment is unpublished.
    Michael · 1 months ago
    Has anyone figured out how to copy files that are located in multiple subfolders of the main directory, and paste into another folder directory? Also does this transfer method only work for folders on the C drive? I am trying to copy files from our directory that includes multiple subfolders where several files are stored located in Microsoft Sharepoint, to a folder on my C drive.

    Any help would be much appreciated!
  • To post as a guest, your comment is unpublished.
    julien · 2 months ago
    hi do you know how to search also on the subfolder ?
  • To post as a guest, your comment is unpublished.
    Technically Specific · 4 months ago
    Works great - thank you! However-->>>Can this be adjusted to use a PARTIAL FILE NAME? If so, can you help show how?
    In other words, if file name on the Excel sheet list of filenames is: OW4234TR_J19031.txt (I'd like it to only look at last 5 chars "19031" which is a Julian Date and move a range of files... (anything with a Julian Date of 19092 thru 19120) into the March folder.. The ultimate over-arching task is to locate any files that have Julian dates for March and put those in the Fiscal March Folder "06-Mar" , April into the April folder "07-Apr" and so on...so Fiscal reconciliations by month can then take place.. TIA for any help anyone can offer to expedite this time consuming hunt and picking process. =-)
  • To post as a guest, your comment is unpublished.
    Fer · 5 months ago
    en el codigo que copia ¿como puedo colorear el nombre de la lista que no encuentre?
  • To post as a guest, your comment is unpublished.
    Johnette · 6 months ago
    I cannot get either version to work in Windows 10.


    Argggg
  • To post as a guest, your comment is unpublished.
    Andy · 9 months ago
    Any tips on how to modify the code to add a wide card? I have an archive of hundreds of PDF files that are 10 digit numbers and revision level (XXXXXXXXXX_REVA). I can export a list of file names very easily from our ERP system, but the list is missing the revision and file extension. Is there a way to add wild cards into the program to ignore everything BUT the 10 digit number?
  • To post as a guest, your comment is unpublished.
    PWD · 10 months ago
    Hello,
    how to make this code copy files from subfolders?
  • To post as a guest, your comment is unpublished.
    Pr · 11 months ago
    Hi Guys,

    How I need to change '' If TypeName(xVal) = "String" And xVal <> "" Then '' to move files based on partial file name.


    Thanks in advance,
    Best regards, P
    • To post as a guest, your comment is unpublished.
      Technically Specific · 4 months ago
      Did you ever find out HOW to use a PARTIAL FILE NAME? I need that as well...
      In other words, if file name on the Excel sheet list is: OW4234TR_J19031.txt (I'd like it to only look at last 5 chars "19031" which is a Julian Date and move a range of files... anything with a Julian Date of 19031 thru 19075..
  • To post as a guest, your comment is unpublished.
    Sidney · 1 years ago
    Você não tem noção de como me ajudou com esse script... Muito bom!!! Obrigado!!!
  • To post as a guest, your comment is unpublished.
    Robinson · 1 years ago
    Obrigado!!!!
  • To post as a guest, your comment is unpublished.
    Alisson · 1 years ago
    Era exatamente isso que eu precisava!!!

    Muito Obrigado!!!!
  • To post as a guest, your comment is unpublished.
    Raki · 1 years ago
    Hi, In the source folder, I wish to set it as a constant from a cell, for example the path entered in a particular cell, like A1, should be treated as the source folder. How to do this?
  • To post as a guest, your comment is unpublished.
    madhan · 1 years ago
    It is the nice macro.Real helpful for me. but I need some update macro.this used to copy the files from single folder to another folder. we need copy files from folder and subfolder to another folder.