Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    check files exist (2003)

    Hi,
    I need help with coding the following:
    I need to check that 6 excel files exist in the same location as this workbook before continuing. eg:
    Lets call them excel1 to 6. check if each file exists, if they do, call next sub routine, if not show messagebox 'file xyz does not exist, create file before continuing'.
    Thanks....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: check files exist (2003)

    You can use the following code as starting point:
    <code>
    Public Sub FileExists(strFullName As String) As Boolean
    FileExists = Not (Dir(strFullName) = "")
    End Sub

    Sub MyMacro()
    Dim strPath As String
    Dim strFile As String
    strPath = ThisWorkbook.Path
    If Not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If

    strFile = "Excel1.xls"
    If FileExists(strPath & strFile) = False Then
    MsgBox "Create " & strFile & " before continuing.", vbExclamation
    Exit Sub
    End If

    ' Similar for the other files
    ...
    End Sub</code>

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check files exist (2003)

    Thanks Hans, I will work on this and post back.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check files exist (2003)

    Help......
    Attached Images Attached Images

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: check files exist (2003)

    Nathan

    It will work if you change Hans' code slightly to:

    <pre>Function FileExists(strFullName As String) As Boolean
    FileExists = Not (Dir(strFullName) = "")
    End Function
    </pre>

    Jerry

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: check files exist (2003)

    The reason you cannot see strFile is because it has a NULL value at the time of constructing the msg. Later you construct a value for strFile so the addition of strFile can be place by further constructing the msg.

    The attached text file should clarify this
    Jerry

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check files exist (2003)

    Thanks Jerry, it works as you said it would.

    I have modified the code (as per the attached) which works as I would like, except the MSGBOX which is the same for each error EXCEPT for strFile. The message halts at strPath and does not show strFile. Maybe I need to use different messages as part of each IF?

    Thanks for assisting.

    Added later: I have updated the attachment, to add code that works, but is long winded, and am wondering if there is a shorter way?
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check files exist (2003)

    Thanks Jerry, as per my edit, you've come up trumps!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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