Excel VBA: Open word file, find word/number, copy and paste back into excel

[The Power Loon]

I must apologize, as I know this crosses over into word. What I am trying to do is create a Macro that will do the following:

- Open multiple word files (pathways starting at A3). Search for words/numbers containing predefined values (starting at B3). Copy the words/numbers. Paste them back into Excel starting at D3 (preferably into separate columns for each word file, but a single column will do as well).

I started building off of some existing code I have, but stopped as this exceeded my abilities. You'll find what I have so far below. I'm hoping one of you could help me out.

Thank you for your time and consideration of this request.

Sub Find ()

Dim ws As Worksheet, msWord As Object, itm As Range
    
    Dim i   As Long
    Dim rc  As Long
    
    Set ws = ActiveSheet
    Set msWord = CreateObject("Word.Application")
    rc = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With msWord
        For i = 3 To rc
        
            .Visible = True
            .Documents.Open ws.Range("A" & i).Value
            .Activate
    
            With .ActiveDocument.Content.Find
                .ClearFormatting
                .Replacement.ClearFormatting
                For Each itm In ws.UsedRange.Columns("B").Cells
                    If Not IsEmpty(itm.Value2) Then
                        With .Find
                            .Text = "" 'column B characters to find and return words here
                            .Format = False
                            .MatchCase = False
                            .MatchWholeWord = False
                            .MatchWildcards = True
                            .Execute
                        End With
                    End If
                Next
                .Copy
            End With
            .ActiveDocument.Close SaveChanges:=True
        
        Next i
        
        .Quit
    End With
    
    Set msWord = Nothing
    Set ws = Nothing
    
    Range("D3").Select
    ActiveSheet.Paste
    
End Sub

 

[NdNoviceHlp]

I don't understand … you're looking for specific words/numbers (listed in XL) and if found in Word, U want to copy and paste them back to XL at some other specific location? Why not just check if the specific word/number exists in the Word doc and if it does move the word/number from the original location in XL to the other location in XL? Dave

 

[The Power Loon]

I don't understand … you're looking for specific words/numbers (listed in XL) and if found in Word, U want to copy and paste them back to XL at some other specific location? Why not just check if the specific word/number exists in the Word doc and if it does move the word/number from the original location in XL to the other location in XL? Dave

No, I'm looking for predefined values, and returning the word/number containing them.

The a word files I am working with are hundreds of pages long, and have part numbers interspersed throughout them (e.g. E100467-105, M006FG8000C-01, 1000408-202). I want the Macro search for "-0", "-1", "-2", etc., copy all words/numbers containing them, and paste them back into excel.

Also, if you have a more expedient method, I would be happy to utilize it instead.

 

[NdNoviceHlp]

That's considerably more difficult. You need to expand your find to include the whole word...

If .found = True Then
.Parent.Expand Unit:=2 'wdword

The selection should then get you the whole word you're looking for. Dave