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]

Hi, Nick,

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

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]

Thanks a lot Bob!

Pete

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)