PC Review


Reply
Thread Tools Rate Thread

macro to send data from word to excel

 
 
tory04
Guest
Posts: n/a
 
      14th Nov 2003

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

 
Reply With Quote
 
 
 
 
Dick Kusleika
Guest
Posts: n/a
 
      18th Nov 2003
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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"tory04" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing admin.dataentryoutsourcing@gmail.com Microsoft Excel Misc 0 20th Mar 2008 01:45 PM
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing admin.dataentryoutsourcing@gmail.com Microsoft Access Form Coding 0 20th Mar 2008 01:44 PM
Excel Macro for taking data external data and populating it on a sheet and deleting unwanted data bkmn4u@aol.com Microsoft Excel Programming 3 8th Nov 2007 06:59 AM
My send to in excel/word does not offer send as attachment =?Utf-8?B?TXN0aW5r?= Microsoft Excel Misc 11 16th Mar 2006 03:49 PM
Difficulty Send Email through WORD (SEND TO) Word 2003 =?Utf-8?B?Um9zcw==?= Microsoft Word Document Management 1 16th May 2005 12:45 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:34 PM.