Working with Data in Excel

A common scenario in any company is to communicate public data via charts, tables, and graphs through periodic reports. By allowing users to export data directly to Excel, you empower them to leverage the rich set of Excel features to create their own charts and tables at a whim, without having to modify the data or design of the architecture of your Access database solution.

The first example here utilizes a form with a list box control that exposes Queries within the application. The Row Source type of the list box control in Access is Table/Query by default, but you can fill your list box in several different ways. If you want users to be able to choose any Report to export, it is pretty easy to add a few lines of code to populate the list box programmatically. The following code loops through each of the Query objects in the application and adds their names to the list box. The code could then be added to the form's OnLoad event (as is done for the OnLoad event for Export Report Manager on the form):

Dim qdQueryName As QueryDef

'Clear the list if it is already filled lstExport.RowSource = "" lstExport.RowSourceType = "Value List"

'Add all of the Query names to the List Box For Each qdQueryName In Application.CurrentDb.QueryDefs If (InStr(1, qdQueryName.Name, = 0) Then

Me.lstExport.Addltem qdQueryName.Name End If


This same functionality can be accomplished by querying the MSysObjects system table. If you leave the Row Source Type as Table/Query for the list box, users can query the MSysObjects system table to get all of the names for any given database objects. A SQL statement can be created to query the MSysObjects system table for a list of all of the queries and place the SQL in the Row Source property for the list box.

'Create the SQL Statement

Dim strSQL As String

strSQL = _

"SELECT MSysObjects.Name " & _

"FROM MSysObjects " & _

"WHERE (((MSysObjects.Name) Not Like ""-""

) AND ((MSysObjects.Type)=5));"

'Set the Row Source with the SQL Statement

Me.lstExport.RowSourceType = "Table/Query"

Me.lstExport.RowSource = strSQL

This shows all of the Query object names in the list box that are not temp queries created by the system — those are denoted by starting with a tilde (~) character. If you don't want users to be able to choose every Query in the database, the names of the object can always be hard-coded into the Row Source property for the list box. Now that there is a list of queries in the list box control, code can be created to export the results of one of those queries to an Excel workbook.

Exporting to Excel can be completed in several different ways, two of which are presented here. The first involves opening Excel, creating a new workbook with a new worksheet, and transferring the data into the worksheet. The second utilizes the SaveAs method of the RunCommand object to automate this process. Additionally, the OutputTo method can be used to export to Excel, which is covered later in the code examples of working with Word.

Was this article helpful?

0 0

Post a comment