Sub Process_data()
    'written by Paul Dunn (dunnsept @ gmail dot com)
    'Feb 2010
    'Macro to process vmware vscsiStats data
    'written in and tested in Excel 2003
    'it will expect your data to be in column A and the histogram BINS to be in column B
    'it will create a number of chart-sheets
    'charts will be created on individual tabs. If you run
    'vscsistats -p all -w WID and you have lots of drives this can make for a large
    'and unwieldy spreadsheet. If you have lots of drives, I recommend that you
    'process drives seperately
    
     Dim count As Integer
       Dim start As Integer
       Dim cur As String
        cur = ActiveSheet.Name
        
        Sheets(cur).Select
       start = 7
       count = 7
      Range("A" & start).Select
      
     Do Until IsEmpty(ActiveCell)

      ' Set Do loop to stop on empty cell to count how many data rows we have
      Do Until ((InStr(1, ActiveCell.Value, "Histogram", vbTextCompare)) Or (IsEmpty(ActiveCell)))
         count = count + 1
         ' Step down 1 row from present location.
         ActiveCell.Offset(1, 0).Select
      Loop
        'so we have start and count. start is top of list, count is bottom of list
            'Range("G" & start).Value = "START OF HISTOGRAM DATA"
            'Range("G" & count ).Value = "END OF HISTOGRAM DATA"
            
            'we have start of data, end of data, create the chart
            create_chart start, count, cur
            
            'reset and start looking again
            start = count + 6
            count = count + 6
            Sheets(cur).Select
            Range("a" & start).Select
        
      Loop
End Sub

Sub create_chart(st As Integer, en As Integer, sheet1 As String)
    'creates a chart ;-)
    'Feb 2010 Paul Dunn
    '
    Dim chartname As String
    '
    'read the histogram type, then figure out if it's overall, read or write
    'create a new sheet to hold it.
    '
    '
    'IO Length Charts
    If InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "lengths", vbTextCompare) Then
            If InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Read", vbTextCompare) Then
                chartname = "Read IOLength " & Sheets(sheet1).Range("e" & st - 6).Value
               
            ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Write", vbTextCompare) Then
                chartname = "Write IOLength " & Sheets(sheet1).Range("e" & st - 6).Value
                
            Else
                chartname = "IOLength " & Sheets(sheet1).Range("e" & st - 6).Value
                
            End If
            
    '
    'seek distance
    ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "LBNs", vbTextCompare) Then
            If InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Read", vbTextCompare) Then
                chartname = "Read SeekDistance " & Sheets(sheet1).Range("e" & st - 6).Value
               
                
            ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Write", vbTextCompare) Then
                chartname = "Write SeekDistance " & Sheets(sheet1).Range("e" & st - 6).Value
              
             ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "closest", vbTextCompare) Then
                chartname = "Closest SeekDistance " & Sheets(sheet1).Range("e" & st - 6).Value
                
            Else
                chartname = "SeekDistance " & Sheets(sheet1).Range("e" & st - 6).Value
               
            End If
        '
    'interarrival latency charts
    '
    ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "interarrival", vbTextCompare) Then
            If InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Read", vbTextCompare) Then
                chartname = "Interarrival Read Latency " & Sheets(sheet1).Range("e" & st - 6).Value
                
            ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Write", vbTextCompare) Then
                chartname = "Interarrival Write  " & Sheets(sheet1).Range("e" & st - 6).Value
                
            Else
                chartname = "Interarrival Latency " & Sheets(sheet1).Range("e" & st - 6).Value
                
            End If
    
    'Latency charts
    ElseIf (InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "latency", vbTextCompare) And Not ((InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "interarrival", vbTextCompare)))) Then
            
            If InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Read", vbTextCompare) Then
                chartname = "Read Latency " & Sheets(sheet1).Range("e" & st - 6).Value
                
            ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Write", vbTextCompare) Then
                chartname = "Write Latency " & Sheets(sheet1).Range("e" & st - 6).Value
                
            Else
                chartname = "Latency " & Sheets(sheet1).Range("e" & st - 6).Value
               
            End If
    '

    '
    'outstanding IO charts
    ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "outstanding", vbTextCompare) Then
            If InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Read", vbTextCompare) Then
                chartname = "Outstanding Read IOs " & Sheets(sheet1).Range("e" & st - 6).Value
                
            ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "Write", vbTextCompare) Then
                chartname = "Outstanding Write IOs " & Sheets(sheet1).Range("e" & st - 6).Value
                
            Else
                chartname = "Outstanding IOs " & Sheets(sheet1).Range("e" & st - 6).Value
                
            End If
        End If
    
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets(sheet1).Range("A" & st & ":A" & en - 1), PlotBy:= _
        xlColumns
    ActiveChart.SeriesCollection(1).XValues = "=" & sheet1 & "!R" & st & "C2:R" & en & "C2"
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=chartname
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Sheets(sheet1).Range("A" & st - 6).Value & " Volume: " & Sheets(sheet1).Range("e" & st - 6).Value
       
        .Axes(xlCategory, xlPrimary).HasTitle = True
        
        If InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "lengths", vbTextCompare) Then
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Bytes"
        ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "LBNs", vbTextCompare) Then
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "LBN"
        ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "latency", vbTextCompare) Then
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "uSec"
        ElseIf InStr(1, Sheets(sheet1).Range("A" & st - 6).Value, "outstanding", vbTextCompare) Then
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "# of IOs"
        End If
        
        
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Freq"
    End With
    ActiveChart.Legend.Select
    Selection.Delete


End Sub