Script Tips
Working with ADO
Use scripts to query database and spreadsheet data -- just make the right call.
Jeffery Hicks and I recently released a new book, through Microsoft Press, called
Advanced VBScript for Windows Administrators, so I thought I’d share a tip from that book. One of the things we focus on is database scripting, using ActiveX Data Objects. Did you know that Excel spreadsheets can be accessed as a database? Doing so is a lot easier than trying to automate Excel through the Excel.Application object. Here's a script that queries everything in Sheet1 of an Excel spreadsheet and displays the contents of the first column:
Dim objCN, strConnection
Set objCN = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyExcel.xls;Extended Properties=" & _
"""Excel 8.0;HDR=No;IMEX=1"""
objCN.Open strConnection
Dim strSQLQuery
strSQLQuery = "SELECT * FROM [Sheet1$]"
Dim objRS
Set objRS = objCN.Execute(strSQLQuery)
Do Until objRS.EOF
WScript.Echo objRS(0)
objRS.MoveNext
Loop
objRS.Close
End Sub
Notice the Excel filename in the connection string, and note that, because of the way I've written this, you may need to have Microsoft Access installed in order for it to work right. This assumes that the first row of the Excel sheet doesn’t contain column names; change "Hdr=No" to "Hdr=Yes" if that's the case. Finally, note how the Excel sheet name is listed: In square brackets, with a dollar sign after the sheet name.
Learning to work with ADO can take a bit of time, but it's a technology that'll pay huge returns on your investment. A couple of minor changes to the above code would make it work with SQL Server, Access, or nearly any other type of database you have a driver for, so it’s a skill that you’ll be able to use in a variety of situations.
About the Author
Don Jones is a multiple-year recipient of Microsoft’s MVP Award, and is Curriculum Director for IT Pro Content for video training company Pluralsight. Don is also a co-founder and President of PowerShell.org, a community dedicated to Microsoft’s Windows PowerShell technology. Don has more than two decades of experience in the IT industry, and specializes in the Microsoft business technology platform. He’s the author of more than 50 technology books, an accomplished IT journalist, and a sought-after speaker and instructor at conferences worldwide. Reach Don on Twitter at @concentratedDon, or on Facebook at Facebook.com/ConcentratedDon.