+ Reply to Thread
Results 1 to 19 of 19

Pulling data from Excel into word

  1. #1
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Pulling data from Excel into word

    I know there is a way to pull data from excel and into a word template. What I am trying to figure out is how to do it when new rows of information continually get added to an exel spreadsheet, and everytime a new row is added I need to pull that row of info and place it into a word template. Then I can save the word doc with this information.

    Thank you

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    You need to use Mail Merge

    2 ways to attack the problem
    - update Word file when new row is added to Excel sheet
    OR
    - update Word file only when Word file is opened

    Which is your choice?
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Re: Pulling data from Excel into word

    Update the word file when it is opened. I would need a new word document for every row entered.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    A few questions to make sure we go in the correct direction from the beginning:
    1. What needs putting in the word document - is it only the new row or is it all rows on the worksheet?
    2. Is it the whole row(s) or only the values from specific columns?
    3. What is the naming convention for the next file?
    4. What should trigger the new file to be created - a button to click? Auto-trigger when something is entered in the last cell of a row?
    5. Do you already understand how to create mail merge manually?
    6. Do you have a mail merge template set up?
    Last edited by kev_; 03-22-2017 at 05:36 PM.

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    It may be possible not to use mail merge or it may be easier to use it - depends on what you are needing to do
    The attached file illustrates a straightforward copy and paste.


    Open the attached file, enable macros.

    Put the cursor anywhere below row 1 in the table and run the macro with {CTRL} q

    It creates a word file consisting of the Header Row and the row the cursor is placed in.
    It save the file and names it with a default name + the row number (formatted as 0000 for consistency of appearance)

    You need to add the word object library to the VBA references to allow Word to be managed from Excel
    Scroll down and click in the box


    WordObjectLibrary.jpg



    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Re: Pulling data from Excel into word

    1. It will only be the new row
    2. It will mostly be the whole row, minus a few columns, but they are at the end
    3. What is the naming convention for the next file?
    4. Button click would be best
    5. No I do not
    6. I have attached a small example of what I need.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    See attached workbook and see if it gives you what you want.
    It is a modification of the macro I posted earlier.

    Place cursor in any cell in any row
    Click on the button
    If you have selected the wrong row you can change the value in the input message box
    A table is created in sheet "TempValues"
    Table is copied to a new Word document which is saved
    You are given an option to close the Word file

    Does this give you what you want in Word?
    It creates a 2column X 4row table
    Do you need extra columns or rows in the table in Word?

    If it's not perfect, let me know - I have elected for simplest option first

    Please Login or Register  to view this content.
    Last edited by kev_; 03-23-2017 at 01:02 PM.

  8. #8
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Re: Pulling data from Excel into word

    That is exactly what I wanted!
    It works perfectly and I never thought to add a 2nd worksheet in excel to pull the data I want. I still need to test it out on a bigger Word Template, but I can't imagine it not working. I will let you know.
    Thank you for all of the help, you just made my life a little easier.

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    You are welcome
    Please mark the thread as solved

  10. #10
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Re: Pulling data from Excel into word

    Is there a way to run this for an already set up word form that needs to get filled in with the information from the excel spreadsheet?

  11. #11
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    This is what the macro does - please let me know if you want to do something slightly different
    - opens an existing word file
    - pastes the values from Excel at pre-specified BOOKMARK
    - saves the file under a new name

    For test no 1
    1. leave the macro unchanged
    2. copy the Word file that you want to use to the same folder as the Excel Workbook
    3. Rename that file "AddExcelTableInThisFile.docx"
    4. Place a BookMark named "ExcelTable" into the word file, save it again and close it
    5. Run the macro as previously

    Does that work in the way you want it to?



    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    For some reason the icon would not let me attach this to previous post!

    WordInsetBookMarks.jpg

  13. #13
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Re: Pulling data from Excel into word

    It is getting closer and I should have provided a better example to start with. I have attached a Word document that has a table, and the information that is being pulled in Excel, goes into some of those cells. I know I have seen online to add Labels and that is how it would add the data to correct areas. Thank you again for your help. I hope this makes sense
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    You will require mail merge as I first thought.

    EDIT - the above sentence is totally incorrect! - please see next post



    Before getting to the VBA familiarise yourself with how mail merge works.

    To make it easier for me to help you:
    - use the Word document that you attached to your previous post - copy and rename it "MailMergeExcelToWordTemplate.docx" - easier if we are looking at the same thing.
    - copy the Excel file we have been using and rename it "MailMergeExcelToWordData.xlsm", delete the 2nd worksheet leaving only sheet "Table", then save and close it and use this as the source for your data
    - put both files in the same folder


    Here are some images to point you in the correct direction (also there is a lot of help online)
    Save the document between steps 7 and 8 -save it as an ordinary word document - this will become the template file.

    Step 8 opens a new document containing all records merged (probably called Letters1) - allows you to see how everything will look

    When you close the Template file and re-open it you will get a message "Opening this document will run this SQL command..." - that is normal - click OK

    I will have a couple of questions for you later before I amend the VBA.

    MailMergeA.jpg

    MailMergeB.jpg

    MailMergeC.jpg

    MailMergeD.jpg
    Last edited by kev_; 03-25-2017 at 06:00 PM.

  15. #15
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    I was not thinking clearly earlier

    Here is how to achieve what you want without Mail Merge

    - The attached Word File contains 4 bookmarks where the text is required to go
    - Place the attached 2 files into the same folder.
    - Open the Excel file and click on the button etc
    - Have a look at the newly-created Word file - it must be pretty close to what you want

    If that is ok - we need to sort out how VBA is to decide which Word template file to open - it looks as though each row's values goes into a file with some information already in it - and so I'm thinking VBA needs to be given a "row-specific" way to find the correct file. Do you want to browse to it or is there a way that VBA can work that out for itself if we give it some rules?

    Please Login or Register  to view this content.
    Last edited by kev_; 03-25-2017 at 06:01 PM.

  16. #16
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Re: Pulling data from Excel into word

    Yes that is what I am looking for

    As for finding the specific file, I would say either way would work. I would say if anything giving it a rule would be great, but I understand if that is difficult.

    I can't say this enough, but thank you so much for the help!

  17. #17
    Registered User
    Join Date
    07-15-2016
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    80

    Re: Pulling data from Excel into word

    Actually...the more I think about it browsing would be the best way.

  18. #18
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    OK , I'll amend the VBA and post it tomorrow

  19. #19
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Pulling data from Excel into word

    Try this
    - workbook with amended code is attached
    - if there is anything that is not ideal - let me know
    (eg Is the default folder the best one for browsing to start at? etc)

    After we finalise the code I will provide extra notes so that you understand every line - which should make it easier for you to amend it as things change.


    Amended code:
    - contains file browser
    - marker added in column G to show row has been saved (thought this might be useful)
    - Word file closes automatically

    If you prefer not to rename the file (ie update the template file), merely delete these 3 rows:
    Please Login or Register  to view this content.
    This row was amended to save the file as it closes and so this takes over from the above (currently saves the file a second time under its new name)
    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-11-2016, 07:40 PM
  2. Pulling information from word to Excel
    By Maddie77 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2016, 12:38 PM
  3. Pulling all data left of a certain word surrounded by spaces
    By excellerant8 in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 10-26-2015, 01:48 PM
  4. [SOLVED] Word pulling data from Excel: For-Each loop on read input
    By Phixer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2014, 04:19 PM
  5. Replies: 1
    Last Post: 04-04-2012, 11:14 PM
  6. Pulling data from a sheet into Word in a specific order
    By schallpattern in forum Excel General
    Replies: 0
    Last Post: 06-09-2009, 03:29 PM
  7. Word userform pulling data from Excel
    By Freaky_zoid in forum Word Programming / VBA / Macros
    Replies: 3
    Last Post: 04-02-2009, 05:38 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1