none
VBA Check if file exist in a folder RRS feed

  • Question

  • Hi All,

    I am using below code to check a folder having multiple files. If any file name has any of string from the list of strings than run some code.

    Sub LoopThroughFiles()
    Application.ScreenUpdating = False
       
    Set MyObj = CreateObject("Scripting.FileSystemObject")
    Set MySource = MyObj.GetFolder("H:\Foldername\")
    Myarray = Array("NY", "NJ", "CA", "PA", "OR", "IL")
      For Each item In Myarray
        For Each file In MySource.files
            Myfile = file
        If Myfile Like "*" & item & "*" Then
            Workbooks.Open (file)
            StrOpenWb = ActiveWorkbook.Name
            If Myfile Like "*NY*" Then Call GetNYData
            If Myfile Like "*NJ*" Then Call GetNJ_Data
            If Myfile Like "*CA*" Then Call GetCA_DATA
                    
        End If
        Next
      Next
      Application.ScreenUpdating = True
       
    End Sub

    I need to pop up a message box if no file name has string listed in the array. So if no file is open using below code used in my above code than show message box

    Workbooks.Open (file)


    Thanks,

    Zav



    • Edited by zaveri cc Monday, July 11, 2016 7:01 PM
    Monday, July 11, 2016 7:00 PM

Answers

  • Hi zaveri cc,

    please try to update your code as I mentioned below.

    Sub LoopThroughFiles()
    Application.ScreenUpdating = False
       Dim MyObj As Object
       Dim MySource As Object
       Dim Myarray(), StrOpenWb As String
       Dim Item, File, Myfile As Variant
    Set MyObj = CreateObject("Scripting.FileSystemObject")
    Set MySource = MyObj.GetFolder("C:\Users\v-padee\Desktop\folreceive\")
    Myarray = Array("NY", "NJ", "CA", "PA", "OR", "IL")
      For Each Item In Myarray
        For Each File In MySource.Files
            Myfile = File
        If Myfile Like "*" & Item & "*" Then
            Workbooks.Open (File)
            StrOpenWb = ActiveWorkbook.Name
            If Myfile Like "*NY*" Then Call GetNYData
            If Myfile Like "*NJ*" Then Call GetNJ_Data
            If Myfile Like "*CA*" Then Call GetCA_DATA
        Else
        MsgBox ("There is no files in a folder that match the characters from the list")
     GoTo LastLine
        End If
        Next
      Next
      Application.ScreenUpdating = True
    LastLine:
    End Sub

    the other thing I had notice that it is checking the file one by one. so if there is no match then it will give you message every time. so I put the Goto statement. so that it will exit the loop and next it will not check the further but if you want to check further then please remove the goto statement from the code then it will check every file and give you message.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, July 12, 2016 3:16 AM
    Moderator