Whilst I'm the mood to share VBScript experiences with the world, I thought I would put up a small piece about how you can use VBScript to get data out of Excel. You may think it's a bit stupid using VBScript to access Excel when you can script directly into Excel with VBA (Visual Basic for Applications) - but I find it can be pretty useful as it requires minimal interaction (double clicking a file instead of opening a spreadsheet and running macros).

To work through this I have tried to construct a reasonable real life scenario where you might need to extract data from Excel - I am no expert in the world of biscuit sales, but I've tried my best:

The data file

The data file used can be XLS, CSV, or anything else that Excel can read into cell values. There are far more complex things you can do with VBScript & Excel than this example, but it provides a good starting point. We're going to be using a standard XLS file with a small amount of data in.

As you can see from the screenshot of the file, the data does not start in the first cell (A1) - instead it starts in A3. We need to bear this in mind when we start to think about parsing out this data - we obviously don't want to include these first two rows.

Basic program structure

Option Explicit
On Error Resume Next

Dim loopCount, directory, objExcel, workbook
Set objExcel = CreateObject("Excel.Application")

'Gets the directory where our script is running from
directory = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)

'Open our XLS file
Set workbook = objExcel.Workbooks.Open(directory & "\product-performance-2006.xls")
'We want to skip the header row, and then the blank row below
loopCount = 3

Do while not isempty(objExcel.Cells(loopCount, 1).Value)
  Dim i       'For looping through the columns on each row
  Dim value   'Value extracted from each cell

  'Spreadsheet is 6 columns across
  For i = 1 To 6
    value = objExcel.Cells(loopCount, i).Value
    'Do something with your value here!
    MsgBox value
  Next
  loopCount = loopCount + 1
Loop

objExcel.Workbooks.Close
objExcel.quit
objExcel = Empty
workbook = Empty

That's quite a bit to take in all at once - I've put comments in the code to help out a bit.

Before running a copy of this script you may want to comment out line 18, or put in something more useful - otherwise you'll be bombarded with alert boxes giving you each cells' value.

Also of note is value = objExcel.Cells(loopCount, i).Value - this is the part of the program which actually pulls a cells value (not formula) from the spreadsheet. In this example, we loop through each row (loopCount) until we've finished with the sheet, and for each row we recurse through the 6 columns (i) and extract the value.

Wrapping up

Slightly related to my VBScript & Oracle article, it's possible to construct SQL queries from looping through each row, and then send them off to a database. I'll hopefully come to this in a later post.

This should have given you a very basic introduction as to how you can start using VBScript to interact with Excel - leave a comment if you need any more clarification or help!