Link to home
Start Free TrialLog in
Avatar of JohnAeris
JohnAerisFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How Do I Pre-populate a Word Document with External Data?

Hello Experts

Scenario

You take take an objective multiple choice test with options a)  b) or c)

A MS Word report will be written depending on your answers.

To illustrate:

Question 1
If Respondent chooses option a) - Write  "You should really have a check-up!" in the report
If Respondent chooses option b) - Write "This is great news!" in the report
If Respondent chooses option c) - Write "Are you sure?" in the report.

The Challenge
If we had 100 questions, we will have a combination of over 300 possible answers! This will make writing the MS Word report very tedious indeed!

So, what is the best way to pre-populate an MS Word document with the correct responses?

My current thinking is to use MS Excel, SQL Server or SSIS to sort through the answers and match it to the correct response the output the file. I'm a bit stumped at how to then automate the pre-population in the MS Word report!

Maybe it would have to be pre-populated dropdowns?

I am totally open to ideas here! Any thoughts?
Avatar of Steve
Steve
Flag of United States of America image

Drop downs would be a good idea and would make writing the macros easier if you are not real comfortable with vba.
What about writing the Word doc as a template and have it save the answers to separate document? Then you only write it once.
Avatar of PortletPaul
I noticed you refer to "report", so you ARE collecting the data in a structured manner - but wish to produce a "report" for each respondent? Is this what you are doing?

How are you collecting the survey data? In a web form? is this .net? (something else?) do you have programming skills or access to programming skills?

Why does the report have to be a word file? could it be pdf?

The method you choose to collect the data will influence what is sensible to deliver as a report. e.g. if it's collected as a web form, will each user "download" their "report" (i.e. would you expect it to be generated then and there (by software)?

There are commercial reporting (Business Intelligence) products (name escapes me right now) but this may not be of interest. There is a decent "library" for either .net or java that will produce word documents and/or pdfs (aspose word for .net/for java). But that's assuming you have programming skills.

So, more info?
Avatar of JohnAeris

ASKER

MORE INFO:

Structured Data: Data is collected in a structured manner and I wish to produce an individual report for each respondent.
Data Collection: Data is collected via a web-front end that can output collated data in various formats, including CSV, Excel, PDF or Word. The output is not very user-friendly and requires further manipulation before it can be used in reports
Report Format: We have MS Word templates so it would make sense to continue using MS Word. However, if I can solve the challenge using PDF then we will use PDF. Report Format is less important than create a simple solution to generate reports based on responses.
Report Delivery: The web-front end captures the data from a respondent and we expect back office human input to prepare the report and produce a report very specific to each respondent. This is why I’m leaning towards PDF or MS Word as we can simply email the results for each respondent to view or print.
My Skills: VBA, Microsoft SQL Server BI Stack including T-SQL, SQL Server, SSIS, SSRS, SSAS. I don’t have Java or .Net skills.

I hope this helps.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your input! PortletPaul!

I originally considered Mail Merge in Word. However, the results for each individual report maybe a few paragraphs long!

The examples I see online seem to focus around addresses and names!

Can Word Mail Merge handle large swatches of text from the data source?
mmmm, the one BIG missing feature if Word is "conditional text" (like you find in FrameMaker for example).

Ans: In short - not really. You have 2 problems: Excel cell size, and "formatting" within large section of variable text (such as paragraph breaks).

This is where an external program could step in (sorry I keep falling back to that).

So, the final "report" needs conditional text. How big is the biggest such example? If its a mere 1 or 2 hundred chars that might be OK, bigger than that you are in trouble.

Out of interest what is the BI solution that produces Word docs for you now? What file formats does it support? What are the roadblocks to using this?
The biggest paragraph example has nearly 500 words and over 3000 characters!

We’re using Excel 2007 which has a maximum cell size of 32,000+ I believe. Does Word 2007 limit the amount of text it can merge from an Excel Cell to less than 32000?

How would the external program step in at this phase? And I thought FrameMaker produces XML content for the web? Could FrameMaker assist with a solution?

In terms of paragraph breaks and variable sizes, could we get away with splitting the sentences up at source and then merging these into a coherent paragraph in MS Word?

At present there is no BI Solution! The data capture system can create a CSV, flatfile, Excel, PDF or Word format, but as you deduced earlier it is very static and prone to error.

So, we output in CSV (as this can clean the source data using Excel or SQL Server), the contents are then copy pasted directly into a Word template! Very painful indeed! Luckily I don’t deal with that bit :), however, I am charged with creating a better solution!
Ok, several detailed Q's that I don't know detailed answers to, but I sense you have a handle on figuring out that level of detail pretty quick.. 3000 chars might be OK - IF your product ca n output paragraphs to CSV/Excel without breaking - remember you could be dealing with ",CRLF etc in those "cells".

I believe "mail merge" would deal with data of such sizes, and that's easy enough to test - try it.

No BI solution, but "it" (whatever "it" is) puts out word/pdf - OK. I'll dig around on this.

One other approach - now I read your reply would be to "reverse" the poor word, into good word. What I mean is, "it" outputs the poor word (instead of trying csv/excel) and macros in a template "extract" what you need into the cleaner Word document - would depend on the consistency of "it's" word documents. Perhaps a poor idea.

What's the timeframe? is this magic expected tomorrow?
Yes!
It is expected tomorrow! How did you guess? :)

It will be a long, long night...

The "reverse word" idea may work, however, the front-end also exports numbers that drive charts, so makes most sense to export as CSV to generate the charts and handle the text.
darn... i can't offer much more right now - but I have seen articles discussing word macros for "conditional text" in the dim dark past - that might help.

I think at least some mail merge will assist.

Good luck.

btw: looking ahead

http://aspose.com look for "aspose word for ..."
I've tried to find the BI product I had in mind but simply cannot remember it - if I do I'll try and get back to you - but it won't help in the short term
John,

Hope you solved your immediate problem.

I said I'd get back to you regarding a product I had in mind; found it - Winward Reports

see Document generation
and: autotag

I have not personally used any of their products, and there are bound to be competitive offerings - but at least you know there are things out there.

BTW: I've also noticed quite a few Q&A's here at EE regarding 'aspose'
Brilliant! Thanks mate!

I eventually settled on the Advanced Word Mail Merge solution as an interim solution. It meets requirements today and stakeholders are happy.

The reporting is fairly basic right now - which is a blessing! However, an increasing client base portends that we will need to scale up rapidly: preparing detailed reports for 2 or 3 clients to tight deadlines is very different than producing for 20 clients with zero increase in human resource!

As we grow in the coming months, reporting will get far more complicated. I envisage that we will need a stronger interim solution that Word Mail Merge before making the big move to a sophisticated database publishing solution with all the bells and whistles.

Thanks for all your support and diligence!
PortletPaul: Once again, thanks for your assistance! Great work!
really happy to have helped, I guess the final word (pardon pun) would be to seriously discuss the needs with management working towards not only a mif-term solution but a fully fledged  strategic solution. If this is "core business" then even more reason to "do it smart" and better then the competition...

good luck!