Form Referencing excel spreadsheet from word

[gravelj2]

This is more of a Microsoft word question since I'm actually pulling data from excel rather than pushing it.

Excel 2013 Table is a template to calculate those values.
1. Update the excel template
2. Click update the first line on word
3. Save excel template as different name
4. Repeat but use a new excel template and only update line 2 from word.

I'd like those links to be stuck with the sheet I updated them to. So if i were to change something in the saved excel workbooks I could update it just as easily. Can I save line 1 to reference book1, line 2 to reference book2, etc...

Word 2013 Template table - all same reference (manually updated to achieve the different numbers)

Reference vegetable from excel	quantity of vegetable from excel
Reference vegetable from excel	quantity of vegetable from excel
Reference vegetable from excel	quantity of vegetable from excel

<tbody>
</tbody>

 

[shyy]

Post sample screenshot of both your excel and word so we can better assist you/

 

[gravelj2]

The pictures really have nothing to do with it, because it's really the process but here they are.

vegetable	seed type	cost per seed
beans	2	89
corn	4	73

<tbody>
</tbody>

Word

beans

2

89

<tbody>
</tbody>

excel

They both begin as templates.
1. I open the form from the template in word.
2. I open the other form from the template in excel.
3. Update the numbers to calculate everything in excel.
4. Manually update the row in word to insert the values from excel. (Links are pasted to word template as unformatted text)
5. Open a new excel form using the same template but calculate the row for corn.
6. Manually update the row for corn in word.

Sometimes I need to go back and recalculate or adjust the beans row (word) and I'd like to be able to go back to the beans workbook to adjust it but the way the links are setup right now, they only reference the excel template. The links from excel are pasted into word but they're set to manual so when I reopen the word document, everything isn't zeroed out.

 

[shyy]

Are you trying to mail merge but with excel? If so I have a macro that will use the name range in excel, you just have to edit the field codes on the word template. You have to create a new document using the merge data. You have to use shortcut to do this, since Word doesn't show you the typical mail merge options such as preview.

 

[gravelj2]

I don't need a new document for every row though. I often times add or delete rows in the word document so I can calculate more vegetables from excel. I just need to find a way to link each row in the Word document to the different excel spreadsheets(rather than the template) with the click of the update button.

Let's call the word document "Farmer1.docx" and each row represents a vegetable.
The excel Workbook to calculate "Beans" would be saved as "Beans.xlsx"

I'd like to be able to click the update (or a macro button) to change the update link for the row (beans in this case) to that exact excel workbook (beans.xlsx) instead of having to paste the unformatted link for every single cell for every new workbook.

Is there any way I can use a push system rather than a pull to push my data from an excel workbook to a word document with the click of a button? I would need the workbook to sometimes create a new link from a fresh form or update an existing one. There would be a serial number that would be unique to that word document and all of the workbooks that calculate the vegetables on that word document if that helps?

 

[gravelj2]

Does anyone know how to push data from excel into a word template?

 

[shyy]

I no longer have the password to the VBA but can send you the macro and you can alter the word template. You have to create a new document using the merge data. You have to use shortcut to do this, since Word doesn't show you the typical mail merge options such as preview. It is limited for editing unless there is a way around I don't know of.

 

[gravelj2]

I don't think that's what i'm looking for but maybe it'll give me some ideas?!

 

[shyy]

look into this, takes a range and pastes in to word

Sub Create_msword_for_Kia_Report()
Dim appWD As Word.Application
Set appWD = CreateObject("Word.Application.14")
appWD.Visible = True
appWD.Documents.Add


Sheets("For Kia's Report").Select
Range("a1:g9").Copy
appWD.Selection.PasteSpecial DataType:=wdPaste
appWD.ActiveDocument.SaveAs Filename:="C:statusreport.doc"
appWD.ActiveDocument.Close
appWD.Quit
End Sub