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.
Bookmarks