Results 1 to 2 of 2

Searching Excel from word

This is a discussion on Searching Excel from word within the Excel Questions forums, part of the Question Forums category; I am trying to have a Word file and Excel file Interact. I have word tempate that is used by ...

  1. #1
    TLA
    TLA is offline
    Board Regular
    Join Date
    Jul 2003
    Posts
    127

    Default Searching Excel from word

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,115

    Default

    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
    Code:
    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 :-
    Code:
    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.
    Code:
    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.

    Code:
    '=============================================================================================
    '- 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
    '---------------------------------------------------------------------------------------------
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com