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

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 2

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