- Disable strict name checking with PowerShell - Mon, Sep 1 2014
- ManageEngine Exchange Reporter Plus review - Wed, Aug 20 2014
- EventSentry – Full-spectrum monitoring - Thu, Jul 17 2014
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:
Clear_Sheets
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 SubThe 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.
Integrated Security:
strConnection = "Provider=SQLOLEDB.1;" & _
"Data Source=<SERVER>;" & _
"Initial Catalog=<DATABASE>;" & _
"Integrated Security=SSPI;"
Standard Security:
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.
