+1 (317) 804-2330 | info@ballsystems.com | Blog
One of the easiest things to automate in your office is reporting. Typical reports may be for financial, project status, test results, test reports, machine throughput/performance, line efficiency, KPIs, scheduling, inventory, or timecards. This blog will explain the 3 basic steps to create interactive reports and dashboards using Excel. It assumes that you have an organized data source, such as a database, and assumes some basic understanding of VBA.
Start your report by laying out how you want the information to appear. The example below is for a weekly status report. Consider the following sections:
Next create static references to cells or ranges that will contain report data. In the image below, Cell B:3 has been named EmployeeName using the Name Manager in the Formulas ribbon. This creates a reference that is useful later for two reasons. First, if the cell is moved, no VBA code needs to change. Meaning an inserted or deleted column or row doesn’t require code to be rewritten. Second, instead of referencing the cell with:
EmployeeName = Range.(“B:3”). Value
We use:
EmployeeName = Range.(“EmployeeName”).Value
This creates code that is easier to read.
Data can be inserted into your report using Power Query, VBA, or a combination of both. Power Query offers a GUI to create queries and manipulate the resulting data. It creates a Table object that can be placed into your workbook and refreshed. This approach works great if you have simple queries, but becomes tedious to manage as the complexity increases.
The other approach is to use VBA to send SQL commands to your database. First you’ll need to enable an object library to use an ADODB connection (VBA Editor -> Tools -> References, add Microsoft Forms 2.0 Object Library). The function ‘sqlQuery’ will open a connection and send commands. The sub ‘testConnection’ will send a SQL query and analyze the output.
Public Function sqlQuery(query as string)
‘setup connection string
Dim cnn As New ADODB.Connection
Dim ConnectionString As String
Dim rst As New ADODB.Recordset
ConnectionString = "Provider=SQLOLEDB.1;Password=pass;Persist Security Info=True;" & _
"User ID=id;Data Source=sourceURL;Use Procedure for Prepare=1;Auto Translate=True;" & _
"Packet Size=4096;Use Encryption for Data=False;” & _
“Tag with column collation when possible=False;Initial Catalog=IDEP"
‘open connection
cnn.Open ConnectionString
cnn.CommandTimeout = 120
‘perform query
Set rst = cnn.Execute(query)
sqlQuery = rst.GetRows
If VarType(queryIDEP) <> 8204 Then ‘check if type is 2d array (8204) to see if any data was returned
queryIDEP = Null
End If
End Function
Sub testConnection()
Dim Query as string
Query = “SELECT id, name, content FROM tbl_table”
Output = sqlQuery(Query)
If Not IsNull(Output) Then ‘our query returned data
For i = 0 to UBound(Output, 2) ‘loop through each row of data
Id = Ouput(0, i) ‘extract each value from array
Name = Output(1, i)
Content = Ouput(2, i)
‘do something with the data
Next i
End If
End Sub
This method of getting data requires more technical knowledge of the database schema and how to manipulate the data once it has been retrieved. The biggest advantage is having complete control over your entire report.
While Power Query is great at retrieving data, it lacks functionality to save any changes. VBA comes to the rescue once again. If we use the ‘sqlQuery’ Function from above, it is just as easy to create insert statements.
Sub saveExample()
Dim query as string
Id = 1
Name = “Joe Schmoe”
Content = “Engineer”
Query = “INSERT INTO tbl_Table (id, name, content) " & _
"VALUES (‘” & Id & “’, ‘” & Name & “’, ‘” & Content & “’)”
Output = sqlQuery(Query)
End Sub
Typically, the values to insert would be read from the worksheet. Be careful to avoid SQL injection vulnerabilities and make sure all your data types match.
These 3 steps will help you extract, save, and organize data in Excel. Using VBA can be intimidating and seemingly tedious at first, but allows complete control over the layout and functionality of your report. Designing the whole report and establishing named ranges will ease writing and maintaining code.
Looking for partner to help solve your toughest software challenges? Contact us today to see how our programmers can help you.
Ball Systems designs, develops, and delivers custom test systems and produces comprehensive build-to-print systems for companies creating or manufacturing critical electronic or electro-mechanical components for automotive, aerospace and defense and consumer appliance applications.
Blog Comments