Microsoft Office Forums - View Single Post - [Solved] copy a specific words to excel list

macropod · 06-30-2014, 07:29 PM

Since you want to get a whole folder full of data, all on different worksheets, this would be better with an Excel macro instead of a Word macro. Simply add the following macro to an Excel workbook, then run it. Note that the code requires a reference to the Word object model. To do this in the VBE, choose Tools|References then scroll down to the Microsoft Word entry and check it.

Code:

Sub GetWordLists()
     'Note: this code requires a reference to the Word object model
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim strFolder As String, strFile As String
    Dim WkBk As Workbook, WkSht As Worksheet, i As Long
    strFolder = GetFolder
    If strFolder = "" Then Exit Sub
    Set WkBk = ThisWorkbook
    strFile = Dir(strFolder & "\*.docx", vbNormal)
    While strFile <> ""
        Set WkSht = WkBk.Sheets.Add
        i = 1
        WkSht.Cells(i, 1) = strFolder & "\" & strFile
        WkSht.Cells(i, 2) = "Page"
        WkSht.Name = Split(strFile, ".doc")(0)
        Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
        With wdDoc
            With .Range
                With .Find
                    .ClearFormatting
                    .Replacement.ClearFormatting
                    .Text = ChrW(&H649)
                    .Replacement.Text = ""
                    .Forward = True
                    .Wrap = wdFindStop
                    .Format = False
                    .MatchCase = False
                    .MatchWholeWord = False
                    .MatchWildcards = False
                    .MatchSoundsLike = False
                    .MatchAllWordForms = False
                    .Execute
                End With
                Do While .Find.Found
                    i = i + 1
                    WkSht.Cells(i, 1) = Trim(.Duplicate.Words.First.Text)
                    WkSht.Cells(i, 2) = .Duplicate.Information(wdActiveEndAdjustedPageNumber)
                    .Collapse wdCollapseEnd
                    .Find.Execute
                Loop
            End With
            .Close SaveChanges:=False
        End With
        strFile = Dir()
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
End Function

In addition to listing the words, the macro also outputs the pages on which they appear. No attempt is made to check for or remove duplicates.