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 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.
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.
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!