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!

 

[muhleebbin]

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

Is it because it’s on a sharepoint drive?? When I hover over the file name I cannot see the full file name other than: "https://test-my.sharepoint.com/personal/muhleebbin...."

 

[bobsan42]

You cannot use Dir with online files.
Btw, to see a variable's value in debug mode go to the immediate window and type:
?filename
then press enter.

 

[dmt32]

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!

If Dir Function not suitable for your requirement then try following as an alternative approach & see if any help

Sub CopyComparison()
    Dim wk              As Workbook
    Dim filename        As String
    
    filename = ThisWorkbook.Path & Application.PathSeparator & "Scheduler_Reports v2.xlsx"

    On Error GoTo myerror
    
    Set wk = Workbooks.Open(filename, ReadOnly:=True)
        
        'code

myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[bobsan42]

if you have an online file, the separator that should be used is "/". If you are on Windows then Application.PathSeparator is "\".
So you will get the same error as before. So you have to hardcode the correct path separator.
And I would write the code from dmt32 like this:

Sub CopyComparison()
    Dim wk              As Workbook
    Dim filename        As String
   
    filename = ThisWorkbook.Path & "/" & "Scheduler_Reports v2.xlsx"

    On Error Resume next
    Set wk = Workbooks.Open(filename, ReadOnly:=True)
    doevents
    if wk is nothing then exit sub
    on error goto 0

........ the rest of your code goes here

End Sub

 

[muhleebbin]

dmt32/bobsan42 - Thank you very much for this solution! This worked with the sharepoint constraint I have. Oddly enough the filename had / within the website portion but just before Scheduler_Reports v2 it was a \.

I marked DanteAmor's answer as the solution as that seems more likely to be the solution for others (if that goes against mrexcel etiquette, please let me know and I will change the solution) but the answers you both provided are definitely the answers when a sharepoint site is involved.

Thank you again for your help here and have a great weekend!

 

[bobsan42]

dmt32/bobsan42 - Thank you very much for this solution! This worked with the sharepoint constraint I have. Oddly enough the filename had / within the website portion but just before Scheduler_Reports v2 it was a \.

I marked DanteAmor's answer as the solution as that seems more likely to be the solution for others (if that goes against mrexcel etiquette, please let me know and I will change the solution) but the answers you both provided are definitely the answers when a sharepoint site is involved.

Thank you again for your help here and have a great weekend!

Glad we could help.