Extract highlighted words from Word to Excel

Question

How can I extract highlighted words from Microsoft Word into an Excel template?

The template is already created. I want the text from the Word document to be put into the Excel document I already have.

'code that highlights word "olympics"

Dim sFindText As String

sFindText = "Olympics"

Selection.ClearFormatting
Selection.HomeKey wdStory, wdMove
Selection.Find.ClearFormatting
Selection.Find.Execute sFindText

Do Until Selection.Find.Found = False
    Selection.Range.HighlightColorIndex = wdYellow
    Selection.MoveRight
    Selection.Find.Execute
Loop

Answer 1

So, after you run your code you need to run the one below to transfer all highlighted words to your excel file. See additional comments within proposed solution.

Selection.ClearFormatting
Selection.HomeKey wdStory, wdMove
Selection.Find.ClearFormatting

'here you set searching for highlighted words
Selection.Find.Highlight = True
Selection.Find.Execute

'lets open your workbook within new Excel application
    Dim EXL As Object
    Set EXL = CreateObject("Excel.Application")
    Dim xlsWB As Object 'which will be a workbook

    Dim xlsPath As String
    'put path to your file here
    xlsPath = "c:\Temp Priv\TestFile.xlsm"

    Set xlsWB = EXL.workbooks.Open(xlsPath)

    Dim xlsRow As Long

Do Until Selection.Find.Found = False

        'we will write found words to first sheet in your Workbook,  _
         consecutive rows in column A

        xlsRow = xlsRow + 1
        xlsWB.sheets(1).Cells(xlsRow, "A") = Selection.Text
        Selection.Find.Execute

Loop
'lets show our excel application
    EXL.Visible = True