Extracting Data from Word to Excel.

[Guest]

I need to copy data from one cell in a table from a Word document into an
Excel spreadsheet. Can anyone respond with tips (or sample code) that can
help with this?

Thank you!

 

[Dick Kusleika]

jk

Here's one way

Sub GetDataFromWord()

'Set a reference (Tools - References) to the
'Microsoft Word x.0 Object Library

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim sFile As String
Dim rInput As Range

'Define row and column of data in table
Const lROW As Long = 2
Const lCOL As Long = 2

'Specify file that contains table
sFile = "C:\Dick\NG\01 Jan\GetData.doc"

'instantiate Word and open document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(sFile)

'define range where data goes
Set rInput = Sheet1.Range("a1")

'Copy value from table and paste to cell
With wdDoc.Tables(1)
.Cell(lROW, lCOL).Range.Copy
rInput.PasteSpecial xlPasteValues
End With

wdDoc.Close False
wdApp.Quit

Set wdDoc = Nothing
Set wdApp = Nothing

End Sub

 

[Guest]

That worked great!

Thank you!!

jk

Here's one way

Sub GetDataFromWord()

'Set a reference (Tools - References) to the
'Microsoft Word x.0 Object Library

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim sFile As String
Dim rInput As Range

'Define row and column of data in table
Const lROW As Long = 2
Const lCOL As Long = 2

'Specify file that contains table
sFile = "C:\Dick\NG\01 Jan\GetData.doc"

'instantiate Word and open document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(sFile)

'define range where data goes
Set rInput = Sheet1.Range("a1")

'Copy value from table and paste to cell
With wdDoc.Tables(1)
.Cell(lROW, lCOL).Range.Copy
rInput.PasteSpecial xlPasteValues
End With

wdDoc.Close False
wdApp.Quit

Set wdDoc = Nothing
Set wdApp = Nothing

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com