Project Background and Description

My first data analysis position was as a factotum for an MS Access Runtime Database desktop software written in VBA. I was responsible for software installation, server and database formatting, instruction on software use, creation of software updates, sales demonstrations, and more. This software was designed for residential real estate development professionals (sales executives, sales assistants, software development professionals).

I served in this role for eight years and left the position as the software was being migrated over to a web-based system. After leaving this position, I had not expected to ever see a major data operation based on VBA, which I had assumed was being replaced by the major database systems (SQL Server, Oracle, DB2, etc.) for reasons of capacity and processing speed.

In this article I will display how to use VBA to access a SQL Server data source and automate some common data procedures that appear in branch banking operations.

Project Scope

Since that time, I have seen many projects and company data environments, which use VBA as a major data management tool. A typical history is a development of a small spreadsheet/table operation, which expands as the organizational needs expand, without making the decision to move to a major system. This is often due to expense and lack of organizational resolve. My experience has been that VBA remains an important data management tool for many small to medium companies. I am presenting a project that I developed on a contract for a telecom operation in Northern Georgia.

The system needed to include the following:

• Ability to allow both internal and external users to access the application without downloading any software.
• Ability to interface with the existing data warehouse application (SQL Server 2014).
• Ability to supply telecom inventory data to branch managers through an automated email delivery system.
• Creation of regularly scheduled and ad hoc reports on USOC Inventory.
• Supply of other data analysis products as needed.

The deliverables include the following:

• An Excel *.xlsm file containing a VBA automation SQL script and a switchboard with command buttons for 1) the display of USOC Inventory data; 2) the sending of an email through MS Outlook with the spreadsheet as an attached file and 3) an exit command button to close the program.
• A SQL Server table to serve as a data source for the VBA automation screen.

Create Data and Dashboard Worksheets

The Process begins with the creation of an MS Excel workbook with two blank worksheets. The first we will call DATA and the second DASHBOARD. The DATA worksheet will contain the data to be imported from the SQL Server database, which exists in my project on a local server. This database can be located on a remote server and can be accessed by the Data Import process, if necessary.

To populate the DATA worksheet, navigate to the worksheet, click Data à Get Data à From Database à From SQL Server Database.

Fig.1-1 Get_data

A screen prompt will appear that requires the server and the database. The data source settings are displayed below. The server is “desktop[2025b36\tlsdeveloper”, and the database id is “Master” in fig. 1-2.

Fig. 1-2 Navigator 

Once the SQL Server database is selected and you press OK, the Settings in Fig. 1-3 will appear. This will allow you to select the database and data columns necessary for the spreadsheet report. You can also add derived columns or aggregattions as necessary. Once the columns are set, this spreadsheet is ready for display

Fig. 1-3 Data Source Settings 

Now, we have the data imported from our SQL Database into the DATA worksheet.

Fig. 1-4 Data imported into the datasheet 

I am using the other worksheet (“2010-08_ALL_ITO_Svc_Inventory”) as the dashboard. I copy some selected columns to this Dashboard from the “DATA” worksheet. To this Dashboard (fig. 1-4), I have also decided to add some charts and pivot tables based on the data displayed. This Dashboard will be the destination for one of the command buttons which we will be inserting into a switchboard (described below).

Fig. 1-5 Dashboard

Create the Switchboard screen in Visual Basic

Now, open the Visual Basic section of MS Excel by clicking ALT + F8, or click the Developer tab and select Visual Basic. Create a switchboard by clicking Insert àUserform. Rename the Userform to Switchboard. To this Switchboard form, add three command buttons and one large text title at the top of the page.

Fig. 2-1 Switchboard 

A simplified description of the code behind the three command buttons follows.

First, rename Command button 1 to “Monthly Call Center Data”. This command button will display the Dashboard created above Dashboard (fig 1-5). Here is the simple code behind the command button:

Private Sub CommandButton1_Click()
  Unload Me
  ActiveWindow.DisplayWorkbookTabs = False
  Sheets("2010-08_ALL_ITO_Svc_Inventory").Visible = True
End Sub

Note: “2010-08_ALL_ITO_Svc_Inventory” is the name for the SQL SERVER data which I have imported into the spreadsheet

Next, rename Command Button 2 to “Send Email”. The purpose of this command button is to save the spreadsheet as a *.pdf file and open the MS Outlook send email screen, as shown in Fig 2-2. Once this is done, select email addressee and add desired text message before sending the email. Code for this command button follows:

Private Sub cmdEmail_Click()
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
 filename:="BudgetData", Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, IgnorePrintAreas:=False, _
 OpenAfterPublish:=False
 Application.Dialogs(xlDialogSendMail).Show arg1:="", _
 arg2:=EmailTitle, arg3:=Receipt
End Sub

The file format can be changed on the line below the _Click() action by substituting TypeXLS, etc. for xlTypePDF. “ActiveSheet.ExportAsFixedFormat” is a built-in dialog, which is explained with an example at http://codevba.com/excel/dialogs.htm#SendMail

Fig. 2-2 MS Outlook screen 

Also remember that the appropriate references need to be selected when using tools like MS Outlook and VBA (see screen): The Reference is selected in the Visual Basic page, under Tools > Reference > then select the appropriate software (Excel, Outlook), depending on which product(s) are referred to in the code pages. You can see my selections in Fig. 2-3

Fig. 2-3 Product Reference

Set Security on Worksheets to Prevent Unauthorized Changes

One very important issue with VBA forms is protecting data that is behind the form from changes by users. This issue can get complicated. The simplest procedure I have found is to:

• Highlight each data worksheet (“Data”, “2010-08_ALL_ITO_Svc_Inventory”), right click the selected rows, click Format Cells > Protection > Locked (click). This is shown in Fig. 3-1
• Make sure the Locked box is checked, then click OK.

Fig. 3-1 Protect Cells 

• Click File >  Protect Workbook à Protect Current Sheet
• The Protect Sheet dialog box will appear, shown in Fig. 3-2

Fig. 3-2 Protect Sheet 

• Make sure all boxes are unchecked.
• Repeat these steps for each worksheet

This will prevent unauthorized users from changing data. The users may open the worksheets, but are prevented from changing anything.

The SQL Server Data Import

The purpose of this project is to use VBA Excel’s convenient data analysis and processing advantages and to connect to telecom product data located in the SQL Server table. An issue I experienced in building up the SQL Server table and with the SSIS import process (the SSIS import project is illustrated with the graphic below), which I had never experienced before, were compatibility errors with the Input and Output data. Specifically the Input varchar data is assigned the Source Code 1252, and the Output data is assigned Source Code 65001.

The compatibility errors prevent the smooth input of the call center data through import of the data from a *.txt or *.csv file. Microsoft suggests a workaround by changing the data type Output to DT-WSTR, but this doesn’t work for me: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d4eb033-2c45-47e4-9e29-f20214122dd3/ssis-change-code-page-1252-ansi-to-utf8-65001?forum=sqlintegrationservices.

I import the text data into an MS Access table and from there use the Data Flow package Source/Destination Assistants to import data from MS Access into a SQL Server table in the Master database. One other caveat in the SSIS Data Flow package process is to open SQL Server Data Tools as administrator; otherwise the import process does not contain sufficient permissions for manipulating the data import.

Import error due to insufficient permission.

The error message will suggest opening SSIS as administrator.

For me, there was some adjustment necessary, since earlier versions of SQL Server contained all necessary products inside of SSMS. I am using SS Data Tools 2015 to create the SSIS Import/Export packages, using SSMS 2017 to run queries on the local SQL Server tables, and SSIS 2017 Import/export piece for 64-bit processors to run a quick import, when necessary.

The data flow source/destination assistants, processed successfully after opening SS Data Tools 2015 as administrator, are shown below as part of the SSDT package:. Project Design Page (fig. 4-1)

Fig. 4-1 Project Design Page 

Summary

After leaving a data position supporting an Access Runtime product written in VBA, I was pleasantly surprised at the popularity of the VBA product used for data analysis reporting. I assume that many small to medium-sized companies began with automating MS Office products, usually Access and Excel. As the data requirements of the company expand, Microsoft has expanded the data capacity of the Access and Excel products in order to keep clients satisfied with their MS Office products and delay the move to a major data platform (DB2, Oracle, etc). VBA will automate just about anything with some adjusted SQL script. I have described the use of SQL Server as a data source for a VBA spreadsheet automation project, which uses the convenient VBA\SQL product. The dashboard which I have created can also contain pivot tables and charts for more advanced data analysis. I am not sure how long the VBA product will exist but for now the product seems to provide many useful advantages for data analysis people.