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

On Oct 28, 12:52*pm, joel <joel.40q...@thecodecage.com> wrote:
> You are missing the asterisk before the ".xls"
>
> from
> ".xls"
> to
> "*.xls"
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229
> View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=148503

D'oh!! Thanks joel

I think I'd try something like this

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

"geoff_ness" wrote:

> 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
> .
>

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.


--
Regards,

OssieMac


"geoff_ness" wrote:

> 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
> .
>

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

On Oct 28, 3:35*pm, OssieMac <Ossie...@discussions.microsoft.com>
wrote:
> 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.
>
> --
> Regards,
>
> OssieMac
>
>
>
> "geoff_ness" wrote:
> > 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
> > .- Hide quoted text -
>
> - Show quoted text -