the Manx Man's MS Access Pages
to subscribe - click here

Sending Data to Excel - Part 1 (023)

A recent project for a client required a variety of "reports" that could be easily sent to a wide variety of people. Since most of the employees did not have MS Access on their computers, we had to find an easy way of getting the data to them.

A report viewer is available, however, this option was quickly dismissed. Since almost every employee has MS Excel on their computers, I suggested that we get the Access program to create output in Excel format. The Excel workbook could then be e-mailed to the appropriate people and they could view and/or print the "reports".

The following is some sample code to open and populate an Excel workbook. As usual, I am not suggesting that I have discovered the best code, but it works. Let me know if you have found a "slicker" way to do this.

'-----------------------------------------
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As Recordset
Dim iRow As Integer

'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\excel_templates\template_A.xls")
'--- I always name my worksheets something specific, and this next line refers
'--- to a specific worksheet  (Excel names the first one "sheet 1" by default)
Set objSht = objWkb.Worksheets("output")

Note:  I use a preset workbook so that headers and footers are already configured to the project's standards, and in some cases, the headers for columns are already configured, along with column widths, etc.  You could just as easily use a template that contains a plain, default worksheet.

' --- since this particular worksheet has various built in headers and column titles,
' --- the actual data starts at row 10
iRow = 10

Set rst = CurrentDb.OpenRecordset("SELECT * FROM myTable " & _
     "WHERE ((somefield)= 'somevalue') " & _
     "order by field1, field2;")
rst.MoveFirst

Do While Not rst.EOF
   objSht.Cells(iRow, 1).Value = rst!field1
   objSht.Cells(iRow, 2).Value = rst!field2
   objSht.Cells(iRow, 3).Value = rst!field3
   iRow = iRow + 1
   rst.MoveNext
Loop

rst.Close
objSht.Range("A1").Select

------------------------

When the program reaches the end of the above code, you will be looking at the finished spreadsheet on your monitor. You can now make any minor modifications to it before printing it, saving it to disk, and then e-mailing it as an attachment.

Part 2 of this article will examine how to shade, draw borders around data, and do things like auto column width sizing.

NOTE: This code will only work if you have Excel references turned on. The References section is found under the Tools menu in the VBA editor.

p.s.  The above code was based on VBA from an Access 2000 project that uses DAO references. The code was modified for use in this article and was not tested in Access. For example, I removed all error checking portions of the code. This code should also work in Access 97, however, the author makes no warranties of such. If you have trouble with the code, e-mail the Manxman (see link below) with your questions.