Application.FileDialog .Filters.Add method not working as expected

[geoff_ness]

Hello

I have used the following in Excel 2007 to allow a user to select a
file:

Dim fdObj As FileDialog

Set fdObj = Application.FileDialog(msoFileDialogOpen)
With fdObj
.AllowMultiSelect = False
.Title = gsAPP_NAME
.Filters.Clear
.Filters.Add "Consolidation Reports", ".xls"
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
.Execute
End If
End With

Set fdObj = Nothing

Fairly standard I would have thought, but this breaks on the line with
the .Filters.Add method, with error message "Invalid procedure call or
argument". What am I missing here?

Cheers
Geoff

 

[joel]

You are missing the asterisk before the ".xls"


from
".xls"
to
"*.xls

 

[geoff_ness]

You are missing the asterisk before the ".xls"

from
".xls"
to
"*.xls"

D'oh!! Thanks joel

 

[Barb Reinhardt]

I think I'd try something like this

..Filters.Add "Consolidation Reports*" & ".xls"

 

[OssieMac]

Hi Geoff,

Did you really get the answer you were looking for? If you did then
disregard the following.

I thought that Filters referred to the actual list of available file types
that you select from the dropdown if you open the filedialog box in the
interactive mode.

If you want all file names that start with "Consolidation Reports" then you
should set InitialFileName

..InitialFileName = "Consolidation Reports*.xls"

You can also prefix the filename with the path otherwise it uses the current
directory.

 

[geoff_ness]

Thanks Ossie
Yes I did - but thanks for the suggestion. In this instance the
filenames in question don't start with the string "Consolidation
Reports", although I can see how you and Barb both thought that from
the code I posted. Filtering on the .xls extension is sufficient to
get what's needed from this directory (which I did eventually specify
using the .InitialFileName property), as these reports will be the
only xls files in there. Here's the finished code:

Public Sub RetrieveConsolidation()
' pre: None
' post: Consolidation report workbook opened
Dim fdObj As FileDialog

InitGlobals

Set fdObj = Application.FileDialog(msoFileDialogOpen)
With fdObj
.AllowMultiSelect = False
.Title = gsAPP_NAME
.Filters.Clear
.Filters.Add "Consolidation Reports", "*.xls"
.InitialView = msoFileDialogViewDetails
.InitialFileName = gsAppDir
If .Show = -1 Then
.Execute
End If
End With

Set fdObj = Nothing

End Sub

Cheers
Geoff