Excel VBA Open File (error handling)

Question

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?

If you would have pressed the magic key F1 in Excel you would have got your answer :) — Siddharth Rout
– Siddharth Rout, Commented Aug 14, 2012 at 15:28

Siddharth Rout · Accepted Answer · 2012-08-14 15:41:41Z

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

edited Aug 14, 2012 at 15:41

answered Aug 14, 2012 at 15:33

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SeanC · Accepted Answer · 2012-08-14 15:35:54Z

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)

edited Aug 14, 2012 at 15:35

answered Aug 14, 2012 at 15:25

SeanC

16k5 gold badges48 silver badges67 bronze badges

8 Comments

Siddharth Rout Over a year ago

Application.GetOpenFilename returns a variant

SeanC Over a year ago

@siddharthRout: changed explanation of what happens in FancyCorndog's code

Siddharth Rout Over a year ago

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.

SeanC Over a year ago

@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

Siddharth Rout Over a year ago

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

|

Scott Holtzman · Accepted Answer · 2012-08-14 15:22:15Z

1

Change

If fn = False Then

To

If CStr(fn) = "False" Then

answered Aug 14, 2012 at 15:22

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

2 Comments

Jean-François Corbett Over a year ago

Why do you coerce a string to a string? Just write If fn = "False" Then

Scott Holtzman Over a year ago

@Jean-FrançoisCorbett. Thanks. Good point. When I was playing with it, I didn't test that out.

Collectives™ on Stack Overflow

Excel VBA Open File (error handling)

3 Answers 3

Comments

8 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related