CODEWITHSUNDEEP
excelvbaerror-handling
Jamestm_PRE
Jamestm_PRE

Reputation: 15

Error Handling - Where to enter 'Exit Sub'?

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

Upvotes: 0

Views: 373

Answers (1)

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 2

Related Questions