macro to send data from word to excel

[tory04]

Hi,

I am trying to create a summary document of data held in a table at the
start of many word documents.

When a person modifies one of the many word documents they are asked to
fill in 4 cells of the table at the top of that document, recording the
changes.

I tried to record a macro to copy the cells I wanted and paste them
into a summary document in excel, so the user would simply have to
press a button to submit the information they added, to the summary
document.

However, i was unable to select cells of a table in the "record macro"
mode. I then inserted an excel spreadsheet into word for users to log
their changes, so I could reference the cells, but I could not edit the
spreadsheet in "record macro" mode either!

I have also tried creating a linked object but could not make that work
either!

I dont mind whether the summary document is in excel or word format (i
just thought excel would be easier to write to) or whether the table in
the word documents is a table or spreadsheet, could you please suggest
a way of copying the added information in each of the many tables when
a user edits the documents to a summary document??

Thank you!

Tory

 

[Dick Kusleika]

Tory

Here's a sub that might get you started. It assumes that the first table in
your word doc is the table you want to use. It also assumes that the last
row of the table is the row you want to log into the excel workbook. It
uses automation to open the excel workbook so you have to set a reference
(Tools - References) to the Excel object library.

Sub LogTable()

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim xlRng As Excel.Range
Dim Tbl As Table
Dim i As Long

'Use the first table in the document
Set Tbl = ActiveDocument.Tables(1)
Set xlApp = New Excel.Application

'open the log workbook
Set xlWb = xlApp.Workbooks.Open("C:\Dick\WordLog.xls")
Set xlSh = xlWb.Sheets("Sheet1")

'Find the next open line on the sheet
Set xlRng = xlSh.Range("A65536").End(xlUp).Offset(1, 0)

'Loop through the columns and transfer the text
'to excel
For i = 1 To Tbl.Columns.Count
xlRng.Offset(0, i - 1).Value = _
Tbl.Cell(Tbl.Rows.Count, i).Range.Text

Next i

xlWb.Save
xlWb.Close
xlApp.Quit

End Sub