Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FileSearch - TextorProperty (Excel VBA 2003)

    I have noted the

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    Welcome to Woody's Lounge!

    As far as I know, there is no way to limit FileSearch to the body of the document. To do that, you need to open each document in turn and search for the text. For example:

    Sub FindText()
    Dim strPath As String
    Dim strFile As String
    Dim doc As Document
    ' Path name must end in a backslash
    strPath = "Cocuments and SettingsYourNameMy Documents"
    strFile = Dir(strPath & "*.doc")
    Application.ScreenUpdating = False
    Do While Not strFile = ""
    Set doc = Documents.Open( _
    FileName:=strPath & strFile, _
    AddToRecentFiles:=False)
    With doc.Content.Find
    .ClearFormatting
    .Text = "oss"
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    If .Execute Then
    Debug.Print strFile
    End If
    End With
    doc.Close SaveChanges:=False
    strFile = Dir
    Loop
    Application.ScreenUpdating = True
    Set doc = Nothing
    End Sub

    Unfortunately, this is much slower than FileSearch (and it doesn't search subfolders)

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    Thanks very much. My concern is that my overall objective is search several hundred documents for about 70 key words and the method you have suggested may be too slow. All was going well until I noted that TextOrProperty searches the file properties as well as the body of the text. If I could extract all the text from the file properties I may be able to work around it.

    I am still confused as to why when using FileSearch to search for the specific word

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    Opening the documents and searching for 70 keywords would indeed be slow. But FileSearch is notoriously quirky. I have no idea why it finds "OSS" in each document. I created a macro to loop through all document properties and inspect their values; "OSS" didn't occur. But since "*OSS*" appears to work OK, why don't you use that?

  5. #5
    New Lounger
    Join Date
    Nov 2006
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    The problem with *OSS*" (with asterisks) is that it picks up words I do not want such as 'possibly' . I guess I may be able to get around that by using 'MatchTextExactly ' = true, assuming it is case sensitive.

    What is particularly strange is that "OSS" (without asterisks) even works for pdf files, which are not supported by the File Search object.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    FileSearch does search pdf files, but the result is useless because the text isn't stored in a human-readable way. Perhaps most files have "oss" somewhere in the metadata part.

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    Is OSS your company name? Is that why it may be appearing in your document properties?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    New Lounger
    Join Date
    Nov 2006
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    In addition to the problem with 'OSS' search I have discovered what appears to be another more serious problem.

    If I use FileSearch to search for a phrase composed of two words separated by a space such as "business analysis" it finds documents that contain both the word 'business' and word 'analysis', even though the phrase "business analysis" is not present. The space seems to causes problems.

    Any explanation as to what is going on would be much appreciated

    To confirm the code is as follows:

    Function FindText(CVname, TextName, ExactMatch) As Integer
    Const CV_Path = "C:
    Dim Wd As Object

    Set Wd = GetObject(, "Word.Application")
    With Wd.FileSearch
    .NewSearch
    .LookIn = CV_Path
    .Filename = CVname
    .TextOrProperty = "business analysis"
    .MatchTextExactly = True
    If .Execute > 0 Then
    FindText = 1
    Else
    FindText = 0
    End If
    End With
    End Function

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    I don't think that's the reason. The code (using "OSS") also found all Word documents in a folder on my PC.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    When I include the line

    .MatchTextExactly = True

    FileSearch looks for the entire phrase; if I omit it, it looks for each word in TextOrProperty separately.

  11. #11
    New Lounger
    Join Date
    Nov 2006
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    Thanks. However, MatchTextExactly = True is case sensitive so how do I carry-out a NON case sensitive search on a phrase such as "business analysis" or "business analy*" ?

    In other words using MatchTextExactly = True seems to resolve the problem of FileSearch looking for the two words separately but prevents you from running a non case sensitive search. Is this correct ?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch - TextorProperty (Excel VBA 2003)

    Hmm, I ran several tests, and the results are confusing. Sometimes it finds text that isn't there, and sometimes it doesn't find text that is definitely present. I fear that FileSearch is problematic when it comes to searching for text within documents, I wouldn't depend on it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •