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 2

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

(article continues after sponsor spot)



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.

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

' --- 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.

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.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy