Macro to delete Recent Files list (except those "pinned") - Excel

[Geoff Budd]

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

 

[Ron de Bruin]

Hi Geoff

Start with Jim's example on my site
http://www.rondebruin.nl/ribbon.htm

In the RibbonX Tips part

See "Dictator examples and Hide the MRU("Most Recently Used") file list "

 

[Rick Rothstein]

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

For Each rFile In Application.RecentFiles

 

[Geoff Budd]

Thanks for this Rick.

I've included your suggestion but now I get the following error message:

Run-time error '1004'
Application-defined or object-defined error

The debug now highlights the statement:
rKeyWord = WSHShell.RegRead(RegKey & "Item " & rFile.Index)

I really can't understand why Excel VB doesn't work the same as Word VB, but
maybe it's too much to hope that "compatible" applications work together!

Any further ideas Rick? (Sorry to be a pain).

Geoff

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

For Each rFile In Application.RecentFiles

--
Rick (MVP - Excel)

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

.