Trouble with variable type

[Ken McLennan]

G'day there Once Again,

I've received some great advice here on this list, most recently
from the list archives. It's amazing how much information is in there
that I didn't have to ask about specifically.

This latest problem of mine, though, doesn't seem to be addressed
specifically. Or if it is, then I've not used the correct terminology in
my searches and missed it. Hopefully, someone can point me in the right
direction.

I'm using the GetOpenFilename method to load a list of filenames
into a variable of type Variant:

Option Explicit

Sub Text_In()

Dim fileList As Variant
Dim x As Integer
Dim newSht As Worksheet
Dim UF As String, fName As String

'
' Text_In Macro
' Macro recorded by Ken McLennan
'

x = 1

fileList = Application.GetOpenFilename(, , , , True)

If CBool(fileList) <> False Then <----- ****
' Parse list and open worksheets for each file
Do
fName = Mid(fileList(x), InStrRev(fileList(x), "\") + 1)

Set newSht = Worksheets.Add
newSht.Name = fName

x = x + 1
Loop Until x = UBound(fileList) + 1
End If

' Code found in archives - I think by Tom Ogilvy

' Dim UF As String
' Dim FName As String
' FName = Mid(UF, InStrRev(UF, "\") + 1)
' Workbooks(FName).Activate

End Sub


I find that all works well up to the line with <----- ****.

The CBool() is only one of my attempts to get it to work. By
modifying that line I can have the routine either enter the selected
filenames, or pass the 'Cancel' option through to completion.

'fileList' becomes an array of strings if a file is selected (a
single element array if there's only one file), or a boolean containing
'false' if the file dialog is cancelled.

If I set the <----- **** line to work OK with one option, I get a
"Type Mismatch Error" for the other. I.e. if I get it accepting the
Boolean value, it won't work with the string, and vice versa.

How can I get it to accept either Boolean (for cancel) *and*
String (for selected files) data types?

I'm sure that it can be done, but it's giving me a headache.

 

[Ken McLennan]

G'day there Chip,

Use the IsArray function to test the fileList variable. If it

Geez... it's so obvious when you say it like that!!

Thanks for that Chip. Saved me from having to issue instructions
to users to never cancel a File Open dialog =).

Thanks very muchly,
Ken McLennan
Qld, Australia

 

[Chip Pearson]

Ken,

Use the IsArray function to test the fileList variable. If it
returns False, then fileList is not an array, and you can safely
assume that the user pressed Cancel. Otherwise, if IsArray
returns True, fileList contains an array of file names. E.g.,

Dim fileList As Variant
Dim Ndx As Long
fileList = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(fileList) = False Then
' user cancelled
Debug.Print "Cancel"
Else
For Ndx = LBound(fileList) To UBound(fileList)
Debug.Print fileList(Ndx)
Next Ndx
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com