Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: VBA Alternative To Application.FileSearch

  1. #1
    Join Date
    25th April 2012
    Posts
    2

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    10,362

    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)

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th April 2012
    Posts
    2

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

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

    This page may help: Alternative To File Search

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Application.filesearch In Office 2007
    By richphillips in forum EXCEL HELP
    Replies: 4
    Last Post: June 29th, 2013, 02:43
  2. Application.FileSearch In Excel 2010
    By criciuma in forum Excel and/or Access Help
    Replies: 2
    Last Post: June 21st, 2011, 23:26
  3. Application.filesearch
    By Scotchjim in forum EXCEL HELP
    Replies: 3
    Last Post: June 17th, 2011, 06:16
  4. Application.FileSearch alternative?
    By Bryan021 in forum EXCEL HELP
    Replies: 2
    Last Post: May 13th, 2011, 23:20
  5. Application.filesearch
    By parrimin in forum EXCEL HELP
    Replies: 5
    Last Post: June 28th, 2007, 22:57

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
  •  
porno