Chapter 13: Working with Excel

Overview

Excel is the Office component that is used to analyze data, particularly numeric data. In early versions of Office, you had to use Excel to produce attractive charts or pivot tables, but Access has had a charting component since v. 1.0 (MS Graph) and PivotTables since Access 2000, and PivotCharts were introduced in Access 2002 (Office XP). Because of these enhancements to Access, you no longer need to export data to Excel in order to produce elegant, interactive PivotTables and (if you have Office XP or higher) PivotCharts (see Chapter 5, Using PivotTables and PivotCharts to Interact with Data, for information on PivotTables and PivotCharts). However, there are still circumstances in which you will need to export Access data to an Excel worksheet or chart, primarily when you need to give the data to someone who doesn’t have Access and needs to work interactively with your data.

If you just need to send somebody a read-only image of attractively formatted Access data, you can prepare a report (possibly including a PivotTable or PivotChart image), export it to either the Access Snapshot or Adobe PDF format, and email it as an attachment, as described in the “Email a Report Form” section of Chapter 12, Working with Outlook.

This chapter explains how to export Access data to Excel worksheets, using the OutputTo and TransferSpreadsheet methods and Automation code, and how to import data from Excel worksheets into Access tables. The following table lists the sample files referenced in this chapter, and where they should be placed.

Document Name	Document Type	Place in Folder
Excel Data Exchange.mdb	Access 2000 database	Anywhere you want
Categories.xls	Excel worksheet	\My Documents\Access Merge
Customers.xls	Excel worksheet	\My Documents\Access Merge

The main menu of the Excel Data Exchange database was created with my Menu Manager add-in (see Chapter 6, Printing Data with Reports, for more information on this add-in.) In addition to the standard main menu sections, the main menu also has a Docs Path textbox to allow editing the Documents path (the path is picked up from tblInfo in various procedures in the database) The main menu is shown in Figure 13.1.

Figure 13.1

The first of the following two functions checks whether the path entered into the DocsPath textbox on the main menu is valid, and if so, the second function retrieves the path from tblInfo, defaulting to C:\My Documents if the field is blank. It also checks whether there is an Access Merge subfolder under the Documents folder, and creates one if needed.

  Public Function CheckDocsDir() As Boolean On Error GoTo ErrorHandler        Set dbs = CurrentDb    Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset)        With rst       .MoveFirst       strFolderPath = Nz(![DocsPath])       If strFolderPath = "" Then          strFolderPath = "C:\My Documents\"       End If    End With           ‘Test the validity of the folder path    Debug.Print "Folder path: " & strFolderPath        If strFolderPath = "" Then       strTitle = "No path entered"       strPrompt = "Please enter a Docs folder path on the main menu"       MsgBox strPrompt, vbOKOnly + vbCritical, strTitle       CheckDocsDir = False       GoTo ErrorHandlerExit    Else       Set fso = CreateObject("Scripting.FileSystemObject")       If fso.FolderExists(strFolderPath) = False Then          strTitle = "Folder path invalid"          strPrompt = "Please enter a valid Docs folder path on the main menu"          MsgBox strPrompt, vbOKOnly + vbCritical, strTitle          GoTo ErrorHandlerExit          CheckDocsDir = False       End If    End If        CheckDocsDir = True     ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Function Public Function GetDocsDir() As String On Error GoTo ErrorHandler        Dim strFolderPath As String        Set dbs = CurrentDb    Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset)        With rst       .MoveFirst       strFolderPath = Nz(![DocsPath])       If strFolderPath = "" Then          strFolderPath = "C:\My Documents\"       End If    End With           ‘Test the validity of the folder path    Debug.Print "Folder path: " & strFolderPath        If strFolderPath = "" Then       strTitle = "No path entered"       strPrompt = "Please enter a Docs folder path on the main menu"       MsgBox strPrompt, vbOKOnly + vbCritical, strTitle       GoTo ErrorHandlerExit    Else       Set fso = CreateObject("Scripting.FileSystemObject")       If fso.FolderExists(strFolderPath) = False Then          strTitle = "Folder path invalid"          strPrompt = "Please enter a valid Docs folder path on the main menu"          MsgBox strPrompt, vbOKOnly + vbCritical, strTitle          GoTo ErrorHandlerExit       End If    End If        strDocsDir = strFolderPath & "Access Merge\"    Debug.Print "Access Merge subfolder: " & strDocsDir        ‘Test for existence of Access Merge subfolder, and create    ‘it if it is not found    Set fso = CreateObject("Scripting.FileSystemObject")    If Not fso.FolderExists(strDocsDir) Then       ‘Access Merge subfolder does not exist; create it       fso.CreateFolder strDocsDir    End If        GetDocsDir = strDocsDir     ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Function