Small problem with GetopenFileName

P

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
 
N

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
 
B

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)
 
P

Peter Rooney

Thanks a lot Bob! :)

Pete


Bob Phillips said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top