Results 1 to 3 of 3

Thread: Find string in Excel sheet from Word doc

  1. #1

    Find string in Excel sheet from Word doc

    i have a macro that reads a selection in a (open) word file
    and i want to find this selection in the 2nd column of a worksheet
    and copy the cell value of the 4th column (same row).
    I've tried several methods with no success.
    I ask for your help for the [find] section and the selection of the cell in 4th column.

    This reads all cells in the 2nd column but doesn't find the string from Word (r) that exists in 10th row.

        With wb.Sheets("new_prices").Range("b1")    '<---------------------   wb is the open excel file
            For idx = 1 To .CurrentRegion.Rows.Count
                With Selection.Find
                    .Forward = True
                    .MatchWholeWord = False
                    .MatchCase = False
                    .Wrap = wdFindContinue
                    .Text = r  '<-------------------------------------- string from Word
                    If .Execute = True Then
                    End If
                End With
        End With
    Last edited by Aussiebear; 12-28-2021 at 02:06 PM. Reason: Added code tags to supplied code

  2. #2
    If I understand the requirement correctly, you want to search column 2 of your worksheet for a term selected in a word document, and if that term is found, you want to copy the value from column 4 of the same row to column 2?
    That being the case the following Word macro should do the job:

    Sub Macro1()
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim idx As Long
    Dim wdDoc As Document
    Dim oRng As Range
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        If Err Then
            MsgBox "Open Excel and the workbook first!", vbCritical
            GoTo lbl_Exit
        End If
        On Error GoTo 0
        Set xlWB = xlApp.activeworkbook
        Set wdDoc = ActiveDocument
        Set oRng = Selection.Range
        If Len(oRng) = 0 Then
            MsgBox "Nothing selected in the document!", vbCritical
            GoTo lbl_Exit
        End If
        Set xlWS = xlWB.Sheets("new_prices")
        With xlWS
            For idx = 1 To .UsedRange.Rows.Count
                Debug.Print .Cells(idx, 2) & vbTab & oRng.Text
                If .Cells(idx, 2) = oRng.Text Then .Cells(idx, 2) = .Cells(idx, 4)
            Next idx
        End With
        Set wdDoc = Nothing
        Set oRng = Nothing
        Set xlApp = Nothing
        Set xlWB = Nothing
        Set xlWS = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes

  3. #3
    Thank you very much for your answer.
    I didn't mention the selection term is part of cell's content. Finally with this lines i have the desired result.

      With xlWS
           For idx = 1 To .UsedRange.Rows.Count
                If InStr(.Cells(idx, 2).Value, oRng) <> 0 Then
                        .Cells(idx, 4).Copy
                        GoTo 30
                End If
           Next idx
       MsgBox "Code not found"
       GoTo lbl_Exit
       End With

Posting Permissions

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