jim may
Well-known Member
- Joined
- Jul 4, 2004
- Messages
- 7,486
Over the weekend I switched from xl 2007 to 2010. This exact code worked fine in 2007, but not in 2010 currently. As i step thru (F8) the red code line appears to be "flawed"; Any help appreciated...
Rich (BB code):
Sub foobar() ' This is what's required for XL 2007
Dim fso As Object
Dim strName As String
Dim strArr(1 To 65536, 1 To 1) As String, i As Long
Dim searchTerm As String
searchTerm = Range("B3").Value
Const strDir As String = "C:\Users\Owner\Documents\Trusted_Locations"
Range("B4:B7000").ClearContents
strName = Dir$(strDir & "\*" & searchTerm & "*.xls?")
Do While strName <> vbNullString
i = i + 1
strArr(i, 1) = Mid(strDir & "\" & strName, 37, 99)
strName = Dir$()
Loop
fCount = i
MsgBox "Found " & fCount & " Files"
If i > 0 Then
Range("B4").Resize(i).Value = strArr
End If
End Sub