Results 1 to 3 of 3

Macro - Open up file based on search filter located in folder with various subfolders

Threaded View

  1. #1
    Registered User
    Join Date
    03-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Macro - Open up file based on search filter located in folder with various subfolders

    Hi,

    Can anyone please help me with a coding that allows me to search and open up a file that can be located in a folder and all its sub folders?

    my coding so far only allows me to search and opens up the file name in a specific folder "Quotes - WA" and does not search sub folders.

     Sub Openup_WA()
    
    
    Dim myFile As String
        Dim myPath As String
        Dim myFilter As String
        Dim Quotenum As String
        Dim eScriptObject As Object
        Dim xresult As String
        ' pick up search string from
    startagain:
        Quotenum = InputBox(prompt:="Enter Quote Number and Company Name", _
                            Title:="Open Quote")
    
        If Trim(Quotenum) = "" Then
            xresult = MsgBox("Do you want to try again", vbYesNo, "No Value Entered")
            Debug.Print xresult
            Select Case xresult
    
            Case vbYes
                GoTo startagain
            Case vbNo
                Exit Sub
            End Select
           End If
           
           myFilter = Quotenum
                  
    Set eScriptObject = CreateObject("Scripting.FileSystemObject")
            If eScriptObject.FileExists("\\NAS\Shared\Quotes - Customers\Quotes - WA\" & myFilter & ".xls") Then
                MsgBox "Opening Quote File", vbInformation, "File Found"
                Workbooks.Open ("\\NAS\Shared\Quotes - Customers\Quotes - WA\" & myFilter & ".xls")
            Else
                xresult = MsgBox("Do you want to try again", vbYesNo, "Quote Not Found")
    
                Select Case xresult
    
                Case vbYes
                    GoTo startagain
                Case vbNo
                    Exit Sub
                 End Select
                
                End If
    
    End Sub
    The main coding i need help with is
        If eScriptObject.FileExists("\\NAS\Shared\Quotes - Customers\Quotes - WA\" & myFilter & ".xls") Then
                MsgBox "Opening Quote File", vbInformation, "File Found"
                Workbooks.Open ("\\NAS\Shared\Quotes - Customers\Quotes - WA\" & myFilter & ".xls")
            Else
    I need it to allow me to search in the 'Quotes - Customers' folder parameter but also searches in subfolders eg. 'Quotes - WA' / 'Quotes - VIC' or 'Quotes - NSW'

    Can anyone help? Hopefully i have explained it properly.
    Last edited by larzep; 03-26-2012 at 02:17 AM.

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