Home > Storage, Vmware > New vscsiStats Excel Macro

New vscsiStats Excel Macro

I wrote an Excel Macro to process vscsiStats data and turn it into pretty charts & graphs. I shared that macro with my friend Matt Kelliher and after showing him how to use it, he suggested and made a modification to it. The latest version will still process the data and create charts but then it will also export the charts as PNG, create an HTML file and put thumbnails of the charts in. You can then click on any of the charts for a full-screen view. Handy method of presenting the data in a concise format.

Let me know what you think of the file, you may download a copy here. You are welcome to download and use this macro but please leave the comments at the top (feel free to buy us a beer or two too)

This macro has been tested in Excel 2007 and 2010 beta. I must say though,  it runs much more slowly in these than in 2003.

Note: SAVE your spreadsheet first before running this version of the macro, it uses the current save location as the starting point for creating the HTML and saving the images.

Advertisements
Categories: Storage, Vmware Tags: , , ,
  1. Valentin
    March 21, 2010 at 3:12 pm

    Hi Paul,

    after the first chart is created, “Run-time Error ‘1004’ – Application-Defined or Object-Defined Error” pops up, Debug points at the line: ActiveChart.SeriesCollection(1).XValues = “=” & sheet1 & “!R” & st & “C2:R” & en & “C2”


    Excel-Macros are not something I ever looked into, do you have an idea why this could be happening? Much appreciated, and thanks for the macro, will definitely beat creating every one by hand 🙂

    Cheers

    Valentin

    P.S.
    I have the same issue with all datasets I collected (vscsiStats -p all -w * -c > /*.csv).

  2. March 26, 2010 at 3:19 pm

    I have a couple ideas.. can you email me the csv output file so I can test them on yours?
    Paul

  3. Valentin
    March 30, 2010 at 8:17 pm

    For anyone else reading this, Paul ran my file and it worked as it should, must be something on my box. I guess I will just install a bunch of different Excel Versions on minimal VMs and try which on works for me :-).

  4. Vishy
    March 31, 2010 at 2:13 am

    Had same issue, rename the file as data.csv. it works, looks like excel can’t translate this format name vscsiStats-export.csv

    • Valentin
      April 8, 2010 at 8:25 pm

      Hi Vishy,

      wow, it really is the dash, I even remember renaming it before sending it to Paul … thx for solving the mystery 🙂

      Cheers

      Valentin

  5. Christian
    April 19, 2010 at 10:56 am

    Hi,

    when using two disks and all counters I always get “Run-time error ‘1004’: Method ‘Location’ of object ‘_Chart’ failed” when processing chart 17. The following line is marked when I click debug: ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=chartname.

    When using only one disk with all counters, all charts are generated, but I can’t see the horizontal axis labels.

    Regards

  6. Christian
    April 19, 2010 at 11:11 am

    sry. forget my post. I had to separate the text to columns before running the macro.

    • April 27, 2010 at 12:08 pm

      Kein Problem. Freue mich das es funktioniert hat

  7. Brant
    August 3, 2013 at 4:25 pm

    Great script Paul,

    Excel 2010 generates errors when running the script due to the : character when naming the chart tab and/or the length of the name being longer than 31 characters.

    To fix this after the line:
    ActiveChart.SeriesCollection(1).XValues = “=” & Sheet1 & “!R” & st & “C2:R” & en & “C2”

    I added these two lines:
    chartname = Replace(chartname, “:”, “_”)
    chartname = Left(chartname, 31)

  8. Mouton
    January 3, 2014 at 5:41 pm

    Thanks for your Macro.
    I’ve got somme problems :
    when using many disks and all counters I always get “Run-time error ’1004′: Method ‘Location’ of object ‘_Chart’ failed”
    And I can’t see the horizontal axis labels.

    So I’ll try with only one disk.

    Sincerely yours
    Michel

  9. January 7, 2014 at 4:14 pm

    a few people have written that the script fails on Excel 2010. Honestly, I’ve not run this in a while (vmware stuff at work is running fine hence no real need to)
    but I will run it and check why excel 2010 files.. I’m sure it’s just that the script uses some call that was deprecated in 2010

  1. March 11, 2010 at 9:00 pm
  2. April 8, 2010 at 4:35 pm
  3. October 22, 2010 at 7:48 am
  4. August 5, 2012 at 5:28 pm
  5. July 10, 2013 at 12:52 pm
  6. January 8, 2015 at 8:35 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: