Automating Excel using Access

Automating Excel using Access

Reasons to consider using Excel in your application

If you are designing an application that will be deployed on a system that will have the full Office suite of programs on, then you may wish to consider using Excel from your application. There are several reasons to do so:

Rather than having static reports, you can output to Excel, and the user can then alter them or re-use them as required;
I find the charting/graphing easier to control in Excel than in Access;
As Excel is designed for statistical analysis of data, it often makes more sense to use it in this role.

Some sample code

Here is some sample code to give you an idea of how to get started with running Excel from Access. This code is designed to be used in the Northwind database, as it uses a query from there. You will need to add a reference to the Microsoft Excel Object Library. It outputs the data from the query into a new Excel workbook, and then creates a graph showing the values.

Sub sExcelChart()
'   A sample procedure to show how to automate Excel 97 from Access 97 by outputting data from a query
'   in the Northwind sample database to an Excel spreadsheet, and then creating a Pie chart summarising the data.
    On Error GoTo E_Handle
    Dim objXL As New Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim objXLSheet As Excel.Worksheet
    Dim objXLChart As ChartObject
    Dim db As Database
    Dim rs As Recordset
    Dim strFile As String
    Dim intLoop As Integer
    Set objXLBook = objXL.Workbooks.Add
    Set objXLSheet = objXLBook.Worksheets("Sheet1")
    strFile = Left(CurrentDb.name, Len(CurrentDb.name) - Len(Dir(CurrentDb.name))) & "Test.xls"
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("Category Sales for 1995")
    intLoop = 1
    With objXLSheet
        .Columns("A:B").ColumnWidth = 14
        Do
            .Cells(intLoop, 1) = rs!CategoryName
            .Cells(intLoop, 2) = rs!CategorySales
            intLoop = intLoop + 1
            rs.MoveNext
        Loop Until rs.EOF
        Set objXLChart = .ChartObjects.Add(156, 0, 300, 300)
        objXLChart.Activate
        With objXLChart.Chart
            .ChartType = xl3DPieExploded
            .ChartArea.Border.LineStyle = xlNone
            .SeriesCollection.Add Source:=objXLBook.Sheets("Sheet1").Range("A1:B" & intLoop - 1)
            .HasTitle = True
            .ChartTitle.Text = "Category Sales for 1995"
            .Legend.Position = xlLegendPositionBottom
        End With
        .Cells(1, 1).Activate
    End With
    If Len(Dir(strFile)) > 0 Then Kill strFile
    objXLBook.SaveAs strFile
sExit:
    On Error Resume Next
    Set objXLChart = Nothing
    Set objXLSheet = Nothing
    Set objXLBook = Nothing
    objXL.Quit
    Set objXL = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Tips when automating Excel

Here are some tips and hints that I have found to be useful when automating Excel:

To get an idea of the code that you will need to use, record a macro in Excel, perform the actions manually, and then view the code that has been produced.
If you are not able to guarantee the exact version of Excel installed on the system, you will need to use late binding. In this case, use early binding when writing your code, so that you can take advantage of Intelli-Sense, and then convert back to late binding, replace the Excel constants with your own similarly named constants. For example, I would replace the Excel constant xl3DPieExploded with one called XL_3DPIE_EXPLODED that has a value of 70.
One of the things that I have found quite difficult is the fact that the order of operations is very important in Excel, and there is limited documentation about this. For example, in the the sample code above, if you try to set the chart's title before the data is added, an error occurs.

Top

Last modified at 06/06/2006 14:56:56