15.3. Sending Information from Access to Excel

Within your Access database, you can create detailed reports including graphs and tables. However, it's impossible to design enough reports to please all of your users, all the time. Sometimes it's better to simply allow your users to export some of the data to Excel where they can manipulate the data in a variety of different ways. In addition to exporting data from Access to Excel, you can even create charts in Excel directly from Access.

First, add a reference to the Excel 11 object model from the References dialog box. Now you can manipulate not only the Excel application, but worksheets, cells, and graphs.

15.3.1. Provide Management with Flexible Data Access

Within any company, you typically have a variety of managers. Some are quite technically savvy while others are not. Some love to fiddle and massage data, creating their own reports and graphs. By allowing management to export a variety of queries directly to Excel, you don't have to worry about designing a new report every time they want to view the data in a slightly different manner. Our first example utilizes a form (shown in Figure 15-5) with a list box that a manager can use to choose the query he or she wants to export.

When you create your form and list box, make sure to set the Row Source for the list box to Value List. The default Row Source value of a list box is Table/Query. You can fill your list box in two different ways. If you want the managers to be able to choose any report to export, consider using the following code to populate the list box. This code loops through each query in your database and adds its name to the list box.

Figure 15.4. Figure 15-4

Private Sub Form_Open(Cancel As Integer)
Dim strQryName As String
Dim itmQuery As QueryDef
For Each itmQuery In Application.CurrentDb.QueryDefs
    strQryName = itmQuery.Name
    Me.lstQuery.AddItem strQryName
Next
End Sub

If you don't want management to be able to choose from every report in your database, you can always hard-code the query names into the data source for your list box. Now that you have a list of queries in your list box, you can write code to export the results of one of those queries to Excel. You can perform the export in several ways. We'll cover two of those methods here. The first method we'll cover involves opening Excel, creating a new workbook with a new worksheet, and transferring the data into the worksheet. The second method utilizes the Save As method of the RunCommand method to automate this process. After we've walked through both of these steps we'll cover why you might want to use each of those steps.

Figure 15.5. Figure 15-5

15.3.1.1. Opening Excel and Creating a New Worksheet with Code

We 'll build the code in a few steps. First, we need to create a reference to an Excel application and create a new worksheet, as shown in the following code.

Private Sub cmdExport_Click()
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set xlWorkbook = Nothing
Set xlApp = Nothing
End Sub

Once you verify that Excel opens with a new worksheet, it's time to fill that worksheet with data. There are several ways of accomplishing this task. You can use a recordset object (from DAO) and set that recordset to the result set from your query. Once you have the recordset object, you can use Excel's CopyFromRecordset method of the Cells object to send the results to your spreadsheet. This method is accomplished by the following code.

Private Sub cmdExport_Click()
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSheetName as String
strQueryName = Me.lstQuery
strSheetName = Left(strQueryName, 31)
strSheetName = Trim(strSheetName)
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)

Set xlSheet = xlWorkbook.Sheets(1)
With xlSheet
    .Cells.CopyFromRecordset objRST
    .Name = strSheetName
End With
Set objRST = Nothing
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
End Sub

The preceding code creates a fairly plain Excel spreadsheet, as shown in Figure 15-6, and displays it on the screen. However, let's add a little pizzazz to our spreadsheet. We can add just a few lines of code and add column headings, shade those column headings, and save the spreadsheet with a filename and location the user specifies.

We 're using a DAO recordset object so we can use the properties and methods of this object within our code. To add column headings, we'll need to loop through the Fields collection of the recordset and add a heading for each field. You can accomplish this with the following lines of code.

For lvlColumn = 0 To objRST.Fields.Count - 1
   xlSheet.Cells(1, lvlColumn + 1).Value = _
   objRST.Fields(lvlColumn).Name
Next

Figure 15.6. Figure 15-6

This code loops through every column in the worksheet and places the appropriate field name within that column. But, just placing field names in the appropriate column isn't very exciting. Why not add some color? Column headings are typically colored gray. For some added pizzazz let's also add a cell border and a bolded font. You can accomplish these tasks with the following code (don't worry, we'll put all the code together at the end of the section):

'Change the font to bold for the header row
xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
'Add a border to header row cells
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
    .LineStyle = xlContinuous

.Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

Now that's a lot of code. But it's pretty simple code. All you're doing is setting each border (top, bottom, left, and right) to a thin line. Now, you're ready to return to the code to fill the sheet with data. You'll need to make one simple alteration to the previously listed code. If you add the code to fill and format the column headings and then try to execute the previously listed code as is, you'll end up with no header row and the first row of data formatted with bold font and borders. In order to start the actual data in the second row of the spreadsheet, change the code

With xlSheet
    .Cells.CopyFromRecordset objRST
    .Name = strSheetName
End With

to the following code:

With xlSheet
    .Range("A2").CopyFromRecordset objRST
    .Name = strSheetName
End With

That's a pretty simple change. Your worksheet should now look like the one shown in Figure 15-7.

In order to create the spreadsheet as shown in Figure 15-7, you can use the following procedure.

Private Sub cmdExport_Click()
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
strQueryName = Me.lstQuery
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)

Figure 15.7. Figure 15-7

Set xlSheet = xlWorkbook.Sheets(1)
    For lvlColumn = 0 To objRST.Fields.Count - 1
      xlSheet.Cells(1, lvlColumn + 1).Value = _
      objRST.Fields(lvlColumn).Name
    Next
    'Change the font to bold for the header row
    xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
    'Add a border to header row cells
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin

.ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
With xlSheet
    .Range("A2").CopyFromRecordset objRST
    .Name = Left(strQueryName, 31)
End With
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
End Sub

If you prefer not to use the CopyFromRecordset method, you can use a slightly older method, the TransferSpreadsheet method, from the DoCmd object. You might remember that the DoCmd object is more of a legacy object left over from previous versions of Access and it's now been replaced by the Application.RunCommand method. Well, unfortunately there are still a few instances where you need the DoCmd object as opposed to the Application.RunCommand method. There are a few distinct advantages to the TransferSpreadsheet method. First of all, you can export an entire table to a spreadsheet with one line of code. For example,

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Samples",
"c: samples.xls"

The previous line of code is all you need to export the Samples table to a spreadsheet called Samples.xls on the C drive. This method allows you to export either tables or queries stored in your database. The next advantage to this method is that you don't actually invoke the Excel object model. Why is this an advantage? Well, it's not so much an advantage as it is simpler.

There are a couple of disadvantages to this method, however. First of all, if you already have a file called Samples.xls stored in the location you've specified, this code will fail without error. The code runs, but the existing spreadsheet isn't replaced by the new spreadsheet. So you could wind up with outdated spreadsheets. You could work around this error by checking for the existence of a file of that name before this line of code runs. You can do this with the following code.

Dim intFileLength As Integer
Dim strFilePath As String
strFilePath = "C:samples.xls"
intFileLength = Len(Dir$(strFilePath))
If Err Or intFileLength = 0 Then
'Run Transfer Spreadsheet code - file does not exist
Else
'Do something else, file already exists
End If

Here's the entire procedure as we've used it in the past with a CommonDialog control from the Windows Common Controls. This control isn't available in the default installation of Microsoft Access 2003, but it's installed when you install Visual Basic 6.0 or Visual Studio .NET. If you have access to this control, you can use it to prompt your users to select a folder and file to open. Prompt the user to choose a filename and location. Because you can never trust users to always follow instructions, check to make sure the filename doesn't exist in the location they've chosen. If the file already exists, prompt them to enter another filename. If the file doesn't yet exist, run the TransferSpreadsheet method.

Private Sub cmdTransferSpreadsheet_Click()
Dim intFileLength As Integer
Dim strFilePath As String
Dim strFileName As String
Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName

intFileLength = Len(Dir$(strFilePath))
While Not (Err Or intFileLength = 0)
MsgBox "You entered a file that already exists. Please choose another file
name or location.", _
vbOKOnly, "Duplicate File"
Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName
intFileLength = Len(Dir$(strFilePath))
Wend
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Samples",
strFilePath
End Sub

The second disadvantage is that you can't control the look and feel of the spreadsheet. Your spreadsheet will look similar to Figure 15-8. You can't manipulate the column headings, fonts, or shading.

You 'll need to decide whether your project requires the formatting and flexibility of the first method or the ease of use of the second method. Both work equally well for their basic task, transferring data between Access and Excel.