macro to send data from word to excel

Discussion in 'Microsoft Excel Programming' started by tory04, Nov 14, 2003.

  1. tory04

    tory04 Guest

    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/
     
    tory04, Nov 14, 2003
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:...
    >
    > 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/
    >
     
    Dick Kusleika, Nov 18, 2003
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve
    Replies:
    3
    Views:
    962
    Jim Cone
    Jul 6, 2006
  2. jsd219

    convert a Word macro to an Excel macro

    jsd219, Oct 23, 2006, in forum: Microsoft Excel Programming
    Replies:
    24
    Views:
    449
    Dave Peterson
    Oct 27, 2006
  3. Guest

    Re: Excel Macro call Word Macro with Parameters

    Guest, May 24, 2007, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    435
    Guest
    May 24, 2007
  4. Replies:
    3
    Views:
    678
  5. Michael

    Converstion of Complex Word VBA macro to Excel Macro

    Michael, Jan 16, 2008, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    270
    Jon Peltier
    Jan 18, 2008
Loading...

Share This Page