searching for text in a word table from Excel

[davefish]

I have an Excel SS that allows the user to make selections for a configurable product and once done produce a quote. I can successfully gather the data and launch a word template that works, but now I am asked to add or delete specific rows dependent on the selection. This means identifying text in the word document and returning the row number. As an example P725-M-6W2H-6T-18A-[highlight #FCE94F]4UN[/highlight]-SL-R-T-FC24 I would need to add a row after 4UN. How would I identify the row number in the attached table.

Here's also an example of how I add or delete rows currently from fixed locations.

Can anyone help?

   'Populate Model Key Table
   
   wordDoc.Tables(7).Cell(3, 1).Range.Text = Range("O25").Value ' Window size
   wordDoc.Tables(7).Cell(6, 1).Range.Text = Range("P31").Value ' TAR column
   wordDoc.Tables(7).Cell(6, 2).Range.Text = Range("O31").Value ' TAR text
   wordDoc.Tables(7).Cell(8, 1).Range.Text = Range("O17").Value ' Illumination type
   wordDoc.Tables(7).Cell(8, 2).Range.Text = "Illumination by pluggable" & " " & Range("C9").Value ' Illumination type Text
   If Range("C10").Value = "No" Then
   wordDoc.Tables(7).Rows(9).Delete
   End If
   
   If Range("C12").Value = "No" Then ' Tropicalisation
   wordDoc.Tables(7).Rows(10).Delete
   End If
   
  If Range("C11").Value > 0 Then ' Unarmed channels
  wordDoc.Tables(7).Rows(8).Select
  wordApp.Selection.Rows.Add[img]https://res.cloudinary.com/engineering-com/image/upload/v1528918479/tips/Table_wzzewe.jpg[/img]
  wordDoc.Tables(7).Cell(8, 1).Range.Text = Range("P24").Value '
  wordDoc.Tables(7).Cell(8, 2).Range.Text = "Number of Unarmed Windows"
  End If
   
    If Range("C16").Value = "No" Then
    wordDoc.Tables(7).Rows(14).Delete
    End If

 

[davefish]

I've found something that seems to work to a degree, but it hangs

The code that inserts the part number in the graphic above comes from a value in an excel spreadsheet and I have successful manager to find the row number using the code below:-

   With ActiveDocument.Tables(7)
 For r = 1 To .Rows.Count
 If .Cell(r, c).Range.Text Like "*4UN*" Then
 wordDoc.Tables(7).Rows(r + 1).Select
 wordApp.Selection.Rows.Add
 End If
 Next r
 End With

it works, but hard coded to 4UN.I want this to use the value in the Excel spreadsheet ..."wordDoc.Tables(7).Cell(8, 1).Range.Text = Range("P24").Value "... but it doesn't find the value as I assume there are hidden characters. Can anyone tell me how to search for text, stripping off any hidden character??

 

[Andrzejek]

You may first try something simple, like:

Debug.Print "---" & wordDoc.Tables(7).Cell(8, 1).Range.Text & "---"
Debug.Print "---" & Range("P24").Value & "---"

And SEE what's in between [tt]---[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

If .Cell(r, c).Range.Text Like """*" & YourWorkbookObject.YourSheetObject.Range("P24").Value & "*""" Then

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[macropod]

You might try something based on:

Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Word.Range, StrFnd As String, r As Long
StrFnd = Range("C11").Value
With wordDoc.Tables(7)
  Set Rng = .Range
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Text = StrFnd
      .Replacement.Text = ""
      .Forward = True
      .Wrap = wdFindStop
      .MatchCase = True
      .MatchWholeWord = True
      .MatchWildcards = False
      .Execute
    End With
    Do While .Find.Found
      If .InRange(Rng) Then
        r = .Cells(1).RowIndex
        With Rng.Tables(1)
          .Rows.Add BeforeRows:=.Rows(r)
          'or
          '.Rows(r).Delete
        Next
      Else
        Exit Do
      End If
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
End With
Application.ScreenUpdating = True
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[davefish]

Hi Skip,

thanks for the lead. I modified the code slightly and it works fine as below:-

  With ActiveDocument.Tables(7)
 For r = 1 To .Rows.Count
 If .Cell(r, c).Range.Text Like "*" & Range("O25").Value & "*" Then
 wordDoc.Tables(7).Rows(r + 1).Select
 wordApp.Selection.Rows.Add
 End If
 Next r
 End With

Many thanks to others who also replied

DaveFish