Hi CJ London, I have some code that was searching for data on a specific Excel file, I have slightly changed the code i found from another database i have, tweaked it a little so the file can be any file in the XL folder.
I haven't tested this yet but here it is, can you see any flaws in this ?
Code:
Dim apXL As Object, xlWB As Object, xlWS As Object, rngRange As Range
Dim intLR As Integer, iRow As Integer, i As Integer, intID As Integer, intOrder As Integer
Dim sFile As String, sPath As String, strSearch As String
sPath = "T:\DMT Ltd\XL Files\"
sFile = Dir(sPath & "*.xlsx", vbNormal)
strSearch = InputBox("Paste Number Here ?")
Select Case strSearch
Case Is = False
Exit Sub
Case Else
Set apXL = CreateObject("Excel.Application")
Set xlWB = apXL.Workbooks.Open(sPath & sFile)
Set xlWS = xlWB.Worksheets("Sheet1")
apXL.Visible = False
Do While sFile <> ""
With xlWS
intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To intLR
If StrComp(xlWS.Range("D" & i).Value, strSearch, vbTextCompare) = 0 Then
iRow = i
Exit For
End If
Next i
End With
If iRow > 0 Then
MsgBox (strSearch & " Is On File Name: " & sFile)
xlWB.Close
apXL.Quit
Set apXL = Nothing
Application.FollowHyperlink sPath & sFile
End If
If iRow = 0 Then
MsgBox ("File Name: " & "Your Search Is NOT On Any XL Files")
xlWB.Close
apXL.Quit
Set apXL = Nothing
End If
Loop
End Select