VBA - How do I remove a file from the recent documents list in excel 2007?

Question

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.

Answer 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

Answer 2

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

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

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.

Answer 3

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

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

Answer 4

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.

Then the routine will work properly.

Answer 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.

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

Answer 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.