CODEWITHSUNDEEP
excelvbams-wordexcel-2007
Reinstate Monica - Goodbye SE
Reinstate Monica - Goodbye SE

Reputation: 3562

How can I copy one section of text from Word to Excel using an Excel macro?

I need to copy a specific item of text (one or a few words) from Word (2007) to Excel (2007) using an Excel macro, for multiple documents.

So far I have the Excel macro opening each Word document one at a time and locating the text adjacent to what I need.

I now need to:

  1. Move to an adjacent cell in a Word table. I'm thinking wdApp.Selection.MoveLeft Unit:=wdCell (or MoveRight) where wdApp is Word.Application
  2. Copy the contents of the cell. I'm thinking wdApp.Selection.Copy and something like wdDoc.Word.Range where wdDoc is Word.Document but I can't select the whole cells contents.
  3. Paste it into a variable in Excel. Here I don't know how to copy the clipboard to an Excel variable.

Upvotes: 5

Views: 9327

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Updated to show searching for text and then selecting content relative to its location:

Sub FindAndCopyNext()

    Dim TextToFind As String, TheContent As String
    Dim rng As Word.Range

    TextToFind = "wibble" 'the text you're looking for to
                          ' locate the other content

    Set rng = wdApp.ActiveDocument.Content
    rng.Find.Execute FindText:=TextToFind, Forward:=True

    If rng.Find.Found Then
        If rng.Information(wdWithInTable) Then
          TheContent = rng.Cells(1).Next.Range.Text      'move right on row
          'TheContent = rng.Cells(1).Previous.Range.Text 'move left on row
          MsgBox "Found content '" & TheContent & "'"
        End If
    Else
        MsgBox "Text '" & TextToFind & "' was not found!"
    End If

End Sub

Then assign the variable TheContent to your required Excel range.

Upvotes: 6

Related Questions