Pulling and Retrieving Data and Conditional Formatting Using Visual Basics in Excel and Access
The entire Microsoft suite of productivity tools work well together. Excel and Access work well together to allow the user to keep data in Access but pull it into Excel. In this article, we'll first pull data from an external location and then use conditional formatting to change the way it's displayed to the user.
Data Virtualization and Importing to Excel
When dealing with a large amount of data, users keep their records in an Access database. Access and Excel work well together. You can use VBA to import the virtualized or external data to the local spreadsheet and then use the spreadsheet to manipulate and present data in a report format. You can retrieve the data when the workbook opens or using a button that the user clicks and triggers an event. In this section, we'll show you how to import data from an external location.
The first part of your code is to create the subroutine. In this example, we are using a basic subroutine, but you can use this code in an event handler as well.
Sub ImportData()
Dim con As Object, rs As Object
Dim colindex As Integer
Dim database As String
Dim range As Range
End Sub
In the above code, we create a basic subroutine with some variables to start off the code. We need these variables to work through the import process. This first line of code will be used for the connection and the recordset object. The connection connects to the database and the recordset object is what's used to store the data once it's imported from the database.
The other variables are used as columns and ranges for the data as we work to place it in the spreadsheet. With the variables created, we can start with the data import process. First, we set the database name and the database connection object.
Sub ImportData()
Dim con As Object, rs As Object
Dim colindex As Integer
Dim database As String
Dim range As Range
database = "D:\db\database.mdb"
Set range = Sheets("Sheet1").Range("A1")
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & database & ";"
End Sub
We added a database name, range and connection variable values to the code. You should point the database variable to the location of your own Access database. We then set a range, which you've seen in other lessons. The connection objects is something that hasn't been seen yet. This code is the database connection source that is used to open the database name.
After we connect to the database, we now need to pull the records and assign them to their own variable. The following code queries the database for records and assigns the results to the rs object variable.
Sub ImportData()
Dim con As Object, rs As Object
Dim colindex As Integer
Dim database As String
Dim range As Range
database = "D:\db\database.mdb"
Set range = Sheets("Sheet1").Range("A1")
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & database & ";"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Employees'", con, , , adCmdText
End Sub
In the above code, we set the rs variable to a recordset object. It's this object that then queries the database for records. We use a basic SELECT query to retrieve all records from the Employees table. The con object is also specified because we need to tell the recordset object where it can find the database to query. The adCmdText is a predefined variable used to tell the recordset object that you are using text for your query. This is another variable available in Visual Basic for database connections and queries.
When the recordset object queries the database, it then assigns the records to the rs variable. The number of records is determined by the number of records returned by the query. This means it could be 1 or 1,000 records. It can even be 0 records if the table is empty.
Now we need to transfer the data from the recordset to the Excel spreadsheet. The following code loops through the recordset and assigns the data to spreadsheet cells.
Sub ImportData()
Dim con As Object, rs As Object
Dim colindex As Integer
Dim database As String
Dim range As Range
database = "D:\db\database.mdb"
Set range = Sheets("Sheet1").Range("A1")
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & database & ";"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Employees'", con, , , adCmdText
For colindex = 0 To rs.Fields.Count - 1
TargetRange.Offset(1, colindex).Value = rs.Fields(colindex).Name
Next
End Sub
Notice that we added a For loop to our code. This loop goes through each field in the recordset object and assigns a column name to the spreadsheet's cell. Notice that we use "Count – 1" as the loop termination clause. Because we started the loop at 0, we need to terminate the loop before it gets to the final count number or your program will throw an error.
The Fields property contains the number of fields in the recordset's columns. You could have 1 or 1000 column names in your recordset, but since we have a For loop dynamically handling the values, we don't need to worry about the number retrieved from the database. We use the field index number to retrieve the field information and assign it to the right column across the spreadsheet.
Sub ImportData()
Dim con As Object, rs As Object
Dim colindex As Integer
Dim database As String
Dim range As Range
database = "D:\db\database.mdb"
Set range = Sheets("Sheet1").Range("A1")
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & database & ";"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Employees'", con, , , adCmdText
For colindex = 0 To rs.Fields.Count - 1
TargetRange.Offset(1, colindex).Value = rs.Fields(colindex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs
End Sub
Notice that the last line copies from the recordset and pastes it to an offset value. Remember that the offset value is a location relative to the currently active cell.
Since we've been working with opened databases and files, we now need to complete the code by closing the opened connections. The following code snippet completes the code.
Sub ImportData()
Dim con As Object, rs As Object
Dim colindex As Integer
Dim database As String
Dim range As Range
database = "D:\db\database.mdb"
Set range = Sheets("Sheet1").Range("A1")
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & database & ";"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Employees'", con, , , adCmdText
For colindex = 0 To rs.Fields.Count - 1
TargetRange.Offset(1, colindex).Value = rs.Fields(colindex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs
Application.ScreenUpdating = True
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Sub
The code we added to the bottom of the subroutine closes the connection and the recordset object. If we didn't add this code, your program would keep the file open and you could have memory leaks and bugs in your code.
Conditional Formatting
With the data imported, you probably want to format your data. Conditional formatting is a way to format the way your data displays depending on the data stored in a cell. You perform conditional formatting based on formulas you write in your VB code.
Create another subroutine in your module. The first step is to select the cells that you want to format. The following code shows you again how to select cells.
Sub FormatCells()
Range("A3").Select
End Sub
In the above code, we use just one cell in our example, but you can use a larger range for your own data. Just ensure that the selected cells contain the data that you want to format.
Now we need to run the format condition. Take a look at the following code.
Sub FormatCells()
Range("A3").Select
Range("A3:H66").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"
End Sub
In the code above, we use the FormatConditions.Add to add a conditional format. The format uses a formula that determines the way a cell displays data based on the contained text.
With the text formatted, we can now set the colors for the cells. Let's look at an example.
Sub FormatCells()
Range("A3").Select
Range("A3:H66").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"
Range("A3:H66").Interior.PatternColorIndex = xlAutomatic
Range("A3:H66").Interior .Color = 5287936
Range("A3:H66").Interior.TintAndShade = 0
End Sub
In the above code, we've added some additional formatting to the cells. The first one sets a color pattern, which is given a value that is a predefined automatic integer value. The xlAutomatic variable is used to define that the color index should be the Excel default. We then set an interior color attribute. The color is a number value. For your own color, you'll need to look up the color value either on the Internet or in Microsoft's official documentation. All colors and their variants are documented to give you the full range of the color spectrum for your spreadsheets. Finally, we set no tint or shade to the values, so there is no shading.
Run this code with your data import code and you'll see that the two subroutines work together to provide you with data import from a remote database and then a conditional format that only changes the data presentation based on the input.
Sparklines
Starting with Excel 2010, Microsoft introduced a new feature called sparklines. Sparklines are small line graphs that show you a visual presentation of a row of cells. They are usually placed at the end of a row in the last column of your spreadsheet. The sparkline in a miniature line chart that represents the data you see in the row. You can automatically create these small charts using VBA. Just remember that they are only compatible with Excel 2010 and later.
Creating a Sparkline Column
Before you create the code for your sparkline cells, you first need to determine where you want to place the sparkline. A sparkline cell is usually placed directly after the rest of your cells in a row. You must define this column before you start to ensure that you place it in the right place in your code.
Before you start, create a new module in the VBE and start a new subroutine in your module. We'll name the subroutine CreateSpark. The following code creates a shell subroutine for your application.
Public spark As SparklineGroup
Sub CreateSpark()
End Sub
Notice that we included a global variable in the module. This variable will be used in the module code and must be available outside of the local subroutine. This means it must be outside of the subroutine code.
With the shell subroutine created, you can now add your code to it. We first need to create variables to contain the objects that we want to display on the page. Let's create those variables.
Public spark As SparklineGroup
Sub CreateSpark()
Dim range, datarange As Range
Dim LastRow, LastColumn As Long
End Sub
We create two sets of variables. The first set is two Range objects. These range objects will be used to contain the sparkline's range of cells that will be used to determine the way the line chart displays.
The next section is the last row and column values are used to determine the end of both rows and columns, so the sparklines grow as the data grows.
With these variables created, we can now determine the last row and column on the spreadsheet. Let's add this code.
Public spark As SparklineGroup
Sub CreateSpark()
Dim range, datarange As Range
Dim LastRow, LastColumn As Long
LastRow = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Rows.Count
LastColumn = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Columns.Count
End Sub
In the above code, we calculate the last row and column necessary to represent the data. We start the range at the first column and row, which is A1.
The next step is to set up the sparkline column and data range. The sparkline range is the field that contains the line chart that represents your data. The data range is the group of cells that contain the data you want to represent in the sparkline. Let's take a look at the code.
Public spark As SparklineGroup
Sub CreateSpark()
Dim range, datarange As Range
Dim LastRow, LastColumn As Long
LastRow = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Rows.Count
LastColumn = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Columns.Count
Set range = Range("c3", Cells(LastRow, 3))
Set datarange = Range("d3", Cells(LastRow, LastColumn).Address())
End Sub
We added the range variable, which sets the active cell for the sparkline column based on the LastRow variable that we defined earlier. The datarange variable sets the cells that contain the actual data. This is the data used to determine the way the sparkline is drawn on the spreadsheet.
We've defined a global variable, and now we want to use it. The global variable's data type is SparklineGroup contains an Add function that actually inserts the sparkline graph on the spreadsheet. Let's take a look at the code.
Public spark As SparklineGroup
Sub CreateSpark()
Dim range, datarange As Range
Dim LastRow, LastColumn As Long
LastRow = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Rows.Count
LastColumn = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Columns.Count
Set range = Range("c3", Cells(LastRow, 3))
Set datarange = Range("d3", Cells(LastRow, LastColumn).Address())
Set spark = datarange.SparklineGroups.Add(Type:=xlSparkLine, SourceData:= datarange.Address)
End Sub
We added another line of code that sets the spark variable. The spark variable uses the datarange variable to then add a sparkline to the range. Notice that there are two parameters set in the function. The first one defines the type. The xlSparkLine variable defines the type of chart, which is the default line chart you see in most spreadsheet applications. The SourceData parameter defines the range of cells that contains the cells. The sparkline object is then assigned to the spark variable. This variable is used to customize and manipulate the sparkline object.
With the sparkline created, we can now customize it. We can customize it using different attributes available in the sparkline class. For instance, you can change the theme color using the ThemeColor attribute. The following code adds a line to customize the theme color.
Public spark As SparklineGroup
Sub CreateSpark()
Dim range, datarange As Range
Dim LastRow, LastColumn As Long
LastRow = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Rows.Count
LastColumn = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Columns.Count
Set range = Range("c3", Cells(LastRow, 3))
Set datarange = Range("d3", Cells(LastRow, LastColumn).Address())
Set spark = datarange.SparklineGroups.Add(Type:=xlSparkLine, SourceData:= datarange.Address)
spark.SeriesColor.ThemeColor = 5
End Sub
In the above code, we set the series color using a static integer 5. You can use any integer provided it's a valid integer that describes a theme for the sparkline.
After we create the sparkline and then set its theme, you can size the charts. You can size the chart using two properties named MaxScaleType and MinScaleType. Let's take a look at the code.
Public spark As SparklineGroup
Sub CreateSpark()
Dim range, datarange As Range
Dim LastRow, LastColumn As Long
LastRow = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Rows.Count
LastColumn = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Columns.Count
Set range = Range("c3", Cells(LastRow, 3))
Set datarange = Range("d3", Cells(LastRow, LastColumn).Address())
Set spark = datarange.SparklineGroups.Add(Type:=xlSparkLine, SourceData:= datarange.Address)
spark.SeriesColor.ThemeColor = 5
spark.Axes.Vertical.MaxScaleType = xlSparkScaleGroup
spark.Axes.Vertical.MinScaleType = xlSparkScaleGroup
End Sub
Notice that we again use the global sparkline variable to customize the graph. The spark variable contains the object itself, so any customizations should be used on the spark variable and none of the other objects.
The first line we added defines the maximum scale, and we assign the predefined Excel variable named xlSparkScaleGroup. Then, we assign the MinScaleType to the same predefined variable. This keeps the sparkline graph contained within the end column without spreading it across several columns.
Run this code in your editor with some data in the appropriate cells. Once you have a sparkline graph, you might then need to delete them at some point. The next section shows you how to delete a sparkline dynamically.
Delete a Sparkline
You don't want to put the deletion code in the same subroutine, so it's best to create a new subroutine to delete your sparkline. We'll start the code by creating a new subroutine named DeleteSpark.
Sub DeleteSpark()
End Sub
Notice that we don't need to add another global variable for the sparkline object. We've already created the global variable with the sparkline creation code, so you only need to create the subroutine. We can use the existing global variable to work with the delete function.
We now need to create the variables needed to work with the delete function. For this subroutine, we only need one range variable.
Sub DeleteSpark()
Dim range As Range
End Sub
In the above call, we've only created a new Range object. Now, we can move on to the code that actually deletes the sparkline.
First, we need to assign the range variable with the used range for the sparkline. The following code assigns this cell range a reference.
Sub DeleteSpark()
Dim range As Range
Set range = ActiveWorkbook.ActiveSheet.UsedRange
End Sub
In the above code, the UsedRange property identifies the range used in the currently active spreadsheet and assigns it to the range variable. With this variable defined, we can now delete the sparkline.
Sub DeleteSpark()
Dim range As Range
Set range = ActiveWorkbook.ActiveSheet.UsedRange
For Each sparkline In range.SparklineGroups
sparkline.Delete
Next sparkline
End Sub
We haven't seen many For Each loops until now. For Each loops are excellent to iterate through a number of collection values. A sparkline group is a number of sparkline graphs that display in a spreadsheet. Instead of trying to loop through using a regular For loop, it's better to use the For Each loop since you can go through each object instead of trying to reference them using an array.
In the above code, we loop through each sparkline in the cells that contain a graph. We know the range from the range variable, and the Visual Basic compiler identifies the sparkline graphs within the range. Each sparkline graph found in the range is identified and deleted using the Delete function.
This subroutine deletes all sparkline graphs within the spreadsheet range we define. This subroutine is beneficial if you need to delete all sparklines and redraw them based on new code or moving them to a new location. Regardless of what you need the delete function for, you'll often find that you need to delete sparklines at one point to reassign data or adjust the way it's sketched on the spreadsheet.
Sparklines are a graphical feature that can take your graphs and charts to another level. Instead of only focusing on one graph that covers all data in your spreadsheet, you can use a sparkline graph to cover just one row of data. These mini graphs let you dissect data in your spreadsheets and review charts based on granular level information. Just remember that sparkline graphs are only available in Excel 2010 or newer versions. If you attempt to use them in older versions of Excel, this example code will give you an error.
