Small problem with GetopenFileName

[Peter Rooney]

Good morning all!

I want to be able to let the user select multiple files from a
GetOpenFileName dialog box, but also filter the file list to Excel files only
and trap for the user pressing Escapel.

If I use:

SAPDataWorkbook = Application.GetOpenFilename()
If SAPDataWorkbook = False Then
Exit Sub
End If
Workbooks.Open Filename:=SAPDataWorkbook

I can press escape OK AND open a workbook

If however I change the first line thus:

SAPDataWorkbook = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)
If SAPDataWorkbook = False Then
Exit Sub
End If
Workbooks.Open Filename:=SAPDataWorkbook

to filter to *.xls I can press escape, but get the message "Type mismatch"
with

If SAPDataWorkbook = False Then

highlighted.

Can anyone out there help, please?

Thanks in advance

Pete

[NickHK]

I assume you have
Dim SAPDataWorkbook As String

If you read the help you will see "If MultiSelect is True, the return value
is an array of the selected file names (even if only one filename is
selected). "
Hence change it to: Dim SAPDataWorkbook As Variant

NickHK

"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Good morning all!
>
> I want to be able to let the user select multiple files from a
> GetOpenFileName dialog box, but also filter the file list to Excel files
only
> and trap for the user pressing Escapel.
>
> If I use:
>
> SAPDataWorkbook = Application.GetOpenFilename()
> If SAPDataWorkbook = False Then
> Exit Sub
> End If
> Workbooks.Open Filename:=SAPDataWorkbook
>
> I can press escape OK AND open a workbook
>
> If however I change the first line thus:
>
> SAPDataWorkbook = Application.GetOpenFilename( _
> filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)
> If SAPDataWorkbook = False Then
> Exit Sub
> End If
> Workbooks.Open Filename:=SAPDataWorkbook
>
> to filter to *.xls I can press escape, but get the message "Type mismatch"
> with
>
> If SAPDataWorkbook = False Then
>
> highlighted.
>
> Can anyone out there help, please?
>
> Thanks in advance
>
> Pete
>

[Peter Rooney]

Hi, Nick,

No, I thought of that - it's declared as Variant already :-(

Pete


"NickHK" wrote:

> I assume you have
> Dim SAPDataWorkbook As String
>
> If you read the help you will see "If MultiSelect is True, the return value
> is an array of the selected file names (even if only one filename is
> selected). "
> Hence change it to: Dim SAPDataWorkbook As Variant
>
> NickHK
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
> > Good morning all!
> >
> > I want to be able to let the user select multiple files from a
> > GetOpenFileName dialog box, but also filter the file list to Excel files
> only
> > and trap for the user pressing Escapel.
> >
> > If I use:
> >
> > SAPDataWorkbook = Application.GetOpenFilename()
> > If SAPDataWorkbook = False Then
> > Exit Sub
> > End If
> > Workbooks.Open Filename:=SAPDataWorkbook
> >
> > I can press escape OK AND open a workbook
> >
> > If however I change the first line thus:
> >
> > SAPDataWorkbook = Application.GetOpenFilename( _
> > filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)
> > If SAPDataWorkbook = False Then
> > Exit Sub
> > End If
> > Workbooks.Open Filename:=SAPDataWorkbook
> >
> > to filter to *.xls I can press escape, but get the message "Type mismatch"
> > with
> >
> > If SAPDataWorkbook = False Then
> >
> > highlighted.
> >
> > Can anyone out there help, please?
> >
> > Thanks in advance
> >
> > Pete
> >
>
>
>

[Bob Phillips]

SAPDataWorkbook = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)
If Not IsArray(SAPDataWorkbook) Then
MsgBox "Nothing selected"
Else
For i = LBound(SAPDataWorkbook) To UBound(SAPDataWorkbook)
Workbooks.Open Filename:=SAPDataWorkbook(i)
Next i
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Good morning all!
>
> I want to be able to let the user select multiple files from a
> GetOpenFileName dialog box, but also filter the file list to Excel files
only
> and trap for the user pressing Escapel.
>
> If I use:
>
> SAPDataWorkbook = Application.GetOpenFilename()
> If SAPDataWorkbook = False Then
> Exit Sub
> End If
> Workbooks.Open Filename:=SAPDataWorkbook
>
> I can press escape OK AND open a workbook
>
> If however I change the first line thus:
>
> SAPDataWorkbook = Application.GetOpenFilename( _
> filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)
> If SAPDataWorkbook = False Then
> Exit Sub
> End If
> Workbooks.Open Filename:=SAPDataWorkbook
>
> to filter to *.xls I can press escape, but get the message "Type mismatch"
> with
>
> If SAPDataWorkbook = False Then
>
> highlighted.
>
> Can anyone out there help, please?
>
> Thanks in advance
>
> Pete
>

[Peter Rooney]

Thanks a lot Bob! :-)

Pete


"Bob Phillips" wrote:

> SAPDataWorkbook = Application.GetOpenFilename( _
> filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)
> If Not IsArray(SAPDataWorkbook) Then
> MsgBox "Nothing selected"
> Else
> For i = LBound(SAPDataWorkbook) To UBound(SAPDataWorkbook)
> Workbooks.Open Filename:=SAPDataWorkbook(i)
> Next i
> End If
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
> > Good morning all!
> >
> > I want to be able to let the user select multiple files from a
> > GetOpenFileName dialog box, but also filter the file list to Excel files
> only
> > and trap for the user pressing Escapel.
> >
> > If I use:
> >
> > SAPDataWorkbook = Application.GetOpenFilename()
> > If SAPDataWorkbook = False Then
> > Exit Sub
> > End If
> > Workbooks.Open Filename:=SAPDataWorkbook
> >
> > I can press escape OK AND open a workbook
> >
> > If however I change the first line thus:
> >
> > SAPDataWorkbook = Application.GetOpenFilename( _
> > filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)
> > If SAPDataWorkbook = False Then
> > Exit Sub
> > End If
> > Workbooks.Open Filename:=SAPDataWorkbook
> >
> > to filter to *.xls I can press escape, but get the message "Type mismatch"
> > with
> >
> > If SAPDataWorkbook = False Then
> >
> > highlighted.
> >
> > Can anyone out there help, please?
> >
> > Thanks in advance
> >
> > Pete
> >
>
>
>