In Part 1 we set up our development environment, added the ActiveX Data Objects reference to our project, and started writing our first bit of code that will run when the workbook opens. In this post, we will connect to the database, retrieve the data we need, and format it into a presentable report for archiving. I will be using server storage metrics from SolarWinds Orion as the test data.
Latest posts by Andrew Jacops (see all)

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.

Visual Basic in Excel

Once it has opened, double-click on the wkbReport under VBAProject on the left and locate the Workbook_Open() subroutine.

VBA Project

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.

Play Button

Open the main Excel window and you will see the fruits of our endeavor!

Storage Overview

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.

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:

Storage Overview Data

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…

VBAProject Properties

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.

VBA Project Protection

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.

VBAProject Password

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.

0 Comments

Leave a reply

Your email address will not be published.

*

© 4sysops 2006 - 2022

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account