Okay, you also wanted to Format, well here is another module where I format each
spreadsheet once it's exported into Excel. Once again you will want to copy and
paste this into an Access module for easier reading. Note that the "oExcel"
object is declared publicly in my last bit of code I used to show you the export
feature.

Option Compare Database
Option Explicit
Sub ExcelFormat(NumRows As Integer)
'* Gives each department report  a better look and feel
Dim Borders As Variant 'Array
Dim LastColumn As String 'Specific Ranges
Dim i As Integer 'Counter
LastColumn = "D" & NumRows + 1
Borders = Array(xlEdgeBottom, xlInsideHorizontal)
With oExcel
.Range("E:E").Select 'We don't need this, it's the dept name next to each
emp.
.Selection.Delete SHIFT:=xlToLeft
.Range("C:C").Select
.Selection.HorizontalAlignment = xlLeft
.Range("A1:D1").Select
.Selection.Font.Bold = True
.Selection.HorizontalAlignment = xlCenter
With .Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
.Cells.Select
.Cells.EntireColumn.AutoFit
.ActiveWindow.DisplayGridlines = False
.Range("A1", LastColumn).Select
For i = 0 To 1
With .Selection.Borders(Borders(i))
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next
.Range("A1").Select
End With
End Sub
Sub FormatSummarySheet(bNextColumn As Boolean, iEmpTotal As Integer)
'* Formats the summary sheet, first by adding headers, and formatting them.
With oExcel
.Range("B1").Value = "Departments"
.Range("C1").Value = "Emp #"
.Range("B1:C1").Select
If bNextColumn = True Then
.Range("E1").Value = "Departments"
.Range("F1").Value = "Emp #"
.Range("B1,C1,E1,F1").Select
End If
.Selection.Font.Bold = True
With .Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With .Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Cells.Select
.Cells.EntireColumn.AutoFit
.ActiveWindow.DisplayGridlines = False
.Range("A1").Select
'Some final touches to give the report a title, and a better look
.Rows("1:3").Select
.Selection.Insert SHIFT:=xlDown
.Range("A1:F1").Select
With .Selection
.HorizontalAlignment = xlCenter
.MergeCells = True
End With
.ActiveCell.Value = "Bridgewater Employee Report"
With .Selection.Font
.Name = "Arial"
.Size = 14
.ColorIndex = 11
End With
.Range("A2:C2").Select
With .Selection
.MergeCells = True
.HorizontalAlignment = xlRight
End With
.ActiveCell.Value = "Total Employees"
.Range("D2:E2").Select
With .Selection
.MergeCells = True
.HorizontalAlignment = xlLeft
End With
.ActiveCell.Value = iEmpTotal
.Columns("H:H").Select
.Range(.Selection, .Selection.End(xlToRight)).Select
With .Selection.Interior
.ColorIndex = 1
End With
.Range("A1").Select
End With

End Sub