Check for file before executing renainder of marco

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Check for file before executing renainder of marco

  1. #1
    Board Regular
    Join Date
    Dec 2003
    Location
    Vancouver
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Check for file before executing renainder of marco

     
    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.

  2. #2
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check for file before executing renainder of marco

    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

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check for file before executing renainder of marco

    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)
    Regards
    BrianB (using XL2003 & 2010)
    www.cycleofgrowth.com
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  4. #4
    Board Regular
    Join Date
    Dec 2003
    Location
    Vancouver
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check for file before executing renainder of marco

      
    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!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com