Sending MS Access Data to Excel

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

Sending MS Access Data to Excel - Part 3

In Part 1 of this series we saw how to open an Excel workbook and populate the cells therein.

(article continues after sponsor spot)

In Part 2 of this series we looked at changing font sizes, adding bold to the font, triggering auto column width, setting a custom column width, and doing a centre-merge operation on a title.

Here in part 3 we will draw lines around cells and shade the interior of the cells.

The following is some sample code. 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.

This first chunk of code calls a routine that draws single lines around some cells, in this case the range from A5 to G10. The routine will be listed further down the page.

I am depending on you having read parts 1 and 2, and thus I have left out some details, including references to recordsets and, as always, error checking code.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\excel_templates\template_A.xls")
Set objSht = objWkb.Worksheets("output")

'--- draw lines around section
Call gbasDrawLines(objXL, objSht, "A5:G10")

set objSht = nothing
set objWkb = nothing
set objXL = nothing

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.

Here is the code for actually drawing the lines. Note that it deals with all the possibilities, including diagonals. You can experiment with removing some of the code and allowing the defaults to take over.

Public Sub gbasDrawLines(objXL As Object, objSht As Object, strRange As String)

   objSht.Range(strRange).Borders(xlDiagonalDown).LineStyle = xlNone
   objSht.Range(strRange).Borders(xlDiagonalUp).LineStyle = xlNone
   With objSht.Range(strRange).Borders(xlEdgeLeft)
       .LineStyle = xlContinuous
       .Weight = xlThin
       .ColorIndex = xlAutomatic
   End With
   With objSht.Range(strRange).Borders(xlEdgeTop)
       .LineStyle = xlContinuous
       .Weight = xlThin
       .ColorIndex = xlAutomatic
   End With
   With objSht.Range(strRange).Borders(xlEdgeBottom)
       .LineStyle = xlContinuous
       .Weight = xlThin
       .ColorIndex = xlAutomatic
   End With
   With objSht.Range(strRange).Borders(xlEdgeRight)
       .LineStyle = xlContinuous
       .Weight = xlThin
       .ColorIndex = xlAutomatic
   End With
   With objSht.Range(strRange).Borders(xlInsideVertical)
       .LineStyle = xlContinuous
       .Weight = xlThin
       .ColorIndex = xlAutomatic
   End With
   With objSht.Range(strRange).Borders(xlInsideHorizontal)
       .LineStyle = xlContinuous
       .Weight = xlThin
       .ColorIndex = xlAutomatic
   End With

End Sub

xlContinuous makes solid lines
xlThin is obvious for a normal thin line
xlAutomatic controls the colour, which is by default black

The constants in brackets beside the term "Borders" controls which lines are done. This code draws lines in all possible horizontal and vertical places in the range.

Further Suggestion: How do you choose other colours?

Here is a trick you need to utilize. Go into Excel and record a macro. Create and change the colour of several lines. Stop the recording and see what VBA the macro created. This will provide you with info on how to use other colours.

Use the same technique to learn how to create different line styles.

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.

Oops Almost forgot to show you how to shade a cell.

objSht.Range("A5:G10").Select
'shade those cells with light green background
objXL.Selection.Interior.ColorIndex = 15

Make sure you read Part 4 of this series.

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.