How to Create MS Word Documents from MS Access

Programming a dialog with the Access VBA (Visual Basic for Applications), used in all of Office to write macros, you can also export data from one or more tables to a Word document using a template with form fields. We see the process with the main steps. First we have to open at runtime using a COM object of our model and create a document on the fly, to do this is required as a reference the Word object, of course, write macro in menu Tools -> References, find something or Word Object like the ocean of COM objects is checked.

Dim myApp As Word.Application, myDoc As Word.Document
Set myApp = CreateObject("Word.Application")
Set myDoc = myApp.Documents.Add("mymodel.dot")

With the first line of code we have scaled an object myApp of type Word.Application to manage the main window of Word and an object myDoc of type Word.Document to manage the document with form fields. On the next line instantiates the variable myApp and create the Word Application object and third initialize my document with my model.

Dim myData As DAO.Database, myRec As DAO.Recordset
Set myData = CurrentDb
Set myRec = myData.OpenRecordset("SELECT * FROM Table WHERE IDValue = X)

The first line of instruction dimension a variable myData of type object DAO.Database and myRec of type DAO.Recordset, even for these instructions are references to objects (DAO Data Access Object), the old way to access data from Microsoft, in ‘reference list. The second line assigns the current database to the variable myData, while the third line opens the connection to the table (in the example I took a table generic) data by selecting values in a SQL (Structured Query Language).

myDoc.FormFields("Form Field Name").Result = myRec![Field Name Table]

This last statement can be inserted in place of the form field “Form Field Name” field value “Field Name Table” of the database and you’re done, all with VBA. At the end you must close the resource allocation, both the database and the Word application and display the Word application in all its glory with a document filled with data from the database.

myApp.Visible = True

myRec.Close
Set myData = Nothing
Set myRec = Nothing
Set myDoc = Nothing
Set myApp = Nothing

If you want you can also keep invisible the Word application and print the document on the fly and then remember to close the Word application with myApp.Quit, in our case.

Tecnorati Claim 62J7Z54XN3MW