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:
Download VBScript & Excel Spreadsheet (XLS in ZIP)
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
Download VBScript & Excel VBS
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!
And here’s me thinking your VB was a little rusty :p
I’m loving the twist on the blogs recently, Kester. Keep them coming. Might start some HTML exercises on mine, just for jokes
I’m trying to be edumacational.
No, but really… I struggled to consolidate the information around, thought this could be handy : )
mmm.. really nice.. as i never did any scriptong b4..
after reading both the excel and oracle part…
can u make any example on how to read oracle and dump it into excel?
What if I want to transfer data but there are empty rows within the data that I want to ignore? How do I do that?
Thanks!
Hi. I’m foregin to VB Script and have some questions which i need all of ur help. How do I search for excel file with a given file name using VB Script?
Love it. I am so addicted to vbscript and excel that I wrote a book on it to help other people learn the easy way.
http://www.lulu.com/content/1578135
Also I actively keep a blog with loads of examples now:
http://nerds-central.blogspot.com/2008/01/excel-vbscript-index-page.html
This is great! Would you know how to match this Excel sheet with another Excel sheet that would, for example, have the Product Code and Price. And/or if I had another Excel file with the Product Code and new Product Description, can I match by Product Code and change the Description?