Excel : File Open Dialog in VBA that is set to Detail View and sorted by Date Modified

I have tried to Google for this quite extensively but I hope someone here can come up trumps.

As a lot of people ( I imagine ) would like, I want to spawn a File Open dialog box from Excel ( no problem with that using GetOpenFilename ).

I think I am trying to achieve the same thing most people want when they open up a dialog, they want to choose a file, and normally this is the latest one of that type?

So say I have a C:\Temp dir full of .piv files and I want to grab the last created. I have to open the dialog, change to details and then click the Date Modified column and then this will be the view and set up I want.

I have to do this EVERY TIME, as do the users. Is there any way of setting the sort and the view type from Excel VBA.

开发者_如何学GoThe Chapter 9. stuff from Professional Excel Development gives me some hope, but doesn't get me all the way there.

So strange seeing as it must be a common thing for people to want to do?

There's no official way to do this. There are hacks but they are very hard to make work across a wide variety of Windows machines. Getting such hacks to work in VBA sounds exceedingly challenging.

The new file dialogs introduced in Vista remember settings like list view mode, which column was sorted and so on. I think Microsoft took note of the frustrations people had with the older versions when they designed the new versions.

Dude, you gotta get with FileDialog! This object offers way more flexibility than GetOpenFilename (and its sibling GetSaveAsFilename).

It will at least get you into Details view automatically. Example:

Dim fdgOpen As FileDialog
Set fdgOpen = Application.FileDialog(msoFileDialogOpen)
With fdgOpen
    .Title = "Please open a PIV file..."
    .InitialFileName = "C:\MyDocuments\MyDir\"
    .InitialView = msoFileDialogViewDetails ' Aha!!!
    .Show
End With

The above will save you the two clicks required to get to Details view. I don't know whether the last click on "Date modified" can be automated, but hey, at least you're 2/3 of the way.

See VBA help on FileDialog. It has quite a few useful properties.

For XP and Office 2010, msoFileDialogViewDetails works.

With my OS and office version (XP with Office 2007) as far as I can tell, it is not possible.