One thing that any experienced Access developer will learn is that Automating Excel is an essential part of providing good reporting in Access. Although Access has great reporting tools for grouping/organizing data, it is horrible when it comes to charting when compared to Excel (there is simply no comparison). Throw into the mix that there are numerous cases where end-user would like to be able to perform filters … and you don’t want them playing around with the raw data, so exporting it to Excel makes everyone happy.
What one will also learn is that Access does provide a few techniques/tools to export tables/queries to Excel, but once again they are very primitive and lack some serious refinement.
So what can you do to Export data to Excel while enabling you, the developer, better control over the process and final output? It’s simple, automate the process yourself instead of relying on Access to do it for you using Excel Automation.
Below I will elaborate a basic framework of reusable functions that can simplify Excel automation and then I will give you a concrete example of its usage.
Typically, I create 2 standard modules: (i) Constants Declarations, (ii) framework of reusable functions, but nothing stops you from putting them all together in one module if you prefer.
The Constants Declaration
Obviously, you can add/remove constants as required by your specific needs, this is just an example of some of the common constants used.
Option Compare Database
Option Explicit
'Excel Constants used in various Excel functions, ...
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Const xlAutomatic = -4105
Public Const xlDiagonalDown = 5
Public Const xlDiagonalUp = 6
Public Const xlEdgeBottom = 9
Public Const xlEdgeLeft = 7
Public Const xlEdgeRight = 10
Public Const xlEdgeTop = 8
Public Const xlInsideHorizontal = 12
Public Const xlInsideVertical = 11
Public Const xlNone = -4142
Public Const xlContinuous = 1
Public Const xlDouble = -4119
Public Const xlExpression = 2
Public Const xlSolid = 1
Public Const xlThick = 4
Public Const xlThin = 2
Public Const xlUp = -4162
Public Const xlThemeColorDark1 = 1
Public Const xlThemeColorDark2 = 3
Public Const xlSortOnValues = 0
Public Const xlAscending = 1
Public Const xlSortNormal = 0
Public Const xlYes = 1
Public Const xlTopToBottom = 1
Public Const xlPinYin = 1
Public Const xlThemeFontMinor = 2
Public Const xlLandscape = 2
Public Const xlPortrait = 1
Public Const xlValues = -4163
Public Const xlPart = 2
Public Const xlByRows = 1
Public Const xlByColumns = 2
Public Const xlNext = 1
Public Const xlPrevious = 2
Public Const xlPie = 5
Public Const xlUnderlineStyleSingle = 2
Public Const xlUnderlineStyleNone = -4142
Public Const xlCenter = -4108
Public Const xlBottom = -4107
Public Const xlTop = -4160
Public Const xlContext = -5002
The basic framework of reusable functions
Option Compare Database
Option Explicit
Private Const sModName = "mod_MSExcel" 'For Error Handling
Public oExcel As Object 'Excel Application Object
Public oExcelWrkBk As Object 'Excel Workbook Object
Public oExcelWrSht As Object 'ExcelWorksheet Object
Public bExcelOpened As Boolean 'Was Excel already open or not
Public Sub LaunchExcel(Optional bVisible As Boolean = True)
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set oExcel = CreateObject("Excel.Application")
bExcelOpened = False
Else 'Excel was already running
bExcelOpened = True
End If
On Error GoTo Error_Handler
oExcel.Visible = bVisible
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: LaunchExcel" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Sub AddExcelWrkBk(Optional ByVal sTmplt As String = "")
'Create a new workbook based on a template file
On Error GoTo Error_Handler
If sTmplt = "" Then
Set oExcelWrkBk = oExcel.Workbooks.Add()
Else
'Technically should test for the existance of the file before trying to use it
Set oExcelWrkBk = oExcel.Workbooks.Add(sTmplt)
End If
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: AddExcelWrkBk" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Sub OpenExcelWrkBk(ByVal sWrkBk As String, Optional sPwd As Variant)
'Open an existing Excel Workbook
On Error GoTo Error_Handler
If IsMissing(sPwd) Then
Set oExcelWrkBk = oExcel.Workbooks.Open(sWrkBk)
Else
Set oExcelWrkBk = oExcel.Workbooks.Open(sWrkBk, , , , sPwd)
End If
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: OpenExcelWrkBk" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Sub CloseExcel(Optional bCleanupOnly As Boolean = True)
On Error GoTo Error_Handler
If bCleanupOnly = False And bExcelOpened = False Then
'oExcelWrSht.Close False
'oExcel.ActiveWorkbook.Close False
oExcel.Quit
End If
Error_Handler_Exit:
On Error Resume Next
Set oExcelWrSht = Nothing
Set oExcelWrkBk = Nothing
Set oExcel = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: CloseExcel" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Demo
Now let put it all together for a moment to see how it all works in one function to export a query. So let Rewrite my Export Records to Excel post using this reusable framework.
Function Export2Excel(ByVal sQuery As String)
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCols As Integer
Dim iRows As Integer
Call LaunchExcel 'Start Excel
oExcel.ScreenUpdating = False
oExcel.Visible = False 'Keep Excel hidden until we are done with our manipulation
Call AddExcelWrkBk 'Start a new workbook
Set oExcelWrSht = oExcelWrkBk.Sheets(1)
'Open our SQL Statement, Table, Query
Set db = CurrentDb
Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then
'Get a proper record count
.MoveLast
iRows = .RecordCount
.MoveFirst
'Build our Header
For iCols = 0 To rs.Fields.Count - 1
oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
'Do some basic formatting
With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
oExcelWrSht.Cells(1, rs.Fields.Count))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 1
.horizontalAlignment = xlCenter
End With
oExcelWrSht.Range("A2").CopyFromRecordset rs 'Copy the data from our query into Excel
oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
oExcelWrSht.Cells(iRows, rs.Fields.Count)).Columns.AutoFit 'Resize our Columns
oExcelWrSht.Range("A1").Select 'Return to the top of the page
Else
MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Excel spreadsheet with"
GoTo Error_Handler_Exit
End If
End With
'At this point you could save it and close it, or in this instance we're going to leave it open for our users to interact with it.
Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
oExcel.Visible = True 'Make excel visible to the user
oExcel.ScreenUpdating = True
Call CloseExcel
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Export2Excel" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
What else can be done
Pretty much anything.
Above is a simplistic example. I have reusable functions to apply borders the same way, I have reusable function to create charts, … this way with a single call I can automate complex tasks and simplify my overall code and provide me with just one location to edit should the need arise.
Very nice, will you be posting your code for chart generation?
No, not in the near future as it was developed specifically for a client and is thus proprietary to them. Given some time, I will try to put together a generic example.
That said, you can easily do it yourself by recording a macro in excel and transferring it into your own function.
I love it. Quick question is there a simple way to save the excel once it is created? Say I needed to create fours excels and save them when an action is performed. I call the function four times and create four worksheets but then I have to save them and close them.
Yes, you can. It depends on exactly what you want to do, but you could use the following at the end of the procedure to save and close the workbook after performing your automation.
Where sFileName is a String variable with a fully qualified path and filename for the Excel Workbook.
Thanks for the list of constants! I was just converting an app to late binding and this list was a godsend, I was using a bunch of constants. I think your list was only missing one.
Thank you for your very informative posts! Can you post reusable functions to apply borders to each cell in a range?