Easy Excel Charting from Access

One of the strengths of the Microsoft Office suite is the ability for its component parts to communicate between themselves. It is particularly useful to communicate between Access and Excel because while Access is superior at storing data, Excel is superior at manipulating it. For example, I am often asked if it's possible to send data from Access to formatted cells in Excel and create a chart based on it.

This problem can be solved by extensive use of Office Automation, but many people find this prospect daunting. Office Automation through VBA (Visual Basic for Applications) is an extremely powerful but complicated method. I discussed this method in my post: How do I export Access data to Excel - Part 3.

But there are other methods, like the Access TransferSpreadsheet method, that are easier to use, but far more limited. How do I export Access data to Excel - Part 2

A Middle Ground

However, it's also possible to solve with a combination of built-in features of both Access and Excel, that is, Excel templates, a tiny bit of Office Automation, and the Access TransferSpreadsheet method. This middle ground uses the strengths of both, and is both easy and flexible.

The TransferSpreadsheet method allows me to export a query or table from Access to Excel. If the workbook does not exist, it will create one. If the workbook does exist, it will create a new sheet in the workbook named after the table or query. But if both the workbook and sheet already exist, Access will over-write the data in the sheet. This is the critical feature.

Another feature I'll make use of is Excel's ability to link cells from one sheet to another. This means I can link a chart on one worksheet to another worksheet that holds the data. If I use the TransferSpreadsheet method to export a query that overwrites the data in the data worksheet, my chart will be updated automatically.

Lastly, I will use an Excel template to create a new Excel workbook with pre-formatted cells and charts. An Excel template is a special kind of workbook with a .xltx extension. When you open a template, it automatically creates a new workbook with a .xlsx extension, leaving the template untouched.

These features, used in combination with a small amount of Office Automation, give me all the tools I need to accomplish the task.

Overview

The overall process goes something like this:

1. Create an empty Excel template (.xltx)
2. Create the Export code to do the following:
   1. Open the Excel template
   2. Save the resultant workbook
   3. Export my data to the workbook with the TransferSpreadsheet method.
3. Run the export program to create a new workbook with the exported data.
4. Create a new worksheet in this workbook with whatever data formatting and charts I need, linked to the data worksheet.
5. Save this workbook as template, over-writing the previous.
6. Clear the data from the exported worksheet.

Creating the Template

First thing to do is create an Excel template.

You might think I could simply create a data worksheet manually and name the tab after my exported query. Unfortunately, it's not that easy. That's because the TransferSpreadsheet method looks for a named range to export to, not a worksheet name. So if I create a worksheet named after my query, say Drug1, when I export the query, it will create a new worksheet called Drug1(1) instead of exporting to the worksheet I want.

The easiest way around this is to use the Access TransferSpreadsheet to export my query to a blank workbook. This will create the data worksheet automatically for me with the proper named range. Then I'll re-save it as a template so the export routine will find the correct worksheet for the next time.

So to start, I create a blank workbook and save it as a template. To do that, choose File > SaveAs and in the file type box choose Template(*.xltx). Now, Excel will try to save this in the default templates folder. I prefer to store this template with the database, so I then browse to the folder where the database exists and save it there.

Setting a Reference to Excel

Next, I switch to Access to create the export routine. But before I do that, I need to set a reference to Excel in Access. A reference is a call to an external code library, in this case the Excel Object Model. This will allow me to manipulate Excel objects from within Access.

To set a reference, I open the Visual Basic Editor. Then I go to Tools > References. In the dialog box, scroll down to Microsoft Excel 15.0 Object Library (your version number may vary). Click the checkbox next to it and click OK. Figure 1 shows what the References Dialog looks like.

 
Figure 1: The References Dialog showing the Excel Object Library reference.

Export Program

Next, I'll create the Access subroutine (called ExportSpreadsheet), which exports the data to Excel. I start the routine with a call to an Error Handler. I'll explain why later.

Sub ExportSpreadsheet()
On Error GoTo HandleError

Next, I'll declare some variables.

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim strFile As String
Dim strPath As String

I also need to find the folder where the database resides. This is where the template is stored and also where I'll save the completed workbook. Naturally, if I wanted the files stored elsewhere (say a specific directory), I could code that here, too.

strFile = CurrentProject.Name
strPath = CurrentProject.Path

Now, I want to delete the existing workbook if it exists. I do this keep the SaveAs dialog box from asking me if I want to over-write the existing file. This is most useful if I am creating multiple workbooks.

Kill strPath & "\MySpreadsheet.xlsx"

Next, I need to create a workbook from the template. To do that, I have to use a tiny bit of Office Automation. I have to open an Excel Application object and an Excel Workbook object.

Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(strPath & _
       "\MyTemplate2010.xltx")

Next, I save the workbook object as a workbook and close it.

objXLBook.SaveAs (strPath & "\MySpreadsheet.xlsx")
objXLBook.Close

Which leaves a file called "MySpreadsheet.xlsx" in the same directory as my Access database. Next I use the TransferSpreadsheet method to export two queries to the workbook. Because I am specifying the same workbook, each query will create a separate worksheet in the workbook.

DoCmd.TransferSpreadsheet acExport, , "qryDrug1", strPath & _
      "\MySpreadsheet.xlsx", True
DoCmd.TransferSpreadsheet acExport, , "qryDrug3", strPath & _
      "\MySpreadsheet.xlsx", True

Sometimes, the chart data is not refreshed in the resulting workbook, so I'll open the workbook and save it again. This takes care of the refresh problem.

Set objXLBook = objXLApp.Workbooks.Open(strPath & _
     "\MySpreadsheet.xlsx")
objXLBook.Save
objXLBook.Close

Then I add a message box that identifies when the process is done.

MsgBox "Done!" & vbCrLf & vbCrLf & _
     "Look in the directory" & vbCrLf & vbCrLf & _
     "where the application resides for ""MySpreadsheet.xlsx"""

Lastly, I complete the error trapping. After the ProcDone label, I'll destroy the object variables I've created. I do that here so if there is an error and the routine terminates, an Excel object won't be left in memory.

ProcDone:
Set objXLBook = Nothing
Set objXLApp = Nothing
ExitHere:
Exit Sub

There are two main errors that must be handled. Error 1004 occurs if the Template does not exist. In that case, I just want the routine to end without doing anything else. The other error, 53, happens when the MySpreadsheet.xlsx file (that I'm trying to delete with the KILL command) does not exist. In that case, I just want the routine to continue.

HandleError:
Select Case Err.Number
Case 1004 'a template does not exist
     MsgBox "There is no template for this chart."
     Resume ProcDone
Case 53 'Excel file cannot be found to delete
     Resume Next
Case Else
     MsgBox Err.Description, vbExclamation, _
          "Error " & Err.Number
     Resume ProcDone
End Select
End Sub

In this example, I am going to export two queries (Drug1 and Drug3) to my spreadsheet and create two separate charts based on them. Because of that, I'm going to hard code the query names into the routine. But to utilize the real power of this process, you could store a list of queries to be exported in a table, then create a loop that would march through the table, exporting each query in turn. In this way, you could create literally hundreds of charts in just a few minutes. If your process requires dozens or hundreds of charts created every month, this could be quite handy.

Running the Code the first time

So far, all I've got is an empty template and my Export routine. The next step is to run the Export code for the first time. When that happens, my previously empty spreadsheet has two worksheets: qryDrug1 and qryDrug3.

Figure 2 shows the resulting workbook.

Figure 2: Workbook created by the first run of the ExportSpreadsheet subroutine.

Create worksheet with formatted data and chart.

Now I have to manually create two new worksheets, which I'll name Drug1 and Drug3. These will hold my formatted data and charts. (Since the process is the same for both charts, I'll just concentrate on Drug1, but you should realize that you can create as many charts as you want up to the 255 sheet limit of Excel.)

So next, I need to link the cells containing the data in sheet qryDrug1 into my new Drug1 sheet. To do that, I open the Drug1 sheet and select cell A1, hit the equal key (=), click on the qryDrug1 tab to go to that sheet, click cell A1 in that sheet, and finally click the green check mark on the Formula bar. The resulting formula looks like this: =qryDrug1!A1. Next, select cell A1 and click the Copy button, select the range A1:C13 and click Paste. Figure 3 shows the resulting worksheet.

Figure 3: Worksheet of cells linked to the data on qryDrug1.

Now I can format this data. For simplicity, I'll just apply an auto format, then I'll format the cells in column B as Percent with no decimal places. Figure 4 shows the results of the formatting.

But I'm not done with this sheet. I also want to create a chart on this data. So I'll use the Chart Wizard to create a bar chart comparing the drug prescription rate for each physician. Figure 4 also shows the resulting chart.

Figure 4: Formatted data and chart based on the data linked on the worksheet.

Notice that while the chart is based on the formatted data, the actual data resides on the qryDrug1 worksheet. This is important because the Drug1 information in the qryDrug1 worksheet is not formatted as percent. If I based the chart on the qryDrug1 worksheet, the Y axis of my chart would not automatically be formatted as percent either. By linking the data and formatting it, I can control the format of the chart.

Repeat these steps for the Drug3 worksheet.

Save Workbook as Template

I'm almost done now. All that's left is to save my finished spreadsheet as a template, overwriting my existing MyTemplate.xltx template. Again, Excel will try to save the template in the default Templates folder, so I have to browse to the application folder to save it over the existing template.

Lastly, I need to delete the data in the data worksheets: qryDrug1 and qryDrug3. It's very important to just delete the data and NOT the worksheets themselves. To do so will put #REF in each linked cell and all the linking will be lost. But just clearing the data from the exported data worksheet will leave the links intact. Figure 5 shows the cleared worksheet.

Figure 5: Formatted data and chart with data deleted from the qryDrug1 worksheet.

Re-Run the Program

That's all there is too it. Running the program again will open the new template, save it as a workbook, export my queries to the existing worksheets, which will display in the linked cells and chart. Figure 6 shows the final result.

Figure 6: Results of running the export to the completed template.

Conclusion

Microsoft Access has some powerful tools for communicating with Excel. Some of these tools, like the Transfer Spreadsheet method, are very easy to use but limited. Others, like Office Automation, are flexible but complicated. But as I have shown, by using the strengths of both products, you can accomplish this in a way that that is both easy and flexible.

To download a working sample database, follow this link: ExportToExcelCharts.mdb (intermediate)