Here is the code:
Option Explicit
Dim fso
Dim WshShell
Dim Rev()
Dim Part
Dim Nm
Dim prefx
Dim dn
Dim dir
Dim FileNm
Dim Answer
Dim Check
Dim i
Dim j
Dim folder
Dim file
Set fso = CreateObject("Scripting.FileSystemObject")
set WshShell = WScript.CreateObject("WScript.Shell")
Part = UCase(InputBox("Enter Number without extension:", "Number"))
Nm = Left(Part, 1)
If (Nm <> "M" And Nm <> "L") Then
prefx = "\\server\groups\released\"
dn = CLng(Part)
If dn < 3000 Then
dir = "1-2999\"
ElseIf dn < 5000 Then
dir = "3000-4999\"
ElseIf dn < 6000 then
dir = "5000-5999\"
ElseIf dn < 7000 Then
dir = "6000-6999\"
Else
dwgdir = "8000-\"
End IF
End If
If Nm = "L" Then
prefx = "\\server\groups\ls\"
dn = Clng(Right(Part, (Len(Part) - 1)))
If dn < 7000 Then
dir = "L01000-L06999\"
ElseIf dn < 8000 Then
dir = "L07000\"
ElseIf dn < 9000 then
dir = "L08000\"
Else
dir = "L09000-\"
End If
End If
If Nm = "M" Then
prefx = "\\server\groups\masters\"
dn = Clng(Right(Part, (Len(Part) - 1)))
If dn < 1600 Then
dir = "M0-M1599\"
Elseif dn < 1645 Then
dir = "M1600-44\"
Elseif dn < 1700 Then
dir = "M1645-99\"
Elseif dn < 1740 Then
dir = "M1700-39\"
ElseIf dn < 1800 Then
dir = "M1740-99\"
ElseIf dn < 1850 Then
dir = "M1800-49\"
Else
dir = "M1900-\"
End If
End If
i=0
Set folder = fso.GetFolder(prefx & dir)
For Each file In folder.Files
If (Left(file.Name, Len(Part))) = Part Then
ReDim Preserve Rev(i)
Rev(i) = Right(file.Name, (Len(file.Name) - Len(Part)))
i = i + 1
End If
Next
Set folder = Nothing
for j = 0 to (i - 1)
FileNm = prefx & dir & Part & Rev(j)
Answer = Msgbox ("Load file " & FileNm & " ?", 4, "Prompt")
If Answer = vbYes Then
Check = fso.FileExists(FileNm)
If Check Then
WshShell.run Chr(34) & FileNm & Chr(34)
Else
Msgbox("File " & FileNm & " Not Found")
End If
End If
Next
i = 0
Set fso = Nothing
Set WshShell = Nothing
wscript.quit
For a typical number I would see
FileNm = "\\server\groups\released\8000-\9150.xls"
or
FileNm = "\\server\groups\released\8000-\8251.pdf"
The second opens right up, while the first just sits there, no error message but never loads excel...
Thanks for your help