Introduction
Inside an enterprise, especially those with well-established IT system for business operations, specific piece of data or report from the system may be requested from time to time. In some case the requested reports are not provided in the existing front end. In other case the required modification on the existing application may be too involved, if not infeasible.
The MS Excel spreadsheet and VBA customization described in this article is aimed to provide a shortcut to deliver data inquiry function to end user. Utilizing the functionality provided by MS Excel is preferable to coding from scratch. This is also related to another objective of the described application: to let the end user to do their desirable filtering, formatting and the like, with the functions provided by Excel. And the most import thing is, to let the end user do it on their own instead of by our dear fellow developer.
Application Usage
The usage of the application is very simple; user provides their input with the "User Input" sheet and data will be returned to the "Data" sheet. An example is shown below. Please see the next section, "Development", for the things behind the scene.
User selects the data source from the drop down list in the B1 cell. It is quite typical that more than one database can co-exist in an organization.
User selects the report from the drop down list in the B2 cell. The input field(s) for search criteria configured for the selected query will be shown (below and including the B6 cell); besides, the search criteria supplied in last query submission will be loaded also.
After supplied the search criteria, click the green "Run" button to submit the query. Data will than be returned to the "Data" sheet. User could further filter and sort the returned data; these setting will be preserved in next submission of the same query.
Apart from just getting the data out of database, an experimental "save layout" has been implemented. In the current implementation, the local filtering, each column width and data display formatting (e.g. whether the column should be show as number or date) could be saved and recalled for later use.
Development
There are two required tasks to delivery the above function to end user. The first thing is to record the database connection string, the other on is to write the parameterized query.
The connection strings are saved on the "Data Source" worksheet. Examples of connection string of most popular databases are provided in the sample of the article. As illustrated above, the configured connection will be show on the "User Input" worksheet for user's selection.
The parameterized queries should be saved in the "Query" worksheet. Each query should have a Name (column A) for users' selection in the "User Input" worksheet and the parameterized query itself (column B). Each query could have zero or any number of named parameters. The VBA inside worksheet will perform substitution with user inputted values before submitting the actual query to database.
The named parameters, if any, should be prefixed either @s for string or @n for number or @dt for data time. "String substitution", instead of ADO parameter binding functionality, has been chosen for the implementation as named parameter is not supported by ODBC interface. In order to easy my maintenance effort, I would like to write the query with named parameter instead of embedding parameters with "?".
The Code
A very short description of the code inside the Excel file here:
- One of my initial goals of this tool is to shorten the cycle to fulfill the data/reporting request from end user. The "shortest path" I can think of is to let the developer to write the SQL query and letting the end user to do the formatting on their own, with the formatting capability available in Excel.
- User Input sheet: The code here is to handle the user interaction, for example, loading the input fields for selected parameterized query and perform the parameter substitution before submitting the query.
- I want to persist end user input and setting inside the Excel file, my choice is to use XML. Example could be found in the column E in the "Formatting" worksheet and column C in the "Query" worksheet.
- XMLHelper module: Use to hide the complexity of calling to the XML API available in VBA environment.
- SettingStorage module: Use to "save" each kind of Excel objects into XML. A point to note, if I were coding in .Net environment all the tedious, hand written code here could be replaced with reflection.
- ReportSetting module: Encapsulation of the functions in the SettingStorage module to support format saving and recall functionality.
Points of Interest
- The saving and loading of formatting are of experimental only. It is desirable, for example, to handle conditional formatting also. It could be implemented by extending the code inside the SettingStorage module. A point to note, it seems retrieving and setting of sorting order is not available in the Excel Object Model, it means recalling sorting order cannot be implemented.
- As a database client tool, the tool can connect to any database/thing with ODBC or OleDB interface. Apart from Oracle and SQL Server, the list also includes formatted text files, Excel file and even Outlook pst file, and even WMI repository. I am sure you will be surprised by the list shown on the de facto primary reference of connection strings web site.
- The only security issue I considered during my coding is I desired NOT to handle the security issue; therefore please use the code with caution and at your own risk. One of my stand point is the native password protection of Excel (at least on or before version 2003) is so week that it could be cracked with a small piece of code in seconds.
