+ Reply to Thread
Results 1 to 2 of 2

Check if files exist in a formula

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-02-2007
    Location
    Leeds, England
    MS-Off Ver
    MS Office 2007
    Posts
    134

    Check if files exist in a formula

    I have been getting help on the MS forum about how to check a folder for files with a particular name. So far I have go to this:

    Sub Checks()
    
    Dim myNames As Variant
    Dim wkbk As Workbook
    Dim myPath As String
    Dim iCtr As Long
    Set something = Application.FileDialog(msoFileDialogFolderPicker)
    MsgBox "Select the SystmOne GMS Files (Originals)", vbInformation
    something.Show
    somethingpath = CurDir()
    
    myNames = Array("WORKBOOKONE.xls", "WORKBOOKEIGHT.xls", "WORKBOOKNINE.xls")  'you'd type in all 24 of those names
    
    myPath = somethingpath 'or whatever you used to get the path
    If Right(myPath, 1) <> "\" Then
      myPath = myPath & "\"
    End If
    
    For iCtr = LBound(myNames) To UBound(myNames)
    
    Set wkbk = Workbooks.Open(Filename:=myPath & myNames(iCtr))
       
       'do stuff with wkbk
       wkbk.Close savechanges:=False 'or true??
       
    Next iCtr
    
    End Sub
    When a file is found, the script moves to the next file. But when one is not present in a folder, the macro stops. Rather than stopping/crashing, I would like a message box to show with a message e.g. "WORKBOOKNINE.xls Not Found". Then once the user clicks ok it will look for the next file.

    Can anyone help?

    Cheers
    dvent

  2. #2
    Forum Contributor
    Join Date
    11-02-2007
    Location
    Leeds, England
    MS-Off Ver
    MS Office 2007
    Posts
    134
    Result:

    Option Explicit
    Sub Checks()
    
        Dim myNames As Variant
        Dim wkbk As Workbook
        Dim myPath As String
        Dim iCtr As Long
        
        MsgBox "Select the SystmOne GMS Files (Originals)", vbInformation
        
        With Application.FileDialog(msoFileDialogFolderPicker)
            ' Optional: set folder to start in
            .InitialFileName = "C:\my documents\excel\"
            .Title = "Select the folder to process"
            If .Show = True Then
                myPath = .SelectedItems(1)
                'add trailing backslash
                myPath = myPath & "\"
            Else
                MsgBox "Try later!"
                Exit Sub
            End If
        End With
        
        myNames = Array("WORKBOOKONE.xls", _
                        "WORKBOOKEIGHT.xls", _
                        "WORKBOOKNINE.xls")    
        
        For iCtr = LBound(myNames) To UBound(myNames)
            Set wkbk = Nothing
            On Error Resume Next
            Set wkbk = Workbooks.Open(Filename:=myPath & myNames(iCtr))
            On Error GoTo 0
            
            If wkbk Is Nothing Then
                MsgBox myPath & myNames(iCtr) & " was not opened!" & vbLf & _
                    "Maybe it doesn't exist???"
            Else
                'do stuff with wkbk
                MsgBox wkbk.Worksheets(1).Range("a1").Text
                wkbk.Close savechanges:=False 'or true??
           End If
        Next iCtr
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1