Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:
    VB:
    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
    7,335

    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:
    VB:
    Application.FileSearch.LookIn = Folder_B 
    Application.FileSearch.Filename = FileNames_B 
    If Application.FileSearch.Execute = 0 Then 
    
    
    To

    VB:
     
    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,698

    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, 01:43
  2. Application.FileSearch In Excel 2010
    By criciuma in forum Excel and/or Access Help
    Replies: 2
    Last Post: June 21st, 2011, 22:26
  3. Application.filesearch
    By Scotchjim in forum EXCEL HELP
    Replies: 3
    Last Post: June 17th, 2011, 05:16
  4. Application.FileSearch alternative?
    By Bryan021 in forum EXCEL HELP
    Replies: 2
    Last Post: May 13th, 2011, 22:20
  5. Application.filesearch
    By parrimin in forum EXCEL HELP
    Replies: 5
    Last Post: June 28th, 2007, 21: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