GetOpenFilename - with a difference

[michael.beckinsale]

Hi All,

Just wanted to know if there is a method to filter the list of files
shown in the dialog to only those containing certain text in the
filename?

As an example when l use the GetOpenFilename function l would like
like to display only .xls files containing TBREC-WK.

I know how to do the .xls bit but cany seem to get the next bit right.

All ideas greatfully received.

Regards

Michael Beckinsale

 

[RichardSchollar]

Hi Michale

I don't think you can do this with GetOpenFileName, but you can with
the FileDialog object:

Sub test()
Dim fname As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel files (*.xls)", "*.xls", 1
.FilterIndex = 1
.Title = "Open Excel File"
.InitialFileName = "*TBREC-WK*.xls"
If .Show = True Then Workbooks.Open .SelectedItems(1)
End With
End Sub

I think this will default to whatever your current directory is (so
you may want to set that first), alternatively, if you know the exact
folder you want, then you can specify it in the InitialFileName
property eg:

..InitialFileName = "C:\MyFolder\*TBREC-WK*.xls"

I hope this helps!

Richard

 

[michael.beckinsale]

Hi Richard,

Thanks for the info.

I will give it a try and get back to you.

Regards

Michael Beckinsale

 

[michael.beckinsale]

Hi Richard,

Tried the code / idea that you offered and it works a treat.

I think l will probably use this method in preference to the
'GetOpenFilename' in many of my applications.

Again, many thanks.

Regards

Michael Beckinsale

 

[Tom Ogilvy]

As long as you recognize it didn't exist before xl2002 - so if your users all
have xl2002 and later, should work fine.

 

[michael.beckinsale]

Hi Tom,

Thanks for the info.

I think i'm OK on this occasion but l will have to check.

I'm really glad you mentioned it.

Is there a solution / workaround for previous versions?

Regards

Michael Beckinsale

 

[Tom Ogilvy]

You can use the windows API:

http://www.dailydoseofexcel.com/archives/2004/06/09/getopenfilename/

see Jamie Collin's second response with a link to a post by Bob Phillips.

 

[michael.beckinsale]

Hi Tom,

Again may thanks. It must have taken a while to track down Bob's
example and l really do appreciate it.

His example is a complex bit of coding!

I take it l can copy & paste it into my workbook, and just edit as
necessary?

Does the code have to be placed in any specific modules?

I will give it a try

Regards

Michael beckinsale

 

[Tom Ogilvy]

Yes, - at least large portions of it have to be in a class module (and named)
as he describes.

I took the liberty of putting it in an xls and sending it to you since it is
kind of complex and needed some cleanup for word wrap. (and Bob had the typo
on what the class name should be named.)