Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete Recent Files list (except those "pinned") - 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete Recent Files list (except those "pinned") - Excel
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 " -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete Recent Files list (except those "pinned") - Excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete Recent Files list (except those "pinned") - Ex
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" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Excel 2007 "Recent" files not updating | Excel Discussion (Misc queries) | |||
Delete emty file names from "recent documents" Excel | Excel Discussion (Misc queries) | |||
Files not in "My Recent Documents" list | Excel Discussion (Misc queries) | |||
How do I delete items out of my "recent documents" list? | Excel Discussion (Misc queries) | |||
Read ".dll" files, the Macro "work flow" and the actual values of the variables when Macro is running | Excel Programming |