excel - 在文件夹中循环并删除 .xlsm 文件

Question

亲爱的，

我创建了一个代码来循环遍历文件夹并删除 10 年前的文件，但循环无法正常工作，因为 FolderPath(变量)保持不变并且循环遵循无限循环。有帮助吗？

Sub LoopThroughFolder()

Dim FolderPath As String

FolderPath = Dir("C:\Users\XXXX\XXXX\XXXX\XXXX\XXXX\*.xlsm")

 Do While FolderPath <> ""
    If DateValue( _
                    Mid(FolderPath, InStr(FolderPath, ".") + 1, 2) & "/" & _
                    Mid(FolderPath, InStr(FolderPath, ".") + 3, 2) & "/" & _
                    Mid(FolderPath, InStr(FolderPath, ".") + 5, 4)) _
        < DateValue(Date - 10) Then

        Application.DisplayAlerts = False
            SetAttr "C:\Users\XXXX\XXXX\XXXX\XXXX\XXXX\" & FolderPath, vbNormal
            Kill "C:\Users\XXXX\XXXX\XXXX\XXXX\XXXX\" & FolderPath
        Application.DisplayAlerts = True

        FolderPath = Dir

    End If
Loop

End Sub

Answer 1

你需要搬家FolderPath = Dir ,
目前它位于 IF 语句中，这意味着如果当前文件早于 10 天，它只会查找下一个文件。

Sub LoopThroughFolder()

Dim FolderPath As String

FolderPath = Dir("C:\Users\XXXX\XXXX\XXXX\XXXX\XXXX\*.xlsm")

 Do While FolderPath <> ""
    If DateValue( _
                    Mid(FolderPath, InStr(FolderPath, ".") + 1, 2) & "/" & _
                    Mid(FolderPath, InStr(FolderPath, ".") + 3, 2) & "/" & _
                    Mid(FolderPath, InStr(FolderPath, ".") + 5, 4)) _
        < DateValue(Date - 10) Then

        Application.DisplayAlerts = False
            SetAttr "C:\Users\XXXX\XXXX\XXXX\XXXX\XXXX\" & FolderPath, vbNormal
            Kill "C:\Users\XXXX\XXXX\XXXX\XXXX\XXXX\" & FolderPath
        Application.DisplayAlerts = True
    End If
  FolderPath = Dir
Loop

End Sub