I would like some help to point me to best direction to get this project started.

The basic need is that my user would like to print out an editable report using database data.   He would like to adjust some of the data and save it for later, but some data he would like to be able to repeatedly update as the database updates. 

So the report would be a template with some stock text, layout, and logo.  Some database data would fill in some fields and perhaps table headers.  He would like to adjust these and have them remain changed for the life of the document.  An itemized list with item details will also be pulled from the database, but this needs be refreshed independently. 

I am thinking of a Word document template using a document query.  He would need to provide a parameter to drive the data query (an ID value), and Im not sure how to do that part.  Also, I was thinking of maybe including an embedded excel spreadsheet for the itemized info to separate the data that will be updated and the data what will be user changed (so that it is not overwritten in an update).

The full meal deal would include the ability to spawn the Word template from another application (shell exec?). Ideally the calling application could provide the parameter to the Word template.  The Word template opens on his screen where he can edit and save it where he wants.  He comes back later and can press an update button to update the items on the Word document.

I am sure this has been done a million times and I can think of a few ways to do this. Ive done several parts of this project before, just not all put together and with the recent products.  But I am having some difficulty deciding which are the most viable methods to pursue.

Technologies I was considering/am using include Word 2010/2013, SharePoint 2010/2013, SQL Server 2008R2/2012, C#, Windows 7.

I appreciate your help and sugges

Have you considered using Word's mailmerge tools to generate the fixed data and a DATABASE field for the volatile data? Both can be used in a mailmerge main document. Except for the field coding, no programming required. For a working example of the DATABASE field, see: http://www.msofficeforums.com/mail-merge/21847-mail-merge-into-different-coloumns.html#post67110.

Thank you for the suggestion and the great links! I'll try them out. It sounds like it should work for me.

Paul, could you suggest how I could set it up so that Word accepts a parameter?  Typically I use VB to collect that kind of stuff from the user, but I would a) prefer not to use VB and b) prefer to allow it to work without user input: collect the through a command line switch or something (shellexec to winword.exe?).  Thanks again!

I was envisaging a process in which one would open the Word document and execute a mailmerge that uses a SKIPIF field, in conjunction with a FILLIN field to extract the relevant project info. Such a SKIPIF field might be coded along the lines of:
{SKIPIF{MERGEFIELD Project}<> {FILLIN "What is the project number to use?" \o}}
The DATABASE field would likewise reference {MERGEFIELD Project} to filter the data it would use.

Note: The field brace pairs (i.e. '{ }') for the above examples are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.

However, you could also automate Word and the mailmerge via a vbscript, passing the relevant project parameter to the merge process, such that it could be referenced by both the mergefields and the DATABASE field. Instead of using a FILLIN field, your code might populate a custom document property and you'd reference that via a DOCPROPERTY field in place of the FILLIN field. Or you could simply automate the merge process and use the same SKIPIF & FILLIN field setup, with the user inputting the project details when prompted.

Thanks Paul.  Could you please expand on your suggestion:

you could also automate Word and the mailmerge via a vbscript, passing the relevant project parameter to the merge process, such that it could be referenced by both the mergefields and the DATABASE field.

This document works like a customer order letter.  The users want to edit some paragraphs that would incldue the customer name, but also have a table of the items they ordered.  Each document would represent their order using an order#.  Users would create and manage multiple documents each week. 

I would want to avoid having to ask the user for the order# each time they refresh the item list, it will always be the same order# for that version of document.  Ideally, if I can automatically generate (C# or vbs) Word docs with the db queries already setup that would be my goal.

How could I use VBScript to pass the order number to a Word template?  Also, would automation this be accomplished with VBA in the Word template file?

Given that your users need to use Word to maintain the orders, I'm not sure why you want to use vbs or C# to generate the initial document. Why not use Word itself and, to the extent necessary, VBA?

As for passing the relevant project parameter, the document in the link I posted has three DATABASE fields coded along the lines of:

{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept], FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] = 0 ORDER BY [Last] " \l "9" \b "47" \h }

In the above example, 'WHERE [Due] = 0' controls which records will be reported. Now, although it would be possible to change the '0' to, say, '1' programmatically, using VBA, C# or vbscript, one could more easily update a custom document property that holds the 'Due' value. A DOCPROPERTY field could then reference the 'Due' value and the DATABASE field could be coded as:
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept], FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] = {DOCPROPERTY Due} ORDER BY [Last] " \l "9" \b "47" \h }

With this approach, the DATABASE field allows the data in the order document to update any time the data in the database updates. In your case, the custom document property would hold the order#. For VBA code to create and/or update a custom document property see: http://support.microsoft.com/kb/212618/en-us. That code can easily enough be adapted to vbs.

PS: On reflection, a mailmerge on its own wouldn't be suitable because the DATABASE field would get unlinked & converted to an ordinary table when the merge executes. There are ways around this using VBA/vbs but, if you have to do that, you may as well drive the whole process programmatically.

Thanks Paul,

I appreciate your help with the direction; I will investigate these further.