循环文件夹，使用VBA重命名满足特定条件的文件？ [英] Loop through folder, renaming files that meet specific criteria using VBA?

问题描述

我是VBA的新人（在Java中只有一点训练），但是在这里的其他帖子的帮助下组装了这一段代码，并且打了墙。

I am new to VBA (and have only a bit of training in java), but assembled this bit of code with the help of other posts here and have hit a wall.

我正在尝试编写一个循环遍历文件夹中每个文件的代码，测试每个文件是否符合某些标准。如果满足条件，则应编辑文件名，覆盖（或先前删除）任何具有相同名称的现有文件。这些新重命名的文件的副本应该被复制到另一个文件夹。我相信我很接近，但我的代码拒绝循环遍历所有文件和/或崩溃Excel运行时。请帮助？ ： - ）

I am trying to write code that will cycle through each file in a folder, testing if each file meets certain criteria. If criteria are met, the file names should be edited, overwriting (or deleting prior) any existing files with the same name. Copies of these newly renamed files should then be copied to a different folder. I believe I'm very close, but my code refuses to cycle through all files and/or crashes Excel when it is run. Help please? :-)

Sub RenameImages()

Const FILEPATH As String = _
"C:\\CurrentPath"
Const NEWPATH As String = _
"C:\\AditionalPath"


Dim strfile As String
Dim freplace As String
Dim fprefix As String
Dim fsuffix As String
Dim propfname As String

Dim FileExistsbol As Boolean

Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")

strfile = Dir(FILEPATH)

Do While (strfile <> "")
  Debug.Print strfile
  If Mid$(strfile, 4, 1) = "_" Then
    fprefix = Left$(strfile, 3)
    fsuffix = Right$(strfile, 5)
    freplace = "Page"
    propfname = FILEPATH & fprefix & freplace & fsuffix
    FileExistsbol = FileExists(propfname)
      If FileExistsbol Then
      Kill propfname
      End If
    Name FILEPATH & strfile As propfname
    'fso.CopyFile(FILEPATH & propfname, NEWPATH & propfname, True)
  End If

  strfile = Dir(FILEPATH)

Loop

End Sub

如果有用，文件名开始作为ABC_mm_dd_hh_Page _＃。jpg，目标是将它们剪切为ABCPage＃.jpg

If it's helpful, the file names start as ABC_mm_dd_hh_Page_#.jpg and the goal is to cut them down to ABCPage#.jpg

非常感谢！

推荐答案

我认为在开始处理数组或集合之前首先收集所有的文件名是个好主意，特别是如果要重新命名的话。如果您不保证不会混淆Dir（），导致它跳过文件或处理相同文件两次。另外在VBA中，不需要在字符串中转义反斜杠。

I think it's a good idea to first collect all of the filenames in an array or collection before starting to process them, particularly if you're going to be renaming them. If you don't there's no guarantee you won't confuse Dir(), leading it to skip files or process the "same" file twice. Also in VBA there's no need to escape backslashes in strings.

以下是使用集合的示例：

Here's an example using a collection:

Sub Tester()

    Dim fls, f

    Set fls = GetFiles("D:\Analysis\", "*.xls*")
    For Each f In fls
        Debug.Print f
    Next f

End Sub



Function GetFiles(path As String, Optional pattern As String = "") As Collection
    Dim rv As New Collection, f
    If Right(path, 1) <> "\" Then path = path & "\"
    f = Dir(path & pattern)
    Do While Len(f) > 0
        rv.Add path & f
        f = Dir() 'no parameter
    Loop
    Set GetFiles = rv
End Function

这篇关于循环文件夹，使用VBA重命名满足特定条件的文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！