An astute user recently pointed out that buried on the Customize Ribbon tab of the Word Options dialog box is an otherwise hidden command, Insert Database, that lets you create a table in a Word document and populate that table with the results of a database query:
You can do all this by making a few selections in the Database dialog box that opens when you choose that command from the customized ribbon:
You might also not be aware that you can accomplish the same thing programmatically. The following code sample shows how to insert a table into a Word document that displays the contents of the Category table from the Northwind sample database (Microsoft Access):
Sub InsertCategoriesTable()
'
' Insert database table into document
'
Selection.Range.InsertDatabase Format:=11, Style:=191, LinkToSource:=False, _
SQLStatement:="SELECT * FROM `Categories`" & "", _
DataSource:="C:\Users\username\Documents\Access Databases\Northwind.accdb", _
From:=-1, To:=-1, IncludeFields:=True
End Sub
The sample uses the InsertDatabase method of the Range object to insert the results of a database query into a table at the selected location in Word. The InsertDatabase method takes a number of optional parameters that let you customize both the database query and the format of the table to be inserted, as shown in the following table:
In the sample, I’ve passed values for the Format and Style parameters that result in the table looking like this:
The value passed for the SQLStatement parameter, "SELECT * FROM `Categories`", tells Word to query all the records in the Categories table in the Northwind database. The values I’ve passed for the From and To parameters, 1 and –1, tell Word to display all the records in the database table.
Because I’m querying data from Access, it is not necessary for me to pass a value for the optional Connection parameter. See the InsertDatabase topic for more information about this parameter.