Better MRU list possible via VBA?

[DFStoneJr]

I'm intrigued by this Word "hack," which uses VBA to manage a longer
most-recently-used documents list than the default nine-item list in
Word:

http://hacks.oreilly.com/pub/h/2555

I know I fill up my MRU list pretty quickly, and nine items isn't
enough to keep me from having to scrounge through folders in search of
files. Is such a "hack" possible for Excel?

It appears to me that most of the code is easily reproducible in, or
modifiable for, Excel. The key appears to be in the code at the bottom
of the page, which reads file names from and writes them to an .ini
file, and I haven't been able to come up with anything in Excel to
mimic that.

Any suggestions, thoughts or pointers?

TIA.

 

[Bernie Deitrick]

Dick Kusleika has a favorites menu:

http://www.dailydoseofexcel.com/archives/2004/12/07/favorites-menu/

HTH,
Bernie
MS Excel MVP

 

[Guest]

The article appears incomplete - it appears that a userform is required to
actually select a file from the larger list (and the code for populating the
userform is not included) - it doesn't use the built in MRU capability.
Given that, you could certainly program similar functionality into Excel
using low level file IO rather than using an INI file. (the
privateprofilestring functions are not included in Excel VBA, but they really
are not necessary).

http://support.microsoft.com/kb/90988/en-us
http://support.microsoft.com/default.aspx?scid=kb;en-us;151262

http://www.applecore99.com/gen/gen029.asp

If you actually want to use that functionality (ini file functionality), you
can use the windows API which those functions are "fronting"

see MSDN for help on PrivateProfileString
Working with Sequential Access Files
ACC: How to Use Get, WritePrivateProfileString Functions 1.x/2.0

that is for the 16-bit API, but you can adapt it to the 32 bit version.

 

[Guest]

Couple more links.

http://support.microsoft.com/kb/75639/en-us
Accessing Windows Initialization Files from Visual Basic

http://support.microsoft.com/kb/210115/en-us
ACC2000: How to Convert API Calls from 16-bit to 32-bit

 

[RB Smissaert]

Lookup in Excel VBA SaveSetting and GetSetting.
That can replace System.PrivateProfileString
In the code you mentioned.
Or you could just use a simple .ini file. Very easy to read
and write to with the Windows API.

RBS

 

[Guest]

Just for clarification:

Or you could just use a simple .ini file. Very easy to read
and write to with the Windows API.

The code cited does use a simple .ini file as I read it.

 

[DFStoneJr]

Thanks for the answers. I appreciate it, though, since I'm an
accountant and not a professional programmer, "using low level IO" and
the API are beyond my current skill set.

Tom is correct that there's a UserForm that's populated with the list
of files stored in the .ini file:

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 25
lstMRU.AddItem
System.PrivateProfileString(FileName:="C:\Windows\mru.ini",
Section:="MRU_Files", Key:="MRU" & Format(i, "00"))
Next i
End Sub

Private Sub lstMRU_Click()
cmdOpen.Enabled = True
End Sub

Private Sub cmdCancel_Click()
MRU.Hide
Unload MRU
End Sub

Private Sub cmdOpen_Click()
On Error GoTo Trap
MRU.Hide
Documents.Open lstMRU.Value
Unload MRU
End
Trap:
If Err.Number = 5174 Then MsgBox "Word cannot find the file " &
lstMRU.Value & "." & vbCr & vbCr & "The file may have been renamed,
mobved or deleted.", vbOKOnly + vbCritical, "MRU - File Not Found"
End Sub

What makes this thing a beaut is that you can vary the size of the
list. The default is 25, but you can make it larger or smaller to suit
your needs. It sure is handy in Word, but it'd be even more handy in
Excel.

Thanks again for the responses.

 

[DFStoneJr]

Just had a thought ... since I'm not real good at API stuff, what do
you guys think about writing the file names to cells in PERSONAL.XLS
and populating the form from the range of stored names?