Is there a solution to change the recentfilelist programmatically

dherr · #1 11-20-2014, 08:10 AM

Hi all,
sometimes the name of the drive changes where my excelfiles are stored at. In this case all the items of the recentfiles in the list can't be used further on.
So I thought to write a macro to change the drive name of this files, but without success.
Does someone know how to solve this problem?

regards
Dietrich

charlesdh · #2 11-20-2014, 12:52 PM

Hi,

Try tis bit of code in the "Thisworksheet" module.
When the user close the workbook it should be save to the specified drive.

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
     Application.DisplayAlerts = False
    ActiveWorkbook.Save Filename:="C:\Users\laura\Documents\Excel\ExcelFiles\David.xlsm"
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
End Sub

macropod · #3 11-20-2014, 10:43 PM

All you can do through the GUI in such situations is to delete the files from the recent files list. That list is maintained in the Windows Registry and, while it's possible to edit the Registry entries programmatically, you could do serious damage to your OS if you get it wrong.

charlesdh: I can't see how your macro is at all relevant to the OP's problem, since it: (a) pre-supposes the file has been opened; and (b) that you know you want to save it to the hard-coded folder. Adding such code to every workbook, especially with hard-coded paths that may be invalidated (e.g. what do you suppose will happen if the path isn't found or the user wants to save the file to a USB drive?) is likely to create a greater problem than it will save. Furthermore, you can't specify a filename with the Save method, so your code wouldn't work anyway.

dherr · #4 11-21-2014, 09:39 AM

Hi Paul,
thanks for your answer.
You are right, the thread of 'charlesdh' is not to the problem...

But I was afraid, that there is no direct way to do this.
The next idea is:
How is the menu called which shows if you use the right mousebutton on a recentfiles name?
Eventually you could add an item to this menu where you could ask if the drive is actual yet.
(Excuse my English, I'm from Austria...)
Regards
Dietrich

macropod · #5 11-21-2014, 02:52 PM

The right-click pop-up is a built-in Office function. You cannot modify it to give the ability to add a new item or change an existing item's name or path.

dherr · #6 11-21-2014, 04:42 PM

PITY!!!
Greetings and have a nice weekend!
Dietrich

dherr · #7 11-22-2014, 03:13 AM

Hi Paul,
next idea:
can I use a click event on this recentfilelist to code somewhat?
greetings-
Dietrich

macropod · #8 11-22-2014, 06:20 AM

In the unlikely event that it is possible, it would require some serious Windows API programming.

whatsup · #9 11-22-2014, 07:46 PM

Hi @all
I understand you guys are talking of the recentfiles of office, not the one the OS provides.In this case you got the chance to temper with the list's items using the application-object recentfiles. It provides three methods:
.Open ---> to check if the file opens (if not an error occurs, and you can handle this one)
.Delete ---> to delete the item from the list
.Add ---> to add a new item, in this case the same item with the new path
The following example assumes that the second item in recentfiles doesn't open, due to a wrong Drive's name. The Drive of the path will be changed, and is added to the list:

Code:

Sub ChangeRecentFiles()
Dim strPath As String, strName As String
Dim strNewDrive As String
Dim strNewPath As String, strNewName As String
Dim lngFile As Long
 
strNewDrive = "H:"
lngFile = 2
 
strName = Application.RecentFiles.Item(lngFile).Name
strPath = Application.RecentFiles.Item(lngFile).Path
strNewPath = strNewDrive & Right(strPath, Len(strPath) - 2)
 
On Error GoTo DeleteListItem
Application.RecentFiles.Item(lngFile).Open
 
Exit Sub
DeleteListItem:
    Application.RecentFiles.Item(lngFile).Delete
    Application.RecentFiles.Add Name:=strNewPath
    strNewName = Application.RecentFiles.Item(1).Path
End Sub

Cheers

macropod · #10 11-22-2014, 08:36 PM

Hi whatsup,

That might give the OP something to work with. It'd probably need some inputboxes, etc. to solicit the missing file's entry name and/or index # and where to look for the replacement, but at least it's heading in the right direction.

dherr · #11 11-23-2014, 01:48 AM

Hi all,
it's great that you are thinking about my problem!! Thank you.
The code of 'whatsup' ... I will try it in a macro shortly...
Greetings from Salzburg and a nice Sunday-
Dietrich

gmayor · #12 11-23-2014, 03:21 AM

Maybe http://www.gmayor.com/custom_recent_files_lists.htm would help?

dherr · #13 11-23-2014, 05:54 AM

Oh, thank you for sending us this link, Graham!!
It looks great what you have programmed. I will try it shortly and inform you about the outcome.
Best regards.
Dietrich

dherr · #14 12-02-2014, 08:31 AM

Hi all,
I have a little bit programmed in VBA and created a UserForm with some code.
Unfortunately this solution you can use in Excel2007 only. When I tried it in Word2007 an unintelligible fact appeared. In Word and Excel we have the same RecentFileList to work with, but in Word-VBA you don't have all properties for this object at hand. What did Microsoft think in that case? I really don't know... In Powerpoint you have the same RecentFilesList too, but there is no programming to this object possible.
All you can say to this facts is

I have attached the UserForm file and you can import it into Excel. The code has some comments in German and I tried to translate them...
The solution is working fine by me. You have to mark the new drive where your files now are 'positioned'. Then mark these same files where you want to change the 'old' drive name into the 'new' drivename. Then push 'ausführen'-Button. At the end you have a new RecentFilesList which works with the new items.

Greetings-
Dietrich

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating Event Handlers Programmatically	pluviosilla	Word VBA	4	11-06-2014 05:36 PM
Adding macro to normal.dotm programmatically	etippelt	Word VBA	6	04-08-2013 05:55 PM
Programmatically convert Office documents to PDF from C#	hemaneelagiri	Office	0	10-21-2011 06:07 AM
outlook programmatically read an attachment into byte array	chriskaza81	Outlook	0	11-19-2010 01:03 AM
Programmatically changing folder item selection	Richard Cook	Outlook	0	02-09-2007 02:25 PM