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