Talking to Word from Access

Access offers many benefits, but it doesn't have a good way to create a report containing arbitrary text and formatting. With automation, it's easy to work around that limitation; Microsoft Word is perfect for creating whatever reports you might like, and it's an automation server. Figure 21.4 shows a simple Access form that enables you to enter any text you like and a filename. When you click the button, it creates a Word document containing the specified text and saves it to the filename that you supply.

Figure 21.4. Access form to create a Word document.

The code behind the button is once again straightforward:

 Private Sub cmdCreate_Click()   ' Copy the text to a Word document   Dim objWord As Word.Application   ' Instantiate Word and a new document   Set objWord = New Word.Application   objWord.Documents.Add   ' Insert the text after the default range,   ' which is the start of the document   objWord.ActiveDocument.Range.InsertAfter (txtText.Value)   ' Save the file   objWord.ActiveDocument.SaveAs (txtFileName.Value)   ' And show it   objWord.Visible = True End Sub 

The only trick here is knowing enough of Word's object model to make it do what you want. The key steps are creating a document and adding text to the document. To create a document, you call the Add method of the Documents collection. Adding text is a bit trickier. Word documents contain a Range object, which represents the range of text that's currently selected. The Range object has a variety of methods for adding text, including InsertAfter and InsertBefore. When you first create a document there's no text in it, so of course the Range object just points to the very start of the document. Inserting text after this point does the job that you want. Figure 21.5 shows the results.

Figure 21.5. Word document created from Access.

CASE STUDY: Using Excel Chart Features from Inside Access

To show how automation can be used to enhance an application, this case study adds a button to TimeTrack's switchboard form. This button opens Excel and creates a chart showing how the hours worked divides among the various employees of the company. The code, of course, uses automation to Excel:

 Sub ChartInExcel()   ' Send the Weekly Timeslips results to Excel   ' for further analysis   Dim rstHoursByEmployee As ADODB.Recordset   Dim objXL As Excel.Application   Dim objWS As Excel.Worksheet   Dim objChart As Excel.Chart   Dim fld As ADODB.Field   Dim intCol As Integer   Dim intRow As Integer   Set rstHoursByEmployee = New ADODB.Recordset   ' Get the desired data into a recordset   rstHoursByEmployee.Open _    "HoursByEmployee", CurrentProject.Connection   ' Launch Excel   Set objXL = New Excel.Application   ' Create a new worksheet   objXL.Workbooks.Add   Set objWS = objXL.ActiveSheet   ' Copy the data   ' First the field names   For intCol = 0 To rstHoursByEmployee.Fields.Count - 1     Set fld = rstHoursByEmployee.Fields(intCol)     objWS.Cells(1, intCol + 1) = fld.Name   Next intCol   ' Now the actual data   intRow = 2   Do Until rstHoursByEmployee.EOF     For intCol = 0 To rstHoursByEmployee.Fields.Count - 1       objWS.Cells(intRow, intCol + 1) = _        rstHoursByEmployee.Fields(intCol).Value     Next intCol     rstHoursByEmployee.MoveNext     intRow = intRow + 1   Loop   ' Add a new chart   objXL.Charts.Add   Set objChart = objXL.ActiveChart   ' Set up the chart   objChart.ChartType = xl3DPieExploded   objChart.SetSourceData _    Source:=objWS.Range("A1:B" & CStr(intRow - 1)), _    PlotBy:=xlColumns   objChart.Location Where:=xlLocationAsNewSheet   objChart.HasTitle = True   objChart.ChartTitle.Characters.Text = "Hours By Employee"   ' Make the worksheet visible   objXL.Visible = True   ' Don't call the Excel Quit method, so the   ' worksheet will hang around after this   ' procedure exits End Sub 

Much of this code should look familiar from earlier in the chapter: it opens a Recordset using the HoursByEmployee query, and then moves the data over to Excel. Then creating the chart is an exercise in using the Excel object model. First, you call the Add method of the Charts collection to add a new chart. Excel keeps a pointer to the active chart, which you save in the objChart object to make things easier.

The Chart object has many methods and properties, but you need to set only a few of them to make an attractive chart. In this case, we've chosen a 3D exploded pie chart, with the data taken from the range of cells that we just wrote. The chart is saved as a new worksheet, with a specified title.

Figure 21.6 shows the results of running this code. It might not be the clearest chart in the world, but it's colorful enough to impress lots of people.

Figure 21.6. Excel chart from Access.