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

Sending Data to Excel - Part 2 (024)

In part 1 of this article we saw how to open an Excel workbook and populate the cells therein. Part 2 will look 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.

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.

'-----------------------------------------
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")

' --- create column headers
' --- start in row 3, column 1
objSht.Cells(3, 1).Value = "Sale Date"
objSht.Cells(3, 2).Value = "ID Num"
objSht.Cells(3, 3).Value = "Sales Person"
objSht.Cells(3, 4).Value = "Sale Amount"
objSht.Cells(3, 5).Value = "Accumulation"

' --- change the fonts
objSht.Range("A3:E3").Font.Bold = True
objSht.Range("A3:E3").Font.Name = "Arial"
objSht.Range("A3:E3").Font.Size = 10

' --- autofit the columns, then make the Name one wider
objSht.Columns("A:E").EntireColumn.AutoFit
objSht.Columns("C:C").ColumnWidth = 30

' --- create the title
' --- created after the autofit, or else ...
objSht.Cells(1, 1).Value = "Weekly Sales for Week Ending " & Me.strWeekEnding

' --- font for the title
objSht.Range("A1:A1").Font.Bold = True
objSht.Range("A1:A1").Font.Name = "Arial"
objSht.Range("A1:A1").Font.Size = 14

' --- centre the title across the "page"
objSht.Range("A1:E1").HorizontalAlignment = xlCenter
objSht.Range("A1:E1").Merge

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 3 will shade the cells and put borders around them. And I'll see if I have any other Excel tricks in my projects.

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.