One of the tools most overlooked by desktop scripters (WSH, HTA, etc.) is ActiveX Data Objects (ADO). Not only does this provide you with things like database access and manipulation facilities, it also offers things like:
[ul][li]Binary I/O (sorry FSO, I know you mean well...),[/li]
[li]Data access to CSV or fixed-field text files,[/li][/ul]
... and loads of other good stuff.
In this case we're looking for a way to sort a list of information. ADO offers a handy feature called the
disconnected recordset. While this seems to imply that the recordset was once connected to something, this doesn't have to be true at all. By using a recordset we get access to some powerful ADO capabilities including filtering and
sorting!
Here is a brief WSH example. It is a WSF rather than a VBS file. This gives us a number of additional capabilities, but here I use two:
[ul][li]Declarative object definition - I don't need to call CreateObject( ) and I don't need to set the object reference to Nothing to clean it up. There are other subtle advantages as well.[/li]
[li]Declarative reference definition - I can declare a reference to a type library and get access to things like the built-in constants.[/li][/ul]
Ok, enough of that. Here we go:
[color blue]ADO Sort.wsf[/color][color brown]
Code:
<job>
<object id = oRS progid = "ADODB.Recordset"/>
<reference object = "ADODB.Recordset"/>
<script language = "VBScript">
Option Explicit
Sub DefineAndOpenRS()
'Define and open the disconnected recordset
With oRS
.ActiveConnection = Nothing
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
With .Fields
.Append "MyStuff", adVarChar, 255
End With
.Open
End With
End Sub
Sub InsertRow(sData)
'Add data to a new row in the recordset
oRs.AddNew
oRS.Fields.Item("MyStuff").Value = sData
End Sub
Function ConcatRows()
With oRS
.MoveFirst
ConcatRows = ""
Do
ConcatRows = ConcatRows _
& .Fields.Item("MyStuff").Value _
& vbNewLine
.MoveNext
Loop Until .EOF
End With
End Function
Dim lItem, sResults
DefineAndOpenRS
For lItem = 1 To 5
InsertRow(CStr(Rnd)) 'Just some random data.
Next
oRS.Sort = "MyStuff ASC"
sResults = ConcatRows()
oRS.Close
MsgBox sResults, vbOkOnly
</script>
</job>
[/color]
This example creates a random list of data, sorts it, and displays it via MsgBox. It requires MDAC 2.1 or greater (2.7 SP 1 is the current release as I write this). I tested it with MDAC 2.5 myself.
While I created a recordset with only one field, I could easily have added several others if I had the need. Note the use of ADO constants without my having to declare them all as Consts.
Here is another example, showing a sort using two fields as sort keys:
[color blue]ADO 2 Col Sort.wsf[/color][color brown]
Code:
<job>
<object id = oRS progid = "ADODB.Recordset"/>
<reference object = "ADODB.Recordset"/>
<script language = "VBScript">
'This script demonstrates the use of an ADO disconnected
'Recordset object to sort data on multiple fields.
'
'Here I make heavy use of the Variant "array of arrays"
'concept to keep this example short. For example lngAryA
'is Variant containing an Array of Arrays rather than a
'2-dimensional array.
Option Explicit
Sub DefineAndOpenRS(strAryCols)
'Define and open the disconnected recordset
Dim strCol
With oRS
.ActiveConnection = Nothing
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
With .Fields
For Each strCol In strAryCols
.Append strCol, adInteger
Next
End With
.Open
End With
End Sub
Dim lngAryA, lngRow, strAryColNames, strResults
lngAryA = Array(Array(1, 7, 0), _
Array(5, 9, 1), _
Array(1, 1, 2), _
Array(2, 0, 3), _
Array(2, 3, 1), _
Array(4, 2, 0), _
Array(3, 0, 0), _
Array(1, 0, 1))
'Define a Recordset.
strAryColNames = Array("Col0", "Col1", "Col2")
DefineAndOpenRS(strAryColNames)
'Load our array into the Recordset.
For lngRow = 0 To UBound(lngAryA)
oRS.AddNew strAryColNames, lngAryA(lngRow)
Next
oRS.Sort = "Col0 ASC, Col2 ASC"
'Show results and finish.
oRS.MoveFirst
strResults = oRS.GetString(, , " ", vbCrLf, "-")
oRS.Close
MsgBox strResults, vbOkOnly
</script>
</job>
[/color]
This example doesn't pull the data back out of the recordset into the array, but it could. Instead it demonstrates the useful GetString( ) method for building the response text.
The best reference I've found for ADO is a CHM file called "ADO Help" in the Microsoft Data Access SDK. The SDK is a free download you can find via MSDN Online:
http://msdn.microsoft.com/dataaccess
Happy scripting.
