Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Manipulate Word’s Recent Files List

Despite the collective moniker, the Scripting Guys are probably far more different than they are alike. (Among other things, one of them isn’t even a guy.) Like four peas in a pod? Well, not exactly.

The differences between the individual Scripting Guys really show up when it comes to storing files on their computers. Most of the Scripting Guys are at least semi-organized when it comes to file management. However, there is one Scripting Guy – we’ll call him Greg…uh, Gary, we’ll call him Gary, whose idea of file management consists of a single desktop folder named Stuff; everything Gr – everything Gary does is simply crammed into the Stuff folder. Definitely an…interesting…approach to file and document management.

As you might expect, our pal Gary relies heavily on the recent files menu in Word; without that he’d probably never be able to locate and re-open a Word document. If you aren’t familiar with the recent files feature, it’s simply the last x number of Word documents that you’ve worked with, all displayed at the bottom of the File menu:

Nice, huh? But does this mean Gary has to open up Word any time he wants to find out the path to a recently-used Word document? Of course not; instead, Gary can retrieve the list of recently-used files by using a script just like this one:

On Error Resume Next

Set objWord = CreateObject("Word.Application")
Set colFiles = objWord.RecentFiles

For Each objFile in colFiles
    Wscript.Echo objFile.Path & "\" & objFile.Name
Next

objWord.Quit

As you can see there’s not much to this script. We create an instance of the Word.Application object, and then retrieve the RecentFiles collection. After that we use a simple For Each loop to walk through each item in the collection, echoing the Path and Name properties for each recent file. Now we have the paths to the last 9 files Gary used in Microsoft Word. (Incidentally, other Office applications – including Excel – have a similar feature.)

Ok, so we did do one other thing. Each file in the collection has separate Path and Name properties; Path represents the folder path and Name represents the actual file name. Thus the document C:\Scripts\Test.doc has a path of C:\Scripts and a Name of Test.doc. We concatenate the Path, a \ and the Name in order to get output that looks like this:

C:\Scripts\Test.doc
C:\Scripts\Office_Space.doc

Etc.

In case you were wondering, recent files are displayed in order of use. In our very simple example shown above, the last document opened was Test.doc; the document opened before Test.doc was Office_Space.doc.

Why do we care about that? Well, it’s good to know how that works because you can also use a script to add a document to the RecentFiles collection. When you do that the file at the tail end of the collection will drop off the list and the new document will be added to the top of the list. For example, suppose we use a script to add the document C:\Scripts\New_document.doc to the RecentFiles collection. If we do that and then run a script to retrieve a list of the most recently-used files, our collection will look like this:

C:\Scripts\New_document.doc
C:\Scripts\Test.doc

New_document.doc is now No. 1 with a bullet, and Office_Space.doc is gone. The great circle of life goes on.

Of course, having said all that we should probably show you a script that adds a document to the RecentFiles collection:

On Error Resume Next

Set objWord = CreateObject("Word.Application")
Set colFiles = objWord.RecentFiles

colFiles.Add("C:\Scripts\New_document.doc")

objWord.Quit

Again, pretty easy: we bind to the RecentFiles collection and then call the Add method, passing Add a single parameter: the path to the document we’re adding to the list. That’s all we have to do.

Now, what about removing a document from the collection? Well, the easiest way to do that is to loop through the collection and look for a file that has a particular path and name; if and when you find that file you can then use the Delete method to remove the document from the collection. Here’s a script that looks for – and deletes – the file C:\Scripts\New_document.doc:

On Error Resume Next

Set objWord = CreateObject("Word.Application")

Set colFiles = objWord.RecentFiles

For Each objFile in colFiles
    strPath = objFile.Path & "\" & objFile.Name
    If strPath = "C:\Scripts\New_document.doc" Then
        objFile.Delete
    End If
Next

objWord.Quit

All the excitement happens right here:

strPath = objFile.Path & "\" & objFile.Name
If strPath = "C:\Scripts\New_document.doc" Then
    objFile.Delete
End If

For each file in the collection we combine the Path, a \ and the Name to give us a string similar to this: C:\Scripts\Test.doc. We then check to see if this string is equivalent to our target file: C:\Scripts\New_document.doc. If it is, we delete the document from the RecentFiles collection (although, we hasten to add, not from the hard drive). If it isn’t, we then loop around and check the next file in the collection. A bit cumbersome but because you can only have a maximum of nine items in the collection it doesn’t take long to loop through everything.

Incidentally, recent files aren’t necessarily enabled, and the number of recent files displayed on the File menu can vary from 1 to 9. For scripts that can help you manage these settings see this page of the Picture Book of Microsoft Office Scripting. As for Gary, well, there probably isn’t any help for him. But we’ll keep looking.