Thursday, July 10, 2008
Enhancing Visual Basic scripts with Microsoft Excel - Part 1
“Things alter for the worse spontaneously, if they be not altered for the better designedly.” -Francis Bacon
Effectively working with and administering a Microsoft Windows based enterprise environment, let alone any other, requires making full use of all the tools that are made available to you and more often than not, making do with whatever is freely available. In the open source world of Linux this is generally not a problem as just about anything you could come up with a need for has not only already been created but is freely available and the things that aren’t, you can create yourself using any of the many free tools for scripting and programming that are available to fill those voids.
When it comes to system automation in a closed source environment such as Microsoft’s, the Visual Basic Scripting host is an invaluable tool that will let you handle just about every administrative task possible from mass updates to Active Directory to extensive data gathering and reporting; best of all, it is not only free, but it is already integrated into every Windows desktop. Of course, when it comes to automation you’re generally dealing with decently large amounts of data, either coming in, going out, or both; and while you can use simple text files for many things, it often makes more sense to leverage the power of a spreadsheet for both input and reported data.
I’m not looking to write the definitive article on how you can best use Excel with VBS, but my goal is to provide a good starting point, with a decent selection of sample source code to get the beginner or intermediate VBscripter up to speed and hopefully, leveraging Excel spreadsheets in no time, so let’s get started.
Lesson 1: Getting started
Let’s create a real simple script that simply shows us Excel.
DIM objExcel
DIM objTestSheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.WorkBooks.Add
objExcel.ActiveWorkbook.Worksheets.Add
Set objTestSheet = objExcel.ActiveWorkbook.Worksheets(1)
objTestSheet.Name = “Test Sheet”
Well that was easy, not much to it is there? Well go ahead and save the script and run it and you should see results like those below.
Ok let’s now take a look at the script and see what is going on.
After 1) declaring variables we 4) create the Excel object and bind it to objExcel, calling the application forward. We then 5) set Excel to be visible and 6-7) add a workbook and a worksheet inside of that. Once we’ve got the basic spreadsheet created, we then 9) bind an object to the first worksheet and we 10) give it a name. Pretty simple, but that doesn’t really do much for us does it? So let’s see what we need to do to actually put data inside that spreadsheet.
To “setup” the sheet to the spec we want, we’d like to do things like define column width, setup some header rows and maybe even freeze panes and add an autofilter, let’s see what we can do.
DIM objExcel
DIM objTestSheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.WorkBooks.Add
objExcel.ActiveWorkbook.Worksheets.Add
Set objTestSheet = objExcel.ActiveWorkbook.Worksheets(1)
objTestSheet.Name = “Test Sheet”
For i = 1 to 6
objTestSheet.Cells(1, i).Font.Bold = TRUE
Next
objTestSheet.columns(1).columnWidth = 10
objTestSheet.columns(2).columnWidth = 20
objTestSheet.columns(3).columnWidth = 20
objTestSheet.columns(4).columnWidth = 6
objTestSheet.columns(5).columnWidth = 30
objTestSheet.columns(6).columnWidth = 15
objTestSheet.Cells(1, 1).Value = "Status"
objTestSheet.Cells(1, 2).Value = "Test"
objTestSheet.Cells(1, 3).Value = "Department"
objTestSheet.Cells(1, 4).Value = "Floor"
objTestSheet.Cells(1, 5).Value = "Room/Area"
objTestSheet.Cells(1, 6).Value = "Room #"
objTestSheet.activate
objTestSheet.Rows.item(2).select
objExcel.ActiveWindow.FreezePanes = True
And here’s the results we’ll see if we run that script.
Pretty cool, we can see that we’ve got the row headers in place and bolded, the sheet is labeled and we’ve even applied a freeze pane affecting the 2nd row and down. Let’s take a look at the new piece of code we’ve added and see how it works.
Though you don’t have to, I used a short little for loop to 12-14) set the first 6 columns to be font.bold. In 16-21) we’re setting the columnwidth to varying sizes based on the type of information we will hold and in 23-28) we are setting the value of the cells to be equal to what we want as column headings. As you can see, assigning data to a cell is pretty easy, you simply specify the object variable you used to bind to the spreadsheet in this case objTestSheet, with .cells(x,y) at the end where x and y are the row and column followed by .value = “xxx” to set that data. In this case we’re obviously setting it manually, but from a scripting point of view, the resultant data from your scriptwork will simply be put into these areas. On 30) we are activating the sheet we were writing data to and then 31) we are selecting row two so that we can 33) FreezePanes on everything below that.
Two other neat little commands I didn’t use yet but that I’ll throw in for you to tinker with until the next lesson are:
a) objTestSheet.Cells(1, 1).Font.ColorIndex = 3
b) objTestSheet.Cells.autofilter 1, "DOWN"
Where a) sets the color of the text in the cell(1,1) which is equal to A:1 to red and b) creates an autofilter, specifically setting it to row 1 and filtering for “DOWN”. You won’t be able to use b) if you don’t have any data to filter, so keep that in mind while you’re playing with it. If that’s not entirely clear don’t worry, as I will cover it in detail next time.
Anyway this gives us a good starting point to begin working with Excel output from VBS direct to the screen. Is this the best way to have your script output data to Excel? Well, not really, but I guess that depends on whether or not you want to watch the results as they happen. There are many ways to use Excel with VBS as we’ll see in the next couple of lessons, but this should get you headed in the right direction. In later tutorials I will cover Excel spreadsheets as input, writing data to spreadsheets entirely in the background and more. For now, play around with Excel as an output medium, writing your results to cells in your loops instead of writing to text files. What’s cool about vbscript’s manipulation of Excel is that not only can you output direct to a spreadsheet, visibly on the screen or entirely silently in the background, but that you can also format that data so completely that there is nothing left to do other than to interpret the results. (Though I’m working on a script for that too btw, lol)
Incidentally for your automated scripts that you’d like to have write to excel spreadsheets in the background, don’t forget you can have vbs send you an email with the results, attaching the spreadsheet to the message, of course, you could also write it to only email you if there is a problem, etc. but that’s a topic for a separate tutorial altogether.
Labels: DIY, programming
PermaLink / Posted by: Tony
