Searching Excel from word

[TLA]

I am trying to have a Word file and Excel file Interact. I have word tempate that is used by several people to generate documents. After the dcouments are completed, I want to have a macro in the word file that can run and do a compare vs a table in excel. Basically just look at column A starting at a certain cell (say A5) and just loop down. Each cell contains a single word. If that word appears in the document, I want to excute some futher code.

My problem is when I am writing the word macro I can't use common excel references (like Cells(1,1).value, etc). So I am getting stuck in searching the excel column and assigning the words found in that column to variables in my word macro. It should be a piece fo cake but I must be missing something.

Any suggestions?

 

[BrianB]

When running one MS Office application from another, the thing to remember is that they use default code properties to save us having to type them in every time.

In this case the Application Object is one which we do not normally have to use, because the active application is always assumed. So we do not have to type things like

Application.WorkBooks("Book1.xls").Activate

In this case we are using Excel from within Word, so we now need to be explicit with the Excel Application each time we want to do something with it. You also need to note that, to save typing again, I have used code like :-

With xlApp ....... End With

Note that here we have to proceed the Excel code with a fullstop/dot to apply it to the Application Object. eg.

With xlApp
    .Visible =True
End With

I have added "cleanup" code at the end to close both applications, but commented it out to leave them open for test purposes.

'=============================================================================================
'- WORD MACRO
'- TO USE A LIST IN AN EXCEL WORKSHEET TO FIND WORDS IN THE CURRENTLY ACTIVE DOCUMENT
'- this example highlights those words
'=============================================================================================
'- ** In Word VB Editor ... Tools/References ......
'- ** .....  check the reference to "Microsoft (??) Excel Object Library"
'- Brian Baulsom November 2007
'=============================================================================================
'- Excel
Dim ExcelPath As String             ' [*] Amend as required below
Dim MyWorkbookName As String        ' [*] Amend as required below
Dim MyLookupSheetName As String     ' [*] Amend as required below
'---------------------------------------------------------------------------------------------
Dim xlApp As Object                 ' Excel Application Object
Dim MyWorkbook As Object
Dim MyLookupSheet As Object
Dim FromRow As Long
Dim LastRow As Long
Dim SearchString As String          ' string to find in Word
Dim CountAll As Integer             ' count all to find
Dim CountFound As Integer           ' count found items
'---------------------------------------------------------------------------------------------
'- Word
Dim WordDoc As Document
Dim WordRange As Range              ' text range

'=============================================================================================
'- MAIN ROUTINE
'=============================================================================================
Sub EXCEL_LOOKUP()
    '-----------------------------------------------------------------------------------------
    '- NB. NEED TO AMEND THESE VARIABLES TO SUIT YOUR SETUP
    '-----------------------------------------------------------------------------------------
    ExcelPath = ActiveDocument.Path             ' this assumes same folder as Word Document
    MyWorkbookName = "XL to Word List.xls"      '[*] AMEND
    MyLookupSheetName = "LookupSheet"           '[*] AMEND
    '-----------------------------------------------------------------------------------------
    '- Word
    Set WordDoc = ActiveDocument
    Set WordRange = WordDoc.Content
    WordRange.HighlightColorIndex = wdNone      ' remove any highlighting
    CountAll = 0
    CountFound = 0
    '-----------------------------------------------------------------------------------------
    '- OPEN EXCEL APPLICATION
    '-----------------------------------------------------------------------------------------
    '- See if Excel is open already (next line gives error if not)
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")        ' set variable if Excel already open
    '-----------------------------------------------------------------------------------------
    '- check for error
    If Err.Number <> 0 Then
        Set xlApp = CreateObject("Excel.Application")   ' open Excel application
    End If
    On Error GoTo 0     ' reset error trapping
    '-----------------------------------------------------------------------------------------
    '- USE EXCEL
    '-----------------------------------------------------------------------------------------
    With xlApp
        .Visible = True             ' show Excel
        '-------------------------------------------------------------------------------------
        '- Excel workbook & worksheet
        MyWorkbookName = ExcelPath & "\" & MyWorkbookName
        Set MyWorkbook = .Workbooks.Open(FileName:=MyWorkbookName)
        Set MyLookupSheet = MyWorkbook.Worksheets(MyLookupSheetName)
        LastRow = MyLookupSheet.Range("A65536").End(xlup).Row
        '-------------------------------------------------------------------------------------
        '- loop through Excel rows
        For FromRow = 3 To LastRow
            CountAll = CountAll + 1
            SearchString = MyLookupSheet.Cells(FromRow, "A").Value
            FindTextInWordDocument
        Next
    End With
    '-----------------------------------------------------------------------------------------
    '- FINISH
    '-----------------------------------------------------------------------------------------
    '- WORD : close document
    'WordDoc.Close savechanges:=False
    '-----------------------------------------------------------------------------------------
    'Close Excel ( & clear variables from memory)
'    With xlApp
'        .MyWorkbook.Close savechanges:=False    ' close workbook
'        .Quit                                   ' close Excel Application
'    End With
'    Set MyWorkbook = Nothing
'    Set MyLookupSheet = Nothing
'    Set xlApp = Nothing
    '------------------------------------------------------------------------------------------

    MsgBox ("Done" & vbCr _
        & "Looked for " & CountAll & " items." & vbCr & "Found " & CountFound & " matches.")
End Sub
'======== END OF MAIN SUB =====================================================================

'=============================================================================================
'- WORD MACRO : FIND TEXT IN THE DOCUMENT   - called fom Main Sub ()
'=============================================================================================
Private Sub FindTextInWordDocument()
    '- reset search range
    Set WordRange = WordDoc.Content
    '-----------------------------------------------------------------------------------------
    '- Set up Find parameters & find the text
    With WordRange.Find
        .ClearFormatting
        .Text = SearchString
        .Wrap = wdFindStop
        .Format = False
        .Execute
    End With
    '-----------------------------------------------------------------------------------------
    '- what to do if text is found
    While WordRange.Find.Found
        CountFound = CountFound + 1
        WordRange.HighlightColorIndex = wdYellow
        '-------------------------------------------------------------------------------------
        '- find next
        WordRange.Find.Execute
    Wend
End Sub
'---------------------------------------------------------------------------------------------