How to go about this depends a great deal on where the data is and what
you need to do with it.
Excel has extensive facilities for fetching and displaying data from SQL
Server tables and views (look at the Data tab, "From other sources"
option). However, if you need to include data from all sorts of
different parts of your database, you will either need to use multiple
queries - either putting the results of each query directly where you
need them, or for example putting the result of each query into a
separate worksheet then referencing the data.
Word has some built-in facilities if you want to get, say, a table of
records from SQL Server and use them to generate Form Letters. You'll
find that stuff in Word's Mail Merge feature. You can also use the Mail
Merge mechanism to insert different pieces of data from different parts
of your database in different parts of the document, as long as you are
capable of creating the necessary query/view that can retrieve all those
pieces of data in a single row, using a single SQL SELECT statement.
There is also a facility in Word where you can insert the entire content
of a table or view in a Word table format, but you will have to dig
around in Word Options->Customize to find those features. You used to be
able to use that facility to insert individual pieces of data from a
data source in different parts of your document, but Word now always
inserts a paragraph mark along with the data which has more or less
scuppered that approach.
In both Word and Excel you will probably find that you have to create
things called .odc files (Office Database Connection or some such) in
order to get data from SQL Server. Although you shouldn't have to learn
the XML/HTML used in .odc files,
a. you may find that it is not completely obvious in Word how to
create them
b. generally speaking, Excel works better with .odc files than Word.
Word does not conform to all the standards and sometimes will not
retrieve any data at all. There are workarounds for some of this stuff,
but if you encounter problems it is sometimes not a bad idea to think in
terms of using Excel to retrieve the data before re-using it in Word.
Do I need to learn the programming language behind MS
Office?
Not for simple uses. Excel has its "spreadsheet function language" which
enables you to reference databases/lists in various ways. To work
effectively with SQL Server you are likely to need some SQL (rather
obviously!) and perhaps some of SQL Server's procedural language
Transact-SQL.
Beyond that, you can typically do what you need, programming-wise, in
both Word and Excel using the built-in language VBA. This is the same
/language/ in both Word and Excel (and other Office software) but the
language has, if you like, a different vocabulary in each product to
reflect the different objects and features of the product. To get data
directly from SQL Server using VBA, you would typically use a thing
called ADO and "open an ADO connection."
For some types of task, e.g. advanced use of Word's "Content Controls",
you may be better off using something like C#.NET or VB.NET with a thing
called VSTO. However, if you go there you will be faced with a fairly
steep and lengthy learning curve. IN the .NET languages, you would
typically use a thing called ADO.NET to retrieve data directly.
Peter Jamieson
http://tips.pjmsn.me.uk
Visit Londinium at
http://www.ralphwatson.tv