Sending MS Access Data to Excel

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

Sending MS Access Data to Excel - Part 1

Access and Excel work very well together. Sending Access Data to Excel is quite easy to do, from a simple export to a more complex process requiring VBA. I have written a number of articles to show you methods that I use.

(article continues after sponsor spot)

My use of these VBA techniques all started when 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.

An Access report viewer is available (see this article), however, this option was quickly dismissed. Since almost every employee had 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".

A template was created, with the page designed to fit nicely on paper output, and with appropriate Titles and shaded column headers already designed. Columns intended for numbers were also formatted so that the correct numeric format was used.

The following is some sample code from that project. It is designed to open and populate the Excel template 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.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Also, 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. See the above mentioned article on DAO for more details on the References menu.

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 had 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

set objSht = nothing
set objWkb = nothing
set objXL = nothing
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 series on Sending Access Data to Excel will examine how to shade, draw borders around data, and do things like auto column width sizing.

Further Suggestions:

To make sure that you find out whenever we are adding more tips, subscribe to our newsletter. Just click the Subscribe button to the left to start the process.

Entire series ...

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.