Calling Multiple files and exiting if file doesn't exist

[Ananthak275]

My VBA script calls multiple macros, one of them is to open a file and if one of them doesn't exists then exits the sub. The following macros are dependent on the first macro calling the file location, and even if that sub exits, the following macros still run.

Sub Call_Process ()
Call File_Location
Call Open_File
Call Exit_WB
End Sub

Sub File_Location ()
location_db = "C:\Users\Documents\New folder\*.xl??"
End Sub ()

Sub Open_File ()
dim wb as Workbook
dim ws as Worksheet

if dir(location_db) = "" <- as you can see if the file doesnt exists, it exits out of Open_File sub but not the Exit_WB when im running the Call_Process
end sub
end if

wb_filename = wb.name
sheet_name = ws.name
End Sub


Sub Exit_WB
Workbooks(location_db).close savechanges=TRUE
End Sub

 

[Marc L]

​

As a Sub procedure can have only a unique End Sub codeline so you must remove the one within the If … End If !​

​

In fact you need an Exit Sub codeline …​

 

[Ananthak275]

​

As a Sub procedure can have only a unique End Sub codeline so you must remove the one within the If … End If !​

Yeah it exits out of that Macro, but when im using the Sub Call_Process (), it will move onto the next one. How do i stop it from going to the next macro if the file doesnt exist?

 

[Marc L]

​

Transform each Sub to Function returning for example True of False whatever in order to test if the next one should be launched​

or as it is just use a public variable in order to check it before launching the next Sub …​

​

Or the obviously obvious : use a single procedure rather than multiple Sub !​