Announcement

Collapse
No announcement yet.

VBA Alternative To Application.FileSearch

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Alternative To Application.FileSearch



    Hi there,

    I am rather new working with VBA coding etc. and receveid a file containing a macro which is a bit over my head. After searching and reading about the filesearch app and the Dir function, I still do not understand the coding in this file.

    Could someone please explain what this code exactly does and perhaps help me with converting this code to the dir, so this macro can be used in 2010?

    This is the code:
    Code:
    Sub Open_Before_VA_Files()
        Status_bar = "Collecting VA Info (Before). "
        Application.StatusBar = Status_bar & "Locating & Opening VA Files..."
        Folder_B = SU.Cells.Find("   Before", lookat:=xlWhole, LookIn:=xlFormulas).Offset(, 1)
        If Not Right(Folder_B, 1) = "\" Then Folder_B = Folder_B & "\"
         
            Set File_Range = Search_Range.Find("="" File Name ""&", lookat:=xlPart, LookIn:=xlFormulas)
            Set C2 = Search_Range.Find("="" File Name ""&", lookat:=xlPart, LookIn:=xlFormulas)
            Set c1 = Search_Range.Find("="" File Name ""&", lookat:=xlPart, LookIn:=xlFormulas, after:=File_Range)
            Set File_Range = Range(File_Range.Offset(1), File_Range.Offset(NR_EN))
            Do Until c1.Address = C2.Address
                Set File_Range = Union(File_Range, Range(c1.Offset(1), c1.Offset(NR_EN)))
                Set c1 = Search_Range.Find("="" File Name ""&", lookat:=xlPart, LookIn:=xlFormulas, after:=c1)
            Loop
           
            nr_Files_B = UniqueItem(File_Range, 0)
            For i = 1 To nr_Files_B
                FileNames_B = UniqueItem(File_Range, i)
                Application.FileSearch.LookIn = Folder_B
                Application.FileSearch.Filename = FileNames_B
                If Application.FileSearch.Execute = 0 Then
                    MsgBox "The following file is not in the specified folder:" & Chr(10) & Folder_B & FileNames_B & Chr(10) & "Please check File Name and Location." & Chr(10) & Chr(10) & "Macro will not load any Before data, existing data will remain!"
                    JumpToAfter = True
                End If
            Next i
            For i = 1 To nr_Files_B
                FileNames_B = UniqueItem(File_Range, i)
                Workbooks.Open Folder_B & FileNames_B, ReadOnly:=True
            Next i
    End Sub
    Thanks in advance!
    OPM

  • #2
    Re: vba application.filesearch convert to Dir() function

    I haven't bothered to try and understand the logic of what this is doing, but you have asked about the missing FileSearch functionality...
    Change the lines:
    Code:
            Application.FileSearch.LookIn = Folder_B 
            Application.FileSearch.Filename = FileNames_B 
            If Application.FileSearch.Execute = 0 Then
    To

    Code:
       
       If Right$(Folder_B) <> "\" Then
          Folder_B = Folder_B & "\"
       End if
    
       If Dir$(Folder_B & FileNames_B) = vbNullString Then
          '// Rest of code

    The changes are:
    1. - Check if Folder_B ends with a '\'. If not, add it. Needed for DIR
    2. - Use the DIR Function rather than Filesearch. Dir returns a null string if the file does not exist.

    (Written freehand and untested - but it should work)

    Comment


    • #3
      Re: vba application.filesearch convert to Dir() function

      Hi cytop,

      Thanks you for your help, however I replaced the code as you said and got an error on this line:

      If Right$(Folder_B) <> "\" Then

      Any idea what could go wrong there?

      OPM

      Comment


      • #4


        Re: vba application.filesearch convert to Dir() function

        This page may help: Alternative To File Search

        Comment

        Working...
        X