sdbenner


I'm trying to use Excel VBA and open a word document and select a few fields of a table to paste into Excel. Is there a way to search for a certain string and copy the adjacent table fields in Word so I can assign them to a variable for pasting into Excel



Re: Excel VBA open/select text in Word?

Peter Mo.


Hi

My first tip is to try doing it in Word first, and then add the code to Excel.

When you get to Excel VBA you need to reference the Word Library (Tools/References/Microsoft Word Object Library)

The following is an imperpect example of getting everything out of all tables in to the Activesheet.

Sub test()

Dim xla As Excel.Application
Dim wda As Word.Application
Dim tbl As Word.Table
Dim dc As Word.Document
Dim cl As Long
Dim rw As Long
Dim rwE As Long

Dim strFN As String

Set xla = Excel.Application
Set wda = New Word.Application

strFN = xla.GetOpenFilename("Word Document (*.doc), *.doc")
If strFN = "False" Then Exit Sub

Set dc = wda.Documents.Open(strFN)

For Each tbl In dc.Tables

rwE = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1


For cl = 1 To tbl.Columns.Count
For rw = 1 To tbl.Rows.Count
Cells(rwE + rw, cl) = tbl.Cell(rw, cl).Range.Text
Next rw
Next cl

Next tbl

dc.Close
wda.Quit

MsgBox "Done"

End Sub

Regards

Peter Mo