If file doesn't exist exit vba

[muhleebbin]

Hi,

I have the following code where it opens a file and then copies sheets etc.:

Sub CopyComparison()

    Application.DisplayAlerts = False
   
    Dim filename As String
    filename = ThisWorkbook.Path & Application.PathSeparator & "Scheduler_Reports v2.xlsx"
   
    Dim wk As Workbook
    Set wk = Workbooks.Open(filename, ReadOnly:=True)

'code

End Sub

Because Scheduler Reports v2 will not be created until later in the month, can someone help with the code to exit this macro if the file is not there but if it is continue on?

I tried this thread but couldn't seem to get it to work

if file does not exist then exit VB

Hi, I have a peice of code which opens up to ten work books and manipulates data. What i need is a piece of code to put at the start which will check for the existance of each file BEFORE the rest of the code executes, if it does not exist it need to error message and exit the code at that...

www.mrexcel.com

Thank you in advance for your assistance!

 

[DanteAmor]

Try this:

Sub CopyComparison()

    Application.DisplayAlerts = False
   
    Dim wk As Workbook
    Dim filename As String
    
    filename = ThisWorkbook.Path & Application.PathSeparator & "Scheduler_Reports v2.xlsx"
       
    If Dir(filename) = "" Then
      MsgBox "File does not exists"
      Exit Sub
    End If
    Set wk = Workbooks.Open(filename, ReadOnly:=True)

'code

End Sub

 

[bobsan42]

If Len(Dir(filename))=0 then exit sub

 

[dmt32]

Hi,
one way maybe

Sub CopyComparison()
    Dim wk       As Workbook
    Dim filename As String
    
    filename = ThisWorkbook.Path & Application.PathSeparator & "Scheduler_Reports v2.xlsx"
   
   If Not Dir(filename, vbDirectory) = vbNullString Then
    
        Set wk = Workbooks.Open(filename, ReadOnly:=True)

'code
    Else
    
        MsgBox filename & Chr(10) & "File Not Found", 48, "Not Found"
    
    End If
End Sub

Dave

 

[muhleebbin]

Thank you all for the help and suggestions! I got all three to work but I did have to put filename in "" for it to work.

Thank you all again for your help, it is much appreciated!!!

 

[muhleebbin]

Oops spoke too soon.

I just tried running a test when it does exist and it's still saying that the file does not exist. Can you help with that?

Thanks again!

 

[bobsan42]

Remove the quotes around filename.
It is a variable name, not a literal string.

 

[muhleebbin]

Without the quotes around filename it runs into an error on the "If Dir(filename) = "" Then" line even when there is no file.

 

[muhleebbin]

It says 'Bad file name or number'

 

[bobsan42]

This means that the filename path is wrong and does not comply with the standards.
What is the value of filename at the moment of error.