Easily transfer data from Access to Excel – the Template Designer

If you need to create special formatted Excel files from data in your database on a regular basis you can easily automate the process using 4TOPS Document Creation using Microsoft Access. It includes an Excel Template Designer add-in which allows you to specify where the data to be transferred from Access to Excel has to go using drag and drop.

Access to Excel template designer

The Excel Template Designer therefore is key to setting up the Excel spreadsheet creation process. It communicates back to the document creation process wizard what elements of the data source are actually used, allowing the wizard to generate VBA automation code that is accurate, adaptable and fast. (Note: you can use 4TOPS Document Creation without having to see or get involved with the underlaying VBA code.)

Here we describe the specific features of the Template Designer for Excel. To see how to prepare your data source to make the necessary data available and start the template designers in general visit Template Designer add-ins for Word, Excel and PowerPoint.

Excel spreadsheet creation: VBA Code transferring data

When actually used, a document creation process picks up the display values from the relevant controls on the MS Access form or datasheet (data source). These values are then inserted in locations marked by a corresponding bookmark. In case the same source element is placed in multiple positions in the file we make use of a formula expression, e.g. =CompanyName, to refer to the first instance.

Relating Access to Excel using range names

The Excel range names must take into account certain naming restrictions. This is taken care of by the process wizard which provides the list of controls and their corresponding bookmark names to the Template Designer (see top image) to display in the pane on the right.

Excel name manager named ranges
You can also see the range names in Excel’s name manager tool.

Values and formatted data

When you run a document creation process the data from a form control is transferred into the new document created from the template you designed. The most basic kind of data to be is a simple value obtained from a control. However, in practice things are more complicated as in Access the developer has significant means design the application to get the user interaction with data that suit the purposes best:

  • Access forms have multiple ways to present data such as multi-column list- or combo- boxes or getting the selected value in a group box.
  • In some cases there we need to distinguish between display and bound value, e.g. in CompanyID the value could be either an ID number or the name of the company, the latter being usually more informative.
  • The display value may further be affected by a formatting expression
Having a Lookup specified causes a distinction between bound and display value

Function ControlValue

Fortunately, getting the value to be used in the Excel file is much simplified by ControlValue function included in the AccessControl module inserted by the process wizard’s VBA code generator. Function ControlValue has the following interface


Public Function ControlValue(Control As Object, Optional UseDisplayValue As Boolean = True, 
                        Optional Format As String = "") As Variant

This function is used with each simple data element transfer included by the wizard – on instigation of the Template Designer – in the document creation process code on e.g.


Dim efr As New ExcelFiller: Set efr.Workbook = wb
efr.FillElement Bookmark:="CustomerID", Value:=ControlValue(Control:=![CustomerID])

Bound or Display value

In the above example this would put the Company Name in the document, this having been identified as the meaningful (Display) value to identify the company using the Lookup properties.

In case we would instead want the CompanyID, we can add the UseDisplayValue function argument and assign False to it:


wfr.FillElement Bookmark:="CustomerID", Value:=ControlValue(Control:=![CustomerID],
 UseDisplayValue:=False)

If we want both the ID and the name, the royal way is to add an extra control CustomerName to the form which gives the required extra value. This way you can add it using drag and drop in the Template Designer.

Setting UseDisplayValue:=False is particularly useful in Excel considering that a control’s Display Value produces a string which may cause decimal point interpretation problems. Therefore, in case of Excel, the VBA code generation adds UseDisplayValue:=False for all data items of the following types: Currency, Single and Double and Hyperlink.

Formatting the returned value

The generated code makes it easy to apply a VBA Format expression, as commonly used with numbers and dates. Actually, this is done for you in case of fields of type date, where the formatting expression is copied from your formatting preferences. An example for text in bookmarked location in document


wfr.FillElement Bookmark:="DueDate", Value:=ControlValue(Control:=![Due Date], Format:="dd mmm yy")

or as part of the filename


strFileName = ReplaceIllegalCharacters(ControlValue(![CustomerID]), "_") & _
ControlValue(![Invoice Date], Format:="yymmdd")

Note that you are free to change the expression used in a specific line any way you like.

Inserting tables

Tables of data regularly appear in documents. To have them made available requires you to work with Access subforms. The image at the top shows control LaborLine Subform dropped on a document location. A dialog pops up allowing you to change the originally given order of columns, optionally skipping some by unchecking the column name. After insertion you can change the column names or styling, add a summary row all to your liking. Only the bookmarks indicating the first row (LaborLinesubform) and the column bookmarks (LaborLinesubform_Hours, etc.) in that row need to stay as the are used in the table filling process.

Lists

In your Access form, when you have a subform with just one column, the Template Designer will interpret this as meaning a list and let you drop this in a certain location, using the name of the subform control as the named range for the first cell. When creating the workbook it will fill cells downward starting with this cell.


efr.FillColumnFromArray Range:="AppSupportedFeaturessubform", _
Array1D:=ValuesInSubFormColumn(control:=![AppSupportedFeatures subform], _
ColumnControlName:="Feature")