Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 24

Thread: Finding text strings in Word from an excel list of text strings

  1. #1
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location

    Finding text strings in Word from an excel list of text strings

    I have an immediate requirement to find text strings in several Word documents from a list of text strings in Excel. I would think VBA could handle this but I'm not sure how to start!

    There are 400 cells (single column) of very short text strings (aircraft part numbers) in Excel and I need to search through 10 specific Word documents (each document is about 300 pages) to locate each text string. Once the text string is found (in one or more of the documents) I need the macro to list the Word document page and paragraph number in the Excel columns next to the text string.

    Thanks in advance for any help.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    A few questions
    Are all the files in one folder, if so, are there other files in the folder?
    Are the text strings "words" in the documents, or are they part of longer strings?
    Do you want all the results to be returned on one sheet?
    Is any "structure" required for the return?
    Can you post a sample of what you would like the return to look like?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    Thanks for responding...

    The files could be placed in one dedicated folder or I could list the file names and path in a spreadsheet column, whichever is easier...

    ...the strings are words and are not part of a longer string...

    ... yes, I would like the results on the same spreadsheet row adjacent to the column of the text string..

    Here is what I would like the spreadsheet to look like:

    Search String Document Page Number Document Paragraph
    D145-1112 resulting page # resulting para #
    L2233-1 resulting page # resulting para #

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I'm stuggling with the syntax to run the Word search from Excel. Here's some code that I've got so far. The Word code contained within the asterisks works within Word to return the values you're looking for. The code requires a reference to Microsoft Word.

    [vba]
    Sub OpenAndReadWordDoc()
    ' assumes that the previous procedure has been executed
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim tString As String, tRange As Word.Range

    Set wrdApp = CreateObject("Word.Application")
    'wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\MyNewWordDoc.doc")
    ' example word operations
    With wrdDoc
    '**************************
    'This section comes from Word and needs adapting
    Dim MyPos As Range, Paras As Long, Chk As Long, Pg As Long
    With Selection.Find
    Do
    .ClearFormatting
    .Text = "stop"
    .Execute
    Set MyPos = ActiveDocument.Paragraphs(1).Range
    MyPos.SetRange Start:=MyPos.Start, _
    End:=Selection.Range.End
    Paras = MyPos.Paragraphs.Count
    Pg = Selection.Information(wdActiveEndPageNumber)
    If Paras = Chk Then Exit Do
    Chk = Paras
    MsgBox "page " & Pg & " - " & Paras
    Loop
    End With
    '**************************
    .Close ' close the document
    End With
    wrdApp.Quit ' close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    ActiveWorkbook.Saved = True
    End Sub
    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    You found the same problem I am having. When running from Excel I can't get the Word Find property to work...although it works fine when run from Word

    Any suggestions?

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Yes, it seems more difficult than it needs to be...
    If you apply Find to a range (like the whole content of a doc), it returns a boolean but modifies the selection to the found string.
    If you use the selection object to get the position, it needs to be referred to from the document window.
    Well, this seems to work:
    (I've used the filesystemobject to go through the files in the folder so you'll need a reference to the Microsoft Sctipting Runtime as well as Microsoft Word. And I've named the range of cells "partnumbers")
    [vba]Sub Main()

    Const TARGET_FOLDER_PATH As String = "C:\TEMP\"

    Dim fso As FileSystemObject
    Dim oTargetFolder As Folder
    Dim f As File

    Dim appWD As Word.Application
    Dim docSource As Word.Document
    Dim oSearchRange As Word.Range

    Dim rngPartnumber As Range

    Set fso = New FileSystemObject
    Set oTargetFolder = fso.GetFolder(TARGET_FOLDER_PATH)

    Set appWD = New Word.Application
    For Each rngPartnumber In Range("partnumbers")
    For Each f In oTargetFolder.Files
    Set docSource = appWD.Documents.Open(TARGET_FOLDER_PATH & f.Name)
    Set oSearchRange = docSource.Content
    With oSearchRange.Find
    .ClearFormatting
    .MatchWholeWord = True
    .Text = rngPartnumber.Text
    If .Execute Then
    docSource.Range(docSource.Paragraphs(1).Range.Start, _
    oSearchRange.End).Select
    rngPartnumber.Offset(0, 1).Value = f.Name & " Page: " & _
    appWD.ActiveWindow.Selection.Information(wdActiveEndPageNumber) _
    & " Para: " & appWD.ActiveWindow.Selection.Paragraphs.Count

    End If
    End With
    docSource.Close False
    Next f
    Next rngPartnumber
    appWD.Quit False

    End Sub[/vba]
    Last edited by Killian; 03-01-2006 at 05:12 AM. Reason: took out an unused variable
    K :-)

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Thanks Killian.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    I always find all that messing around with the selection object a bit strange but I thought I'd better stick with it - I've got a Word template project 'round the corner so I need to get used to strangeness.
    K :-)

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    A couple of small adjustments to Killian's code, but which may not be necessary. This will ignore any files without a Doc extension and check for multiple instances of the search string.
    Regards
    MD
    [VBA]
    Sub Main()

    Const TARGET_FOLDER_PATH As String = "C:\TEMP\"

    Dim fso As FileSystemObject
    Dim oTargetFolder As Folder
    Dim f As File

    Dim appWD As Word.Application
    Dim docSource As Word.Document
    Dim oSearchRange As Word.Range

    Dim rngPartnumber As Range
    Dim Rw As Long, Paras As Long, Chk As Long


    Set fso = New FileSystemObject
    Set oTargetFolder = fso.GetFolder(TARGET_FOLDER_PATH)

    Set appWD = New Word.Application
    For Each rngPartnumber In Range("partnumbers")
    Rw = rngPartnumber.Row
    For Each f In oTargetFolder.Files
    If UCase(Right(f.Name, 3)) = "DOC" Then
    Set docSource = appWD.Documents.Open(TARGET_FOLDER_PATH & f.Name)
    Set oSearchRange = docSource.Content
    With oSearchRange.Find
    .ClearFormatting
    .MatchWholeWord = True
    .Text = rngPartnumber.Text
    Do
    If .Execute Then
    docSource.Range(docSource.Paragraphs(1).Range.Start, _
    oSearchRange.End).Select
    Paras = appWD.ActiveWindow.Selection.Paragraphs.Count
    Cells(Rw, 256).End(xlToLeft).Offset(0, 1).Value = f.Name & " Page: " & _
    appWD.ActiveWindow.Selection.Information(wdActiveEndPageNumber) _
    & " Para: " & Paras
    End If
    If Paras = Chk Then Exit Do
    Chk = Paras
    Loop
    End With
    docSource.Close False
    End If
    Next f
    Next rngPartnumber
    appWD.Quit False
    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    Thanks so much for the help.

    I am getting a Run-time error '1004' Method 'Range' of object'_Global' failed

    at

    For Each rngPartnumber In Range ("partnumbers")

    any thoughts?

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You need to create a Range Name called PartNumbers in your workbook for the list of these.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    Making progress... however now it opens the Word document but then shuts down Excel... no error message given.

  14. #14
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    There's nothin in the code that should do this. Can you step through it to determine exactly when this occurs. Just for info, what version of Office/Windows are you using?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  15. #15
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    The macro is shutting down in the "With oSearchRange.Find" statement

  16. #16
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    Office 2003
    Windows 2000, Service Pack 4

  17. #17
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    The debugging code shows the following for oSearchRange.Find
    <-The remote server machine does not exist or is unavailable

    Is there a Reference that I need to turn on

  18. #18
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Sorry, I can't replicate this behaviour.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  19. #19
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    OK,
    I found this behaviour when the code tries to open a file containing code or with links to other files. Try incorporating the following at "Set oSearchRange" to track down a problem file. I don't get 100% success with this, but it may show a way forward.

    [VBA]
    On Error Resume Next
    Set oSearchRange = docSource.Content
    If Err <> 0 Then
    MsgBox Err.Description & " - " & f.Name
    appWD.Quit False
    Exit Sub
    End If
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  20. #20
    VBAX Regular
    Joined
    Feb 2006
    Posts
    15
    Location
    mdmackillop:

    I was able to get the code to work on my laptop. Works great! It apparently has something to do with my desktop's References or the module the code is running from.

    When it runs it searches each document for each Rw. Is it possible to change the loops to first go down the list of all Rw's for document 1, then search document 2 for all the Rw's and so on. With 400 Rw's and 10 documents it will open/close 4000 times...which could take some time.

    You have been extremely helpful in getting me this far. If you have time to continue helping me that would be great. If not, I understand and thanks so much for your help.

Posting Permissions

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