+ Reply to Thread
Results 1 to 11 of 11

Macro to copy data within excel into tables in a word document

  1. 01-25-2010, 06:34 PM #1
    rpt21
    rpt21 is offline
    Registered User
    Join Date
    01-25-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010 and Excel 2007 (Windows 7 and XP mode)
    Posts
    9

    Macro to copy data within excel into tables in a word document

    Hello all,

    Currently I am going back and forth between an excel document and a word document cutting and pasting values from the excel spreadsheet into tables in word. The task is a regular occurance therefore I wish to create a Macro that can automate this procedure. Both the excel and word documents are fixed templates therefore once a Macro is created it can be applied to all future work of similar nature.

    Each table in the word document contains 6 rows of values in a single column. The excel data is arranged in a table that is 6 rows by x number of columns (how ever many sets of values there are for the particular job) therefore x determines how many tables must exist in the word document. I move between the excel and word document cutting and pasting each column into each table. This is not so time consuming if x=10 however on occasion x=100+ and it does take time.

    Would love to automate this.

    Any help is much appreciated,

    rpt21
    Reply With QuoteRegister To Reply
  2. 01-25-2010, 10:13 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy data within excel into tables in a word document

    Hello rpt21,

    This macro runs from Excel. It lets the user select the Word document to open. I can change this to open a specific file, but I need to know the file path and document name. The data on "Sheet1" is assumed starts in column "A1:A6". The number of columns to the right are discovered automatically by the macro. The same number of tables are expected to exist in the Word document. The Excel cells are copied into each Word table. Copy this code into a VBA module in your Excel workbook.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 01-26-2010, 04:22 PM #3
    rpt21
    rpt21 is offline
    Registered User
    Join Date
    01-25-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010 and Excel 2007 (Windows 7 and XP mode)
    Posts
    9

    Re: Macro to copy data within excel into tables in a word document

    Thanks Leith,

    I almost have it working, it seems to bug at "wdTbl.Range.Cells(R).Range.Text = Rng.Cells(R, C)". I neglected to mention that each table in the word doc has 2 columns, the first with the data labels and the second where the values need to be inputted. The code began by putting the first column of data from excel into the first column and row of the first word table (A1) then B1 then A2 then B2 and so forth before stopping at the next table.

    Also is it possible to keep number formats from excel?

    much appreciated!

    rpt21
    Last edited by Cutter; 07-27-2012 at 09:19 AM. Reason: Removed whole post quote
    Reply With QuoteRegister To Reply
  4. 01-26-2010, 07:38 PM #4
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy data within excel into tables in a word document

    Hello rpt21,

    I changed the macro to copy the Excel data into the second column. The change is marked in blue.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 01-26-2010, 08:42 PM #5
    rpt21
    rpt21 is offline
    Registered User
    Join Date
    01-25-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010 and Excel 2007 (Windows 7 and XP mode)
    Posts
    9

    Thumbs up Re: Macro to copy data within excel into tables in a word document

    Excellent thanks! It's functioning now, just hoping to keep the number formats (some are currency, some have 1 decimal places, some have 2 dp etc) and also only paste into every 4th table in the word document?

    thanks again,

    rpt21
    Last edited by Cutter; 07-27-2012 at 09:19 AM. Reason: Removed whole post quote
    Reply With QuoteRegister To Reply
  6. 01-26-2010, 09:07 PM #6
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy data within excel into tables in a word document

    Hello rpt21,

    Sorry, I forgot about the format part. Are you saying the macro needs to copy the data to every fourth table in the document?
    Reply With QuoteRegister To Reply
  7. 01-26-2010, 09:58 PM #7
    rpt21
    rpt21 is offline
    Registered User
    Join Date
    01-25-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010 and Excel 2007 (Windows 7 and XP mode)
    Posts
    9

    Re: Macro to copy data within excel into tables in a word document

    yes correct
    Last edited by Cutter; 07-27-2012 at 09:18 AM. Reason: Removed whole post quote
    Reply With QuoteRegister To Reply
  8. 01-27-2010, 03:14 AM #8
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy data within excel into tables in a word document

    Hello rpt21,

    I revised the macro to fill in every fourth table starting with first table in the document. The cells are now pasted from Excel into Word to preserve formatting.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 01-27-2010, 04:46 PM #9
    rpt21
    rpt21 is offline
    Registered User
    Join Date
    01-25-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010 and Excel 2007 (Windows 7 and XP mode)
    Posts
    9

    Re: Macro to copy data within excel into tables in a word document

    Excellent thanks!
    Last edited by Cutter; 07-27-2012 at 09:17 AM. Reason: Removed whole post quote
    Reply With QuoteRegister To Reply
  10. 07-27-2012, 05:01 AM #10
    Salim Sayed
    Salim Sayed is offline
    Registered User
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Macro to copy data within excel into tables in a word document

    The said macro runs fine, but how to use this if I have a list of columns in excel (like 6 headers) and around 20 rows.. When I run this macro it only copy pastes the first column but the remaining data in the 2nd, 3rd & 4th column...
    Reply With QuoteRegister To Reply
  11. 07-27-2012, 05:33 AM #11
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy data within excel into tables in a word document

    Salim,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1