Error Handling - Where to enter 'Exit Sub'?

Question

I have the below code which simply opens a file. If there file does not exist in teh lcoation specified then I require an error message to appear. The trouble I am having with the below code is that when the file does exist it opens, enters 'Hello' into cell A1, however the MsgBox still appears. I think I have the Exit Sub in the wrong place??

Sub Test()

Dim Location As String
Dim File1 As String
Dim Err1 As String

On Error GoTo Err1
  Location = "S:\HRIS\Restricted\Information Services\Regular Reports\DRS _   
    Automation\" & Format(Date, "DD.MM.YYYY")
      File1 = "\Test.xlsx"
        Workbooks.Open FileName:=Location & File1

Range("A1").Value = "Hello"

Err1:
  MsgBox "Could not Locate " & Location & File1
Exit Sub

End Sub

You need an Exit Sub before Err1.... See also stackoverflow.com/a/23688126 — tanderson
– tanderson, Commented Oct 25, 2018 at 11:29

DisplayName · Accepted Answer · 2018-10-25 10:17:17Z

2

move Exit Sub before Err1 label:

Sub Test()

    Dim Location As String
    Dim File1 As String
    Dim Err1 As String

    On Error GoTo Err1
        Location = "S:\HRIS\Restricted\Information Services\Regular Reports\DRS Automation\" & Format(Date, "DD.MM.YYYY")
        File1 = "\Test.xlsx"
        Workbooks.Open Filename:=Location & File1

    Range("A1").Value = "Hello"

    Exit Sub

Err1:
      MsgBox "Could not Locate " & Location & File1

End Sub

answered Oct 25, 2018 at 10:17

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

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

1 Comment

Jamestm_PRE Over a year ago

I Realised as soon as I posted. Thanks for your help.

Collectives™ on Stack Overflow

Error Handling - Where to enter 'Exit Sub'?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related