# Office 365 >  > [SOLVED] Pulling data from Excel into word

## Kyhosa

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

----------


## kev_

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?

----------


## Kyhosa

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

----------


## kev_

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?

----------


## kev_

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.
```

----------


## Kyhosa

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.

----------


## kev_

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  :Smilie: 




```
Please Login or Register  to view this content.
```

----------


## Kyhosa

That is exactly what I wanted!  :Smilie: 
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.

----------


## kev_

You are welcome  :Smilie: 
Please mark the thread as solved

----------


## Kyhosa

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?

----------


## kev_

*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.
```

----------


## kev_

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

WordInsetBookMarks.jpg

----------


## Kyhosa

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

----------


## kev_

You will require mail merge as I first thought.

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



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

----------


## kev_

I was not thinking clearly earlier  :Confused:  :EEK!: 

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  :Smilie: 

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.
```

----------


## Kyhosa

Yes that is what I am looking for  :Smilie:  

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!

----------


## Kyhosa

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

----------


## kev_

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

----------


## kev_

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.
```

----------

