Find file path and run if the path is valid

[sathyaganapathi]

Hi, I want to run a vba macro only if the file path is valid. if not, it should give a message saying "the file opened is from wrong part. please check again".
Could somebody please help with VBA code ?
Thanks. sathyaganapathi

 

[Joe4]

Welcome to the Board!

Check out the nice little custom function that someone created here: Excel VBA Check if directory exists error

 

[sathyaganapathi]

Hi Joe4, thanks for quick reply.
Unfortunately, I am not expert in excel VBA to understand the details available in the link you gave.
To be more specific on my requirement, I give more details now.
I have a excel file for example in folder "E:\example" and the file name is "test.xlsm" which contains a macro.
there is a copy of "test.xlsm" in other folder as an archive copy. for example "E:\example\archive". "E:\example\archive\test.xlsm".
But the macro should work if the file from "E:\example" is opened and should not work if opened from E:\example\archive" folder.
Macro should work for file ""E:\example\test.xlsm"
Macro should not work for file "E:\example\archive\test.xlsm".
Hope, I could explain it.

example macro:
Sub testmacro()
' testmacro Macro
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.FormulaR1C1 = "=NOW()"
End Sub

 

[Joe4]

OK, that is a bit different. The path is "valid" (an "invalid" path would be one that does not exist!), its just not the one you want.

Try this:

Sub testmacro()

' testmacro Macro

    If InStr(ActiveWorkbook.Path, "Archive") > 0 Then
        MsgBox "Cannot run on files in Archive folder", vbOKOnly
        Exit Sub
    Else
        ActiveCell.FormulaR1C1 = "=TODAY()"
        ActiveCell.FormulaR1C1 = "=NOW()"
    End If

End Sub

 

[sathyaganapathi]

Hi Joe4, great.... ! it worked !!. Is there any possibility to make reverse? Means, if the path is "E:\example" the macro should work. If not, should not work.
In the above macro, If I am correct, it check for "archive" folder and if true it stops. right?
sorry, I missed to say that, macro should not work if file is in any other folder.
Thanks.

 

[sathyaganapathi]

I have changed the macro as below. But, it runs in both the folders. something is not ok..


Sub testmacro()

' testmacro Macro

If InStr(ActiveWorkbook.Path, "E:\example") > 0 Then
Range("a1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Exit Sub
Else
MsgBox "Cannot run on files from other folder", vbOKOnly
End If

End Sub

 

[Joe4]

Hi Joe4, great.... ! it worked !!. Is there any possibility to make reverse? Means, if the path is "E:\example" the macro should work. If not, should not work.
In the above macro, If I am correct, it check for "archive" folder and if true it stops. right?
sorry, I missed to say that, macro should not work if file is in any other folder.
Thanks.

That makes it even easier.

Try:

Sub testmacro()

' testmacro Macro

    If ActiveWorkbook.Path = "E:\example" Then
        Range("A1").FormulaR1C1 = "=TODAY()"
        Range("B1").FormulaR1C1 = "=NOW()"
    Else
        MsgBox "Cannot run on files in this folder", vbOKOnly
        Exit Sub
    End If

End Sub

 

[sathyaganapathi]

Now, It is perfect. Thanks Joe4 for your instant and grate help. It made my day..
I will apply this to my original macro.
Thanks again.. bye..

 

[Joe4]

You are welcome.
Glad I was able to help!