-3

I am trying to generate a Word document that consists of Excel information. When the Excel document updates I would like for the Word document to do the same.

I have managed to setup a template in Excel which carries over the information into Word seamlessly. The formatting is still a struggle though and if the Excel document changes I need to reproduce the whole Word doc from scratch again.

I am wondering whether it is possible to create the template in Word and "connect" it to the Excel (as the underlying information) easily using VBA?

What are some of the best practices or suggestions to try and go about doing something like this.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Renier Wessels
  • 131
  • 11

1 Answers1

-1

In Word, on the Insert tab, look at Insert Object. You have the option of inserting / linking part of the contents an Excel worksheet in the Word document. Once linked, whenever you open the Word document, its contents is automatically refreshed with the latest contents from the linked Excel worksheet.

The only thing that you have to be careful about with this solution, is that if you move your Excel files with respect to the Word document (to another folder), then when you open the Word document, Word will not be able to find the Excel documents. When implementing a solution like this, both the Word document and the linked Excel documents are typically kept / copied / moved together.

RobertBaron
  • 2,817
  • 1
  • 12
  • 19
  • Please note that Stack Overflow is for *programming* Q&A. If the solution is in the end-user range, such as suggested, this does not belong in an Answer. It can go into a comment, but the discussion is then off-topic for this site. In any case, this question does not meet the site standards and should be flagged to close. In essence, it's asking for someone to write code, showing no effort or research. The site guidelines for asking and answering can be found in the [help]. Please assist in maintaining the site's quality so that it remains a useful *programming* resource. – Cindy Meister Jul 02 '19 at 13:00
  • The answer I'm looking for. I can easily share many lines of code, but they're not applicable. I've used: wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste I guess I'm asking how can I do the following from the Excel document: wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement:=wdInLine, DisplayAsIcon:=False Or similar. – Renier Wessels Jul 10 '19 at 09:56
  • @Renier Wessels - See [Open and Close Word Document from VBA Excel](https://stackoverflow.com/questions/51369408/open-and-close-word-document-from-vba-excel). Create Word object, open document, then you can programmatically manipulate it. – RobertBaron Jul 10 '19 at 10:51
  • Managed to get everything working. Thank you for the help. – Renier Wessels Jul 10 '19 at 12:51
  • @Renier Wessels - Great! Good luck! – RobertBaron Jul 10 '19 at 13:13