You need to qualify the RecentFiles with the Application's object in the For
statement...

For Each rFile In Application.RecentFiles

--
Rick (MVP - Excel)


"Geoff Budd" wrote in message
...

I have a macro that removes all the recently open file names (except those
that are "pinned") from the Office Button in Word 2007 - kindly supplied
by
one of the experts in these forums.
I now want to set up the same in Excel 2007, so I tried modifying the
macro
(below) by replacing "Word" by "Excel" in the fifth line. (The macro sits
in
Personal.xlsb, which ensures that it remains available whatever workbook
is
open).

However, when I run the macro in Excel 2007, I get the following Visual
Basic error message:
"Run time error '424'
Object required"
and the debug highlights the sixth line ("For Each rFile In RecentFiles").

Has anybody got any ideas on what I need to do to make this work?

Many thanks.

The macro I am running is below:

Sub ClearMRU_NotPinned()
Dim rFile As RecentFile
Dim WSHShell, RegKey, rKeyWord
Set WSHShell = CreateObject("WScript.Shell")
RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\ Excel\File
MRU\"
For Each rFile In RecentFiles
rKeyWord = WSHShell.RegRead(RegKey & "Item " & rFile.Index)
If InStr(1, rKeyWord, "[F00000000]") Then
rFile.Delete
End If
If InStr(1, rKeyWord, "[F00000002]") Then
rFile.Delete
End If
Next rFile
End Sub