Step 1 – Clearing previous data ^
Just like we created the Private Sub Workbook_Open() subroutine, we need to create ones that will perform specific tasks. The first one we must create is the Clear_Sheets() subroutine.
To get started, go to the Visual Basic IDE by clicking on the Developer tab at the top and then the Visual Basic icon on the far left.
Once it has opened, double-click on the wkbReport under VBAProject on the left and locate the Workbook_Open() subroutine.
Remove the following line of code we wrote in the previous article:
MsgBox "Congratulations! You have successfully completed Part One!"
Now we want to add a line to make the Clear_Sheets() function run when the workbook opens. Add the following line:
Below the Workbook_Open() subroutine, we can now add the Clear_Sheets() function. Type the following code:
Private Sub Clear_Sheets() End Sub
The full code block should look like this:
Option Explicit Private Sub Workbook_Open() Clear_Sheets End Sub Private Sub Clear_Sheets() End Sub
Within this subroutine is where I put all of the “reset” code and the default formatting such as font size, color, gridlines, etc. This code will be run every time the workbook is open setting the sheet(s) up for newly acquired data. Add the following lines of code to the Clear_Sheets() sub and then we’ll walk through the code.
' Select shtData worksheet With shtData ' Select all of the cells With .Cells .Clear .Font.Size = 10 .Font.Color = RGB(0, 0, 0) End With ' Create and format a title for the worksheet With .Range("A1:F1") .Merge .Value = "Storage Overview" .Font.Bold = True .Font.Size = 16 .Font.Color = RGB(255, 255, 255) .Interior.Color = RGB(0, 0, 0) .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With ' Label the table headers .Cells(2, 1).Value = "Device" .Cells(2, 2).Value = "Volume" .Cells(2, 3).Value = "Volume Size" .Cells(2, 4).Value = "Percent Available" .Cells(2, 5).Value = "Space Used" .Cells(2, 6).Value = "Space Available" ' Format the table headers With .Range("A2:F2") .Font.Bold = True .Font.Size = 11 .Font.Color = RGB(255, 255, 255) .Interior.Color = RGB(85, 33, 30) End With End With
Following along with the comments
Although I have commented the code, denoted by the single quote (‘), to make it more legible, you can see that the VB code is very straightforward. However, let’s walk through the code so we understand what we are doing. Select the worksheet shtData, named in the previous article, using the With statement. All of the code following the With shtData and ending at the End With line will be executed specifically for this worksheet. The same is applied to all other With statements. They select whatever range or cell we will be working with. It’s an easy way to stay DRY (Don’t Repeat Yourself) with your code.
Next we select all of the cells in the sheet with the With .Cells statement and clear them, change the font size to 10 points, and change the color to black with the RGB() built-in subroutine.
We then select the range A1 to F1, merge them, center vertically and horizontally, set the interior color to black, the font size to 16 points, bold it, change the font to white, and set the value to “Storage Overview”. We “exit” the selected range with the End With line of code.
The next several lines set the values of table headers. The .Cells selector uses grid points to select the cell with the column coming first and the row second. For example, B34 would be (2, 34). We again select a range, this time for the table headers, and format them to bold, 11 point, white font and reddish interior color.
Ensuring your cursor is still somewhere inside the subroutine, click the green “play” button at the top.
Open the main Excel window and you will see the fruits of our endeavor!
Step Two – Connecting to the Database ^
Now that we have a way to reset our worksheet to our default template, we can start pulling and formatting the data from our database. We need to connect to the database, run a query against it, and store that data in a local recordset. To begin this process we need to create a new subroutine called Build_Storage(). I picked this name because it is descriptive. We are going to build our storage worksheet. Add the following code below Clear_Sheets().
Private Sub Build_Storage() ' Create variables Dim adoConnection As New ADODB.Connection Dim adoRecordset As New ADODB.Recordset Dim adoCommand As New ADODB.Command Dim strConnection As String Dim intCol As Integer Dim intRow As Integer End Sub
What we have done here is declare several variables that we will need to use in this subroutine. Specifically, look at the variables declared for the ADODB reference we set up in our last article. These variables would not work if that ActiveX Data Object reference was not there.
Next, add a pointer to this subroutine under Workbook_Open(). Make sure you put it after Clear_Sheets. We want that to run before it.
Private Sub Workbook_Open() Clear_Sheets Build_Storage End Sub
The two types of database connection strings I typically use are Integrated Security and Standard Security. If the users of this custom reporting workbook have view rights (select) on the database, then Integrated Security is the best way to go. It uses their Windows logon credentials to authenticate against the database. It also makes SQL profiling a lot easier.
With Standard Security, there are a lot of risks; number one being the fact that the user id and password to connect to the database is in plain text. This should only be used if you do not want to grant a specific user or group any permission to the database and your code is password protected. We will set that up in this article.
strConnection = "Provider=SQLOLEDB.1;" & _ "Data Source=<SERVER>;" & _ "Initial Catalog=<DATABASE>;" & _ "Integrated Security=SSPI;"
strConnection = "Provider=SQLOLEDB.1;" & _ "Data Source=<SERVER>;" & _ "UID=<USERNAME>;Pwd=<PASSWORD>;" & _ "Initial Catalog=<DATABASE>;"
We will be using integrated security for this example. Insert the code for Integrated Security into the subroutine directly below our last Dim statement. Then add the following code below that:
' Open the connection adoConnection.Open strConnection ' Activate the connection Set adoCommand.ActiveConnection = adoConnection ' Set the SQL query adoCommand.CommandText = "SELECT TOP 10000 " & _ "Nodes.Caption AS NodeName, " & _ "Volumes.VolumeDescription AS VolumeDescription, " & _ "Volumes.VolumeSize AS VolumeSize, " & _ "100 - NULLIF(VolumePercentUsed, -2) AS VolumePercentAvailable, " & _ "Volumes.VolumeSpaceUsed AS VolumeSpaceUsed, " & _ "( NULLIF(VolumeSize, -2) - NULLIF(VolumeSpaceUsed, -2) ) “ & _ “AS VolumeSpaceAvailable " & _ "FROM " & _ "Nodes INNER JOIN Volumes ON ( Nodes.NodeID = Volumes.NodeID ) " & _ "WHERE " & _ "( ( Volumes.VolumeDescription <> 'Physical Memory' ) “ & _ “AND ( Volumes.VolumeDescription <> 'Virtual Memory' ) “ & _ “AND ( Volumes.VolumeDescription LIKE '%Serial%' ) ) " & _ "ORDER BY NodeName ASC " ' Run the query and store the retrieved data in the recordset Set adoRecordset = adoCommand.Execute ‘ Set to the first empty row intRow = 3
In this block of code we opened the connection to the server, activated the connection, set our SQL query and subsequently ran it, and then stored the data within the recordset. We also set our intRow variable to 3 because that is our first empty row after the title and the table headers.
Step Three – Populating the Cells and Closing the Database Connection
In order to get all of the information from the database, we need to loop through the recordset and set the .Value of the cell to the .Value of the recordset. Directly below the line intRow = 3 type the following code to loop through the recordset:
Do While Not adoRecordset.EOF For intCol = 0 To adoRecordset.Fields.Count - 1 With shtData ' Write the data to the cell .Cells(intRow, intCol + 1).Value = adoRecordset.Fields(intCol).Value ' Format just the storage numbers into B, KB, MB, GB, and TB If adoRecordset.Fields(intCol).Name = "VolumeSize" Or adoRecordset.Fields(intCol).Name = "VolumeSpaceUsed" Or adoRecordset.Fields(intCol).Name = "VolumeSpaceAvailable" Then With .Cells(intRow, intCol + 1) ' bytes If .Value < 1000 Then .NumberFormat = "0 \B" ' kilobytes ElseIf .Value < 999500 Then .Value = .Value / 1024 .NumberFormat = "0.000 \K\B" ' megabytes ElseIf .Value < 999500000 Then .Value = .Value / 1048576 .NumberFormat = "0.000 \M\B" ' gigabytes ElseIf .Value < 999500000000# Then .Value = .Value / 1073741824 .NumberFormat = "0.000 \G\B" ' terabytes Else .Value = .Value / 1099511627776# .NumberFormat = "0.000 \T\B" End If End With End If End With ' Go to the next column in the record Next intCol ' Go to the next row intRow = intRow + 1 ' Move to the next record in the recordset adoRecordset.MoveNext Loop
Following along with the comments, what we have done is loop through the entire recordset inserting the values into the cells and then moving on to the next value in the recordset. Then when we are done with all the values in the record, we move on to the next record and increment the row number.
We also added a little bit of spice by formatting the numbers into bytes, megabytes, etc. and adding their identifiers. It is important to note, the original numbers that we calculated are still there. We are just taking advantage of the cell formatting feature in Excel so in the future we can still calculate using these numbers without the need to reformat them again.
Ensuring your cursor is still somewhere inside the subroutine, click the green “play” button at the top.
We should now have real-time, up to date information directly from the database:
Other things you may consider doing is changing the color if the space is above or below a certain threshold or change the percentage available to your desired amount of decimal places.
Step Four – Securing Our Code ^
We have put a lot of effort into creating this automated reporting. The last thing we need is someone to come in, mess up our code, and have to start over. Or worse yet, change it for evil. We need to password protect our code.
In the Visual Basic IDE window, click Tools and then VBAProjectProperties…
When the dialogue opens, select the Protection tab, tick the checkbox for Lock project for viewing, enter your password and the confirm password. Here I have just entered password1.
Be sure to select a password that is not easy to brute force crack. There is no protection against a weak password. Click the OK button, save, and completely close the project.
Now when you reopen the worksheet, magically all of your code runs, and you are presented with the fresh information. Try to go back into your VB IDE and expand the VBAProject and you get presented with a password prompt.
Enter the correct password and you can again edit your code.
Part Two Summary ^
In part two of this three part series, you learned how to format your worksheet, connect to a database, loop through that data, and format it the way you want it in your worksheet. In the next section we will look at building some charts and graphs as well as a dashboard to view these. We will also automatically generate these reports to a saved file with no code.
You can download the VBA script here.