This post takes us to the next phase in the Integrating with Excel from Access series – the FormatReportGeneric() function.
This relatively simple function does the following:
- Selects the current used range in the active spreadsheet.
- Creates a table from the selected range and names it according to the optional TableName argument. Note that this pre-supposes that the data to format begins at cell A1. A few extra lines can be added to account for table data in a location other than A1, but I’m keeping it simple in this post as it works fine as-is for most applications.
- Formats the table to one of the built-in table styles. I use TableStyleMedium4. You can pick the style that best suits your needs, or add some code to make it an optional argument.
- Sorts the table based on the optional argument SortField
- Aligns some fields. There are some fields that appear over and over again in a given application that need to be centered or maybe right-justified. Obviously, you’ll want to include your own fields, perhaps even create an array of common fields and loop through them.
- Converts date fields. Along the same lines as alignment, date fields do not always translate well to Excel, especially when the data derives from a linked SQL Server database. Dates are in a text format and must be converted. NumberFormat = "mm/dd/yyyy" takes care of that.
- Converts numbers stored as text. Numbers are often stored as text (especially when working with SQL Server or Oracle data sources) and have to be converted. This is a two step process:
- Change the number format to "General"
- Rewrite the value into the cell. This second step actually completes the conversion to a number.
- Auto-fit columns to the appropriate minimum width to accommodate the entries
- Reduce some columns – This is also totally optional. Sometimes there are columns (like Description) that have very long entries. You may define those here and reduce them to a manageable 50 px width.
- Unwrap the text in the entire table. Again, this is optional. It makes the table look uniform, and since we have already set the autofit, nothing gets cut off except maybe the longer fields like Description.
That’s it! We’ve got a nicely formatted Excel table that used to be just bland data as it was pasted in from Access. Obviously, there is a LOT more we could do (like setting up headers and footers, etc.,) but this is a quick and dirty overview on how to get started.
Here is the code:
Sub FormatReportGeneric(objXL As Excel.Application, Optional SortField As String = "", Optional TableName As String = "Table1")
On Error GoTo errFormatReport
' Select the data range – we are pre-supposing that the active
' sheet only contains data that we want to convert to a table
' If you want to start from a specified range instead of
' getting the entire sheet, try the following code to select
' just the table data beginning with the active cell:
If .Selection.Rows.Count = 1048574 Then
' Be sure to trap for this error somewhere – it means
' there was nothing on the sheet to format.
Err.Raise vbObjectError + 1001
.ActiveSheet.ListObjects.Add(xlSrcRange, .Selection, , xlYes).Name _
.ActiveSheet.ListObjects(TableName).TableStyle = "TableStyleMedium4"
If SortField <> "" Then
SortFields.Add Key:=.range(TableName & "[[#All],[" & SortField & "]]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
' Format columns (centering, date/time)
' Some columns may exist, others not. It's generic…
On Error Resume Next
.range(TableName & "[Qty]").HorizontalAlignment = xlCenter
.range(TableName & "[Qty]").NumberFormat = "General"
.range(TableName & "[Qty]").Value = .range(TableName & "[Qty]").Value
.range(TableName & "[Quantity]").HorizontalAlignment = xlCenter
.range(TableName & "[Quantity]"). NumberFormat = "General"
.range(TableName & "[TimeStamp]").NumberFormat = "mm/dd/yyyy"
.range(TableName & "[ReceiptTime]").NumberFormat = " [$-F400]h:mm:ss AM/PM"
.range(TableName & "[Description]").ColumnWidth = 50
.Selection.WrapText = False
' Include your favorite error trapping routine.
' Common errors include 1004 (an undetermined Excel error) and
' 91 (a problem with the Excel object being properly set). Don't forget
' to include error trapping for the vbObjectError + 1001 error (no data).