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

G

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
 
R

Rick Rothstein

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

For Each rFile In Application.RecentFiles
 
G

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

Rick Rothstein said:
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 said:
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

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top