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.

Step 1: Build User Interface and create references

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:

1. Title of document
2. Header information
   1. Put information about the report. Who is the report about? What was the last refresh date? What project is the
3. Interactive buttons
   1. Make buttons to trigger VBA code. These describe the expected user interaction with the report. Use labels that clearly describe the button’s function.
4. Entry Fields
   1. These are where the data actually lives. In this picture they’re single cells, but it could be columns or tables.

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.

Step 2: Get data

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.

Step 3: Save data

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.