我正在尝试从 Excel 列表中移动所有日常文件。D 列为来源,D:\Hard drive\Lee’s Hard Drive\My Documents\WBD052U_PRINT01*.txt
E 列为目标,C:\Users\Lee\Documents\Work\01. WBD52U
当我运行宏时出现以下错误
运行时错误 13。类型不匹配。
如果我只写一行
FromPath Range(D5:D5) ToPath Range (E5:E5)
这很完美。
一旦我指定更大的范围,它就不起作用。谢谢,我真的需要一些帮助。
Sub Move_Certain_Files()
Dim fso, MyFile
Dim FromPath As String
Dim ToPath As String
FromPath = ActiveSheet.Range("D5:D6") '<< Change
ToPath = ActiveSheet.Range("E5:E6") '<< Change
On Error Resume Next
Kill FromPath = ActiveSheet.Range("D5:D6")
On Error GoTo 0
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
fso.copyFile (FromPath), ToPath, True
MsgBox "File Copied to Destination Folder Successfully", vbInformation, "Done!"
fso.copyFile Source:=FromPath, Destination:=ToPath
On Error GoTo 0
End Sub
答案1
发生此错误是因为您尝试将范围设置为字符串变量 (FromPath)。您需要使用 Set 将其设置为 Range 变量。我们将要处理该范围内的每个项目。处理单个列范围最简单(我们可以同时轻松地将内容引用到侧面)。此外,我们可以在处理范围时直接进行复制工作,而不是处理范围以进行拾取,然后稍后再进行复制工作。开始吧:
Sub Move_Certain_Files()
Dim fso, MyFile
Dim pathsRng As Range
'Set range here, just the first [FROM path] column:
Set pathsRng = ActiveSheet.Range("D5:D6")
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
'Loop through paths range to copy from FROM path and paste
'to TO path (TO path is found by using relative address (1, 2):
For Each pathItem In pathsRng
fso.CopyFile (pathItem.Value), pathItem(1, 2).Value, True
Next pathItem
'Display success/failure message:
If Err.Number = 0 Then
MsgBox "File(s) Copied to Destination Folder Successfully", vbInformation, "Done!"
Else
MsgBox "Error: Some files may not have copied.", vbInformation, "Done!"
End If
On Error GoTo 0
End Sub
补充:我想问一下,我不确定你这句话的意图:
Kill FromPath = ActiveSheet.Range("D5:D6")
复制后是否要删除源文件?如果是,则应在复制后使用循环执行此操作 - 或者您可以移动文件而不是复制:在上面的代码中,您需要更改以下行:
fso.CopyFile (pathItem.Value), pathItem(1, 2).Value, True
更改为:
fso.MoveFile (pathItem.Value), pathItem(1, 2).Value