The GetOpenFilename stuff

grautu

New Member
Joined
Sep 26, 2004
Messages
38
Hi!
My thread is asking not for a valid code but for any hint/comment for properly understanding the GetOpenFilename method in VBA. The following code does work very well but please notice its second line (Dim MY_FILE_NAME ...) is actually commented out (it will be clear later on why).

Code:
Sub Test_GetOpenFilename()
'   Dim MY_FILE_NAME as ???
    MY_FILE_NAME = Application.GetOpenFilename(FileFilter:="Excel (*.xls), *.xls", Title:="Select File")
    If MY_FILE_NAME <> False Then
                ' Here is a completely mind bogging issue: if choosing "=True" instead of "<>False",
                ' then the code will fail to work as expected!!!
       Workbooks.Open Filename:=MY_FILE_NAME
       ThisWorkbook.Sheets(1).Cells(1, 1).Value = MY_FILE_NAME  'Just for seeing
                                                                'how the concrete 
                                                                'value of MY_FILE_NAME
                                                                'looks like
    Else
       MsgBox "No file selected"
    End If
End Sub

REMARKS
-------
(1) The above macro does work and its output (at cells (1,1)) is the full path to the selected filename if the case. See the command ThisWorkbook.Sheets(1).Cells(1, 1).Value = MY_FILE_NAME
(2) If questioning above
Code:
If MY_FILE_NAME = True
instead of ... <> False, then the code wil fail to work properly in that only the Else branch will allways be chosen. Why? I have no idea.
(3) Furthermore, whenever trying to activate the above command
Code:
Dim MY_FILE_NAME as ???
with various ??? instances, then my code was always failing to work: that ??? may not be Boolean or String etc etc. Most likely it must be an Object but I was not able to learn which one, concretely.
==================

QUESTIONS
---------
(1) Is there any valid choice for ??? at Dim MY_FILE_NAME as ??? above?
(2) How to explain the fact that the intuitively correct variant MY_FILE_NAME = True fails to work as expected?
==================

Any comment is kindly appreciated, grateful thanks in advance.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
GetOpenFileName returns the selected file name or the name entered by the user. The returned name may include a path specification. It returns False if the user cancels the dialog box.

So what's returned can be a String or a Boolean (False, but never True) and you need:

Dim MY_FILE_NAME as Variant
 

grautu

New Member
Joined
Sep 26, 2004
Messages
38
It's for the first time I understand what the Variant type is designed to help to. Thanks a lot!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,794
Messages
5,544,322
Members
410,603
Latest member
rseckler
Top