Check for file before executing renainder of marco

Trag

Board Regular
Joined
Dec 18, 2003
Messages
148
I'm trying to figure out how I can check to see if my file exists before my macro continues but I'm having difficulties.

Sub Check_To_See_If_File_Exists()
If Dir("C:\Documents and Settings\Trag\Desktop\Test.xls") = "" Then
MsgBox(vbOKOnly, "NO FILE EXISTS")
End
End If
Workbooks.Open Filename:="C:\Documents and Settings\Trag\Desktop\Test.xls"

'The rest of my macro goes here...

End Sub

Could someone be so kind to tell me where I'm going wrong? I'm a little lost but I've tried.

I need to have my marco check to see if the file exists before it can go on. If it can't go one, msgbox "No file Exists", macro stops.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi Trag, I can see you have a syntax error on the message box line. You only include brackets if your assigning the result of the message box to a variable.

You can just enter like this without the brackets...
MsgBox "NO FILE EXISTS"
You dont need vbokonly as thats the default button that appears.

An example of using the brackets is here where I want to know whether you pressed yes or no to determine which message to show.

Sub test()
Dim Answer
Answer = MsgBox("Are you happy?", vbYesNo)

If Answer = vbYes Then
MsgBox "Good for you!"
Else
MsgBox "Cheer up"
End If

End Sub

regards
parry
 
Upvote 0
Your code looks OK to me. Perhaps you have mis-spelled the path ?
Try checking what is actually returned by the function. Something like :-

Code:
x=Dir("C:\Documents and Settings\Trag\Desktop\Test.xls")
Msgbox(x)
 
Upvote 0
Parry was right, I had this part wrong...

MsgBox(vbOKOnly, "NO FILE EXISTS")

it should have been

MsgBox "NO FILE EXISTS"

It works just fine now.

Thanks for the help guys! (y)
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top