1

I'm trying to account for a case when the user does not find a file to open:

Dim fn As String
fn = Application.GetOpenFilename("All Files,.", 1, "Select a file", , False)

If fn = False Then
    Exit Sub
End If

This does what I want it to when a file is not chosen.

But when the user does choose a file, this If statement creates an error. Can anyone tell me the proper way to accomplish this?

SeanC
  • 15,695
  • 5
  • 45
  • 66
Neat Machine
  • 681
  • 4
  • 11
  • 18

3 Answers3

3

If you check Excel's inbuilt help, you will notice that Application.GetOpenFilename returns a variant. So all you have to do is declare fn as Variant,

Sub Sample()
    Dim fn As Variant

    fn = Application.GetOpenFilename("All Files,.", 1, "Select a file", , False)

    If fn = False Then Exit Sub

    MsgBox fn
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
2

The function does not return the boolean False, but the string "False", as it will cast the variant from the function into your variable type, so your test should be

If fn = "False" Then

(changed explanation - thanks @SiddharthRout)

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • 1
    `Application.GetOpenFilename` returns a variant – Siddharth Rout Aug 14 '12 at 15:31
  • @siddharthRout: changed explanation of what happens in FancyCorndog's code – SeanC Aug 14 '12 at 15:37
  • 1
    Sean. I don't mean to be picky but this statement `The function does not return the boolean False, but the string "False"` is incorrect :( When nothing is selected it does return a boolean else it returns a string and that is the reason why we declare the variable `fn` as variant. – Siddharth Rout Aug 14 '12 at 15:39
  • @SiddharthRout, the function as written will cause the return value to be cast into a string. The test of `"False"=False` evaluates as true, so the test works on a cancel, but fails when comparing anything other than a string that can be interpreted as a boolean – SeanC Aug 14 '12 at 15:46
  • Try: `Debug.Print "True" = True, "False" = False, "0" = False, "-1" = True` and see how many `True` results you get – SeanC Aug 14 '12 at 15:47
  • I understand that part but if you check the Excel's help `This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box.` We don't need to handle the output per se. If we declare the variable as variant then Excel automatically handles it appropriately. – Siddharth Rout Aug 14 '12 at 15:51
  • 1
    I am not saying that your method is incorrect. I am just saying that the statement `he function does not return the boolean False, but the string "False"` is incorrect. It does return a boolen in case of Cancel which can be converted to a "String" like you have done :) – Siddharth Rout Aug 14 '12 at 15:56
  • @Fancydog: Seems like you didn't get my point. You have declared `Dim fn As String` Now try this `fn = Application.GetOpenFilename("All Files,.", 1, "Select a file", , True)` instead of `fn = Application.GetOpenFilename("All Files,.", 1, "Select a file", , False)` Notice the `Multiselect` is set to true. What happens? – Siddharth Rout Aug 14 '12 at 16:01
1

Change

If fn = False Then

To 

If CStr(fn) = "False" Then
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72