|Hi Derby Dad. Nearly a year on from your post with the macro and it's been found and used successfully! Very strange that Excel doesn't cater for such a basic function, and using the drop down menu a hundred times a day was getting frustrating.|
Anyway, the macro works perfectly. I've assigned it to a hotkey and for anyone reading this in the future, I have virtually no knowledge of macros. You just do View - Macros then I gave the macro a single letter name (longer names didn't seem to work) then hit Create. I copy and pasted Derby's code in but then had to delete the 'sub_open folder' and 'end sub' bits because Excel has entered these automatically. You've then got to delete the bit about "C:\Temp" ' folder to open, and replace it with just the location of the file you want to enter, in speechmarks. The try running it and it should open the relevant folder with all files present.
If you want to assign it to a hotkey, come out of the macro designer and then do View - Macro again, select Options and it's straight forward.