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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,118,808
Messages
5,574,433
Members
412,592
Latest member
moonsugar
Top