Link data from e.g. Excel (or sharepoint) into a Word report. Best Practice?

Copper Contributor

Hello guys,

I want to create a report template for my work where I can simply fill in the data I want to have in my report into a excel sheet
and then it gets automatically inserted into my word report at the correct place and with the correct formatting.

I already tried to use the data linking between excel and word but it is pain in the ass (extremly long loading times when opening the document, and the workaround is just to difficult for the whole company). So I now wanted to ask you if you have any idea how such a thing can be done in a simple and working way. We also use a microsoft sharepoint system which could be used to achive this goal or to provide a mask to fill in the data for the report.

I really hope somebody can give me some hints how I can achive this goal and create a generic report, where only customer data and all other individual data can be inserted by just typing the data in the correct excel cell or something like this.

Thank you very much in advance.

Max

1 Reply

I realize this question was posted several months ago, but perhaps you're still having this challenge or others may stumble across the same challenge, so I figured I'd respond.

 

My recommendation would be to use the Mail Merge feature in Word for this. When using the mail merge wizard, you can select a report template that can be modified to your liking then use your Excel spreadsheet as the source of data that fills the report.

 

Here's an article that explains how to do this: http://www.progenygenetics.com/knowledgebase/index.php?/Knowledgebase/Article/View/639/12/how-do-i-e...

 

Another option would be to create the report in Excel instead of Word. If these are monthly reports where you enter the data for each month on one spreadsheet, you could use a dropdown list on the report page to select the month & year, then use Vlookup to populate the data fields. However, this would not be practical if you needed to run multiple reports for multiple people, say for example, a commissions report for a team of salespeople where they'd each get their own report. But there are other ways you could make it work, either using formulas or macros.