Find Text selection in Word then put into Excel

[vakubia0]

I've got the following text in word:
R.L/I.L.(S.L) METERS. RAIL/FLOOD LEVEL: ___ . No. TRACKS: 2

TRACK ALIGNMENT: CURVE. SUPER'N:________________ GUARD RAILS: YES / NO_______________

SPANS: 1 X 1.2 X 50 METER BRICK ARCH CULVERT

DRAWING: ______________LOAD RATING: ________________ LOAD/CLEAR. SIGNS: _______________________

I need to find the word SPANS and select the rest of the like after the ":" and copy that to an excel cell.
I have a number of these search/copy to do.

The main code is in Excel, opening word document and then finding the stuff to paste back into excel.

Set wd = CreateObject("Word.Application")

wd.Visible = False


Dim i As Integer
i = 1
Range("filename").Select
Selection.Offset(i, 0).Select

If Right(Selection.Value, 3) = "doc" Or Right(Selection.Value, 3) = "rtf" Then
    Application.StatusBar = "Opening File " & Selection.Value
    filename1 = Selection.Value
    
 
    wd.Documents.Open Filename:=filename1
    
    wd.Visible = True
    wd.Selection.GoTo What:=wdGoToLine, Which:=wdGoToRelative, Count:=1
    Set strFoundSpans = Nothing
    With wd.Selection.Find
        .ClearFormatting
        .Text = "SPANS"
        .Execute
        strFoundSpans = wd.Selection.Text
        
    
        If Not IsEmpty(strFoundSpans) Then
            wd.Visible = True
            wd.Selection.MoveRight Count:=3, Extend:=wdMove
            wd.Selection.EndKey Extend:=wdExtend
            strFoundSpansText = Selection.Text
            Range("spans").Select
            Selection.Offset(i, 0).Select
            Selection.Value = strFoundSpansText
            
            End If
    End With

 

[fumei]

Could you write your request with a little better detail?

I need to find the word SPANS and select the rest of the like after the ":" and copy that to an excel cell.

I have absplutely NO idea what "select the rest of the like" means. "Rest of the like"? Huh?

Do you mean the rest of the line? The rest of the document? What?

Ok;

wd.Selection.GoTo What:=wdGoToLine, Which:=wdGoToRelative, Count:=1

What is the purpose of this? You have opened the file, the Selection is at the opening line. Actually it is at Range Location 0. I can not figure out why you are doing this.

Set strFoundSpans = Nothing
With wd.Selection.Find
 .ClearFormatting
 .Text = "SPANS"
 .Execute
 strFoundSpans = wd.Selection.Text       
   If Not IsEmpty(strFoundSpans) Then

If .Execute does not have a .Found = True, then strFoundSpans will still pick up Selection.Text, and yes, it would be "". However while you have something to deal with it NOT being empty, you have nothing to deal with it when it IS empty.

If Not IsEmpty(strFoundSpans) Then
  wd.Visible = True

Why do you have the .Visible = True? You already have .Visible = True previously, and have not made it False. Unneeded.

wd.Selection.MoveRight Count:=3, Extend:=wdMove
wd.Selection.EndKey Extend:=wdExtend
strFoundSpansText = Selection.Text

If I am correct, you are moving the Selection right to account for ": " (the colon and two spaces) after SPANS. Why? Why not just include that in the original search string?

Selection.HomeKey unit:=wdStory
With Selection.Find
  Do While (.Execute(findtext:="SPANS:  ", _
      Forward:=True) = True) = True
   [COLOR=red]' collapse the selection
   ' then make it the rest of the paragraph[/color red]
    With Selection
      .Collapse direction:=wdCollapseEnd
      .MoveEnd unit:=wdParagraph, Count:=1
    End With
[COLOR=red]   ' this is your code to bring it back 
   ' to Excel[/color red]
   strFoundSpansText = Selection.Text
   Range("spans").Select
   Selection.Offset(i, 0).Select
   Selection.Value = strFoundSpansText
 Loop
End With

This loops through all found SPANS and makes a new selection of the rest of the paragraph. You can use this to go through all SPANS and transfer the following text to Excel

I do not see any increment of i. Will this not (if you use this loop) keep putting the text into the same cell?


Gerry

 

[vakubia0]

Thanks Gerry,

I was fed up with the coding last night that I did not re-read the thread.

Kind of worked it out that I didn't have the MSword9.0.olb ticked in the references.

I have used your code and it works a treat.

Thanks so being helpful and understanding in my poorly written thread.

 

[fumei]

Ah the reference issue. Glad you got things working.

Gerry