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.