VBA - 如何从excel 2007中的最近文档列表中删除文件？

The recent documents feature in Office is really useful, but I moved a file to a new directory and now I can't get Excel to stop hitting me with a "can't find this file" notification whenever I open a workbook. The Excel options seem only to control how many of these "recent documents" are displayed and not how many are actually saved. So I;'m wondering if there's a way in VBA to get at the list and remove the offending file.

Office中最近的文档功能非常有用，但是我将文件移动到了一个新目录，现在每次打开工作簿时都无法让Excel停止使用“找不到此文件”通知。 Excel选项似乎只控制显示这些“最近文档”的数量，而不是实际保存的数量。所以我想知道在VBA中是否有办法进入列表并删除有问题的文件。

6 个解决方案

#1

Try this...

尝试这个...

Public Function TestIt()
    For i = 1 To Application.RecentFiles.Count - 1
        Dim answer As String
        answer = MsgBox("Delete " & Application.RecentFiles(i).Name, vbYesNo)

        If answer = vbYes Then
            answer = MsgBox("Are you sure?", vbYesNo)
            If answer = vbYes Then
                Application.RecentFiles(i).Delete
            End If
        End If
    Next i
End Function

#2

Not a VBA solution, but open up Regedit and you can remove files from the list at will.

不是VBA解决方案，而是打开Regedit，您可以随意从列表中删除文件。

The "File MRU" list is what you're after; for Excel 2007 it's under

“文件MRU”列表就是你所追求的;对于Excel 2007，它是在

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\File MRU

Adjust the version number accordingly.

相应地调整版本号。

Close Excel, delete the offending file's entry from the list found there, and restart.

关闭Excel，从找到的列表中删除有问题的文件，然后重新启动。

#3

Try the routine above not as function but as SUB. And in the second line remove "-1" at its end, because the last entry will not be handled else.

尝试上面的例程不是作为功能而是作为SUB。并在第二行中删除“-1”，因为最后一个条目将不会被处理。

Then the routine will work properly.

然后例程将正常工作。

#4

Facing the same issue I wrote this litte macro, removing all files from the recent file list, which are not accessible:

面对同样的问题，我写了这个litte宏，从最近的文件列表中删除所有无法访问的文件：

Public Function CheckRecentFiles() As Integer
    Dim i As Integer, c As Integer
    For i = Application.RecentFiles.count To 1 Step -1
        'Debug.Print Application.RecentFiles(i).name
        If Dir(Application.RecentFiles(i).name) = "" Then
            Debug.Print "Delete from recent file list: " & Application.RecentFiles(i).name
            Application.RecentFiles(i).Delete
            c = c + 1
        End If
    Next i
    Debug.Print c & " files removed."
    CheckRecentFiles = c
End Function

#5

Based on @GunnarBernsteinI 's answer,I just added this to my Personal Macro Book. This is going to be super handy to clean up the temp files that I create to answer questions on SO.

基于@GunnarBernstein的回答，我刚刚将其添加到我的个人宏书中。这对于清理我创建的临时文件以回答SO上的问题非常方便。

Public Sub CleanRecentFiles()
    Const ReviewEntry As Boolean = False
    Dim f As RecentFile
    For Each f In Application.RecentFiles
        If Len(Dir(f.Name)) = 0 Then
            f.Delete
        ElseIf ReviewEntry Then
            Debug.Print f.Name
            Stop
        End If
    Next
End Sub

Demo

#6

Open the Recent Workbooks List. Right click quickly and firmly between the icon and text for the document you wish to remove from the list. A dropdown list appears. It is the list which allows you to pin an item to the list. Choose Remove from List. It does work but it can be a bit tricky to time it correctly. If you are too slow it will just try to open the file.

打开“最近的工作簿列表”。在要从列表中删除的文档的图标和文本之间快速右键单击鼠标右键。出现一个下拉列表。它是允许您将项目固定到列表的列表。选择从列表中删除。它确实有效，但正确计时可能有点棘手。如果你太慢，它只会尝试打开文件。