Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

vba - 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.
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...