我正在尝试使用此代码在VBA中复制文件
Sub move_data()
Dim FSO As Object
Dim FileInFromFolder As Object
Dim FromPath As String
Dim ToPath As String
Dim ws As Worksheet:
Set FSO = CreateObject("scripting.filesystemobject")
Set ws = ThisWorkbook.Sheets("Sheet1")
FromPath = FSO.GetFolder(ws.Range("E1").Value)
ToPath = FSO.GetFolder(ws.Range("E3").Value)
For Each FileInFromFolder In FSO.GetFolder(FromPath).Files
FileInFromFolder.Move ToPath
Next FileInFromFolder
End Sub
我正进入(状态
运行时错误58-文件已存在
尽管该文件中没有文件 ToPath
如果我直接引用文件夹而不是引用Sheet1的单元格E1和E3中的文件夹名称,则该代码有效。
上的Microsoft文档Move
:
备注
在文件或文件夹上执行Move方法的结果与使用FileSystemObject.MoveFile或FileSystemObject.MoveFolder执行的操作相同。但是,您应该注意,其他方法也可以移动多个文件或文件夹。
上的Microsoft文档MoveFile
:
备注
如果source包含通配符,或者destination以路径分隔符结尾,则假定destination指定了一个现有文件夹,可在其中移动匹配的文件。否则,假定destination是要创建的目标文件的名称。在这两种情况下,移动单个文件都可能发生三件事:
调查
fso.MoveFile
fso.MoveFile
是要走的路,但这并不是那么简单。If .GetFolder(FromPath).Files.Count > 0 Then
。FromPath
必须紧跟在PathSeparator
且至少要加上一个*
,而ToPath
必须以结束PathSeparator
。fso.File.Move
fso.File.Move
解决方案必须通过文件循环。但是您无需检查文件计数,可以实现If Not .FileExists(ToPath & fsoFile.Name) Then
检查目标中是否存在相同名称的文件。ToPath
必须以结尾PathSeparator
,但可以选择后面跟文件名(fsoFile.Name
)。编码
Option Explicit
Sub moveAllFilesFSO()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim FromPath As String: FromPath = ws.Range("E1").Value
Dim ToPath As String: ToPath = ws.Range("E3").Value
With CreateObject("Scripting.FileSystemObject")
If .FolderExists(FromPath) And .FolderExists(ToPath) Then
If .GetFolder(FromPath).Files.Count > 0 Then
Dim Sep As String: Sep = Application.PathSeparator
If Right(FromPath, 1) <> Sep Then
FromPath = FromPath & Sep
End If
FromPath = FromPath & "*" ' "*.*"
If Right(ToPath, 1) <> Sep Then
ToPath = ToPath & Sep
End If
.MoveFile FromPath, ToPath
Else
Debug.Print "No files found in " & "'" & FromPath & "'."
End If
Else
Debug.Print "At least one of the folders does not exist."
End If
End With
End Sub
Sub moveAllFilesFile()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim FromPath As String: FromPath = ws.Range("E1").Value
Dim ToPath As String: ToPath = ws.Range("E3").Value
With CreateObject("Scripting.FileSystemObject")
If .FolderExists(FromPath) And .FolderExists(ToPath) Then
ToPath = .GetFolder(ToPath) & Application.PathSeparator
Dim fsoFile As Object
For Each fsoFile In .GetFolder(FromPath).Files
If Not .FileExists(ToPath & fsoFile.Name) Then
fsoFile.Move ToPath ' & fsoFile.Name
'Else
' File already exists.
End If
Next fsoFile
Else
Debug.Print "At least one of the folders does not exist."
End If
End With
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句