'Serving the Microsoft® Access
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.
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.
xlContinuous makes solid lines
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.
Make sure you read Part 4 of this series.
Entire series ...