Using vROPs with VBA for prettier Reports

When asked to produce detailed customer reports, I had some initial struggles with vROPs output format when using multiple views.

Outputting the details report to CSV and messing around with some Visual Basic achieved exactly what I was looking for. All-be-it, I didn’t know VBA, but like all good pieces of code, can be found online and modified to your needs.

What did I want to see from my report?

One spreadsheet neatly broken down into tabs, with bold headings on each sheet containing the following information/metrics:

  • Host Overview
  • VM Overview
  • vDisk Overview
  • Cluster Overview
  • Datastore Overview
  • Snapshot Overview
  • Cluster Performance
  • Host Performance
  • VM Performance

How did I achieve this?

I enabled all the metrics required in the vROPs Policy and created a view for each bullet point listed above.

Note, my first view created was a “Blank Sheet”, which was required for correct formatting.

This was then all combined into a single vROPs Report, which was then run/save as a CSV file.

The VBA Code

Attribute VB_Name = "vReporter"
Sub vReporter()
            Dim hdr As Range, rng As Range, ws As Worksheet, wsn As Worksheet
            Dim rw As Long, lr As Long, b As Long, blks As Long
            Set ws = ActiveSheet
            With ws
                Set hdr = .Cells(1, 1)
                lr = .Cells(Rows.Count, 5).End(xlUp).Row
                rw = 2
                blks = Application.CountBlank(.Range(.Cells(rw, 1), .Cells(lr, 1))) + 1
                For b = 1 To blks
                    Set rng = .Cells(rw, 1).CurrentRegion
                    Set rng = rng.Offset(-CBool(b = 1), 0)
                    Set wsn = Worksheets.Add(after:=Sheets(Sheets.Count))
                    With wsn
                        hdr.Copy Destination:=.Cells(1, 1)
                        rng.Copy Destination:=.Cells(2, 1)
                    End With
                    rw = rw + rng.Rows.Count + 1
                    Set rng = Nothing
                    Set wsn = Nothing
                    If rw > lr Then Exit For
                Next b
            End With
            Set rng = Nothing
            Set ws = Nothing
        Worksheets("Sheet1").Rows(1).EntireRow.Delete
        Sheets("Sheet1").Name = "Hosts Overview"
        Sheets("Sheet2").Name = "VMs Overview"
        Sheets("Sheet3").Name = "vDisks Overview"
        Sheets("Sheet4").Name = "Clusters Overview"
        Sheets("Sheet5").Name = "Datastores Overview"
        Sheets("Sheet6").Name = "Snapshots Overview"
        Sheets("Sheet7").Name = "Cluster Performance"
        Sheets("Sheet8").Name = "Hosts Performance"
        Sheets("Sheet9").Name = "VMs Performance"
        For Each sht In ThisWorkbook.Worksheets
        sht.Cells.EntireColumn.AutoFit
        Next sht
        For Each ws In ThisWorkbook.Sheets
        ws.Range("A1").EntireRow.Delete
        Next ws
        For Each ws In ThisWorkbook.Sheets
        ws.Range("A1:Z1").AutoFilter
        Next ws
        For Each ws In ThisWorkbook.Sheets
        ws.Range("A1:Z1").Font.Bold = True
        Next ws
End Sub

The Results

Before Running VBAAfter Running VBA

Download File

Download File

If you have any questions or would like any assistance with this process, leave a comment below and I’ll get back to you ASAP.

Leave a Reply

Your email address will not be published. Required fields are marked *