E
Ed from AZ
This code was originally written in Excel 200 - I am now in Excel
2003. The code pops up a UserForm and asks for keywords, then does a
text search through a collection of Word documents. The original code
wrote the full path of all matching documents into the worksheet as
hyperlinks. I'm trying to re-write the code to simply write the names
as text formatting as hyperlinks (I'll use the SelectionChange event
to capture a click and do something else).
Somehow, I keep losing my rngFiles range object near the end of the
code. At least, that's what I think the 424 - object required error
is trying to tell me. I have chaged the With - End blocks and stuck
Application.FileSearch everywhere to avoid confusion (then again maybe
not!!). If anyone can help me over this hump, I'd greatly appreciate
it.
Ed
With Application.FileSearch
.NewSearch
.LookIn = strLocation
.SearchSubFolders = False
.TextOrProperty = strSearchFor
.Filename = "L5-" & strName & "*.doc"
.Execute
End With
Stop
Set rngFiles = Range("E20")
rngFiles.Offset(0, 0) = "Found " &
Application.FileSearch.FoundFiles.Count & " containing " &
Application.FileSearch.TextOrProperty
For lngIndex = 1 To Application.FileSearch.FoundFiles.Count
'ActiveSheet.Hyperlinks.Add Anchor:=rngFiles.Offset(lngIndex,
0), Address:=.FoundFiles.Item(lngIndex)
strName = Application.FileSearch.FoundFiles.Item(lngIndex)
' *************** ERROR NEXT LINE
rngFiles.Offset(lngIndex, 0).Text = strName
' **************** ERROR PREV LINE
rngFiles.Offset(lngIndex, 0).Font.ColorIndex = 5
rngFiles.Offset(lngIndex, 0).Font.Underline =
xlUnderlineStyleSingle
Next
End Sub
2003. The code pops up a UserForm and asks for keywords, then does a
text search through a collection of Word documents. The original code
wrote the full path of all matching documents into the worksheet as
hyperlinks. I'm trying to re-write the code to simply write the names
as text formatting as hyperlinks (I'll use the SelectionChange event
to capture a click and do something else).
Somehow, I keep losing my rngFiles range object near the end of the
code. At least, that's what I think the 424 - object required error
is trying to tell me. I have chaged the With - End blocks and stuck
Application.FileSearch everywhere to avoid confusion (then again maybe
not!!). If anyone can help me over this hump, I'd greatly appreciate
it.
Ed
With Application.FileSearch
.NewSearch
.LookIn = strLocation
.SearchSubFolders = False
.TextOrProperty = strSearchFor
.Filename = "L5-" & strName & "*.doc"
.Execute
End With
Stop
Set rngFiles = Range("E20")
rngFiles.Offset(0, 0) = "Found " &
Application.FileSearch.FoundFiles.Count & " containing " &
Application.FileSearch.TextOrProperty
For lngIndex = 1 To Application.FileSearch.FoundFiles.Count
'ActiveSheet.Hyperlinks.Add Anchor:=rngFiles.Offset(lngIndex,
0), Address:=.FoundFiles.Item(lngIndex)
strName = Application.FileSearch.FoundFiles.Item(lngIndex)
' *************** ERROR NEXT LINE
rngFiles.Offset(lngIndex, 0).Text = strName
' **************** ERROR PREV LINE
rngFiles.Offset(lngIndex, 0).Font.ColorIndex = 5
rngFiles.Offset(lngIndex, 0).Font.Underline =
xlUnderlineStyleSingle
Next
End Sub