Bad data selection using VBScript , on date field values

I would suggest connecting to the Excel spreadsheet via ADODB, and retrieve the data using SQL. You can then export the data to a new Excel spreadsheet quite simply, using the CopyFromRecordset method.

Option Explicit

Dim conn, cmd, rs
Dim clauses(34), i
Dim xlApp, xlBook

Set conn = CreateObject("ADODB.Connection")
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=""C:\path\to\excel\file.xlsx"";" & _
        "Extended Properties=""Excel 12.0;HDR=Yes"""

    'If you don't have Office 2007 or later, your connection string should look like this:
    '.ConnectionString = "Data Source=""C:\path\to\excel\file.xls"";" & _
    '    "Extended Properties=""Excel 8.0;HDR=Yes"""

    .Open
End With

For i = 0 To 34
    clauses(i) = "[Task" & i + 1 & " Start Date] < [Task" & i + 2 & " Start Date]"
Next

Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "SELECT * FROM [WorksheetName$] WHERE " & Join(clauses, " OR ")
cmd.ActiveConnection = conn
Set rs = cmd.Execute

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
xlBook.Sheets(1).Range("A1").CopyFromRecordset cmd.Execute

Replace C:\path\to\excel\file.xlsx and WorksheetName with the appropriate values.


Updated

Some links:

VBScript / WSH / Scripting Runtime

  • JScript and VBScript, specifically the VBScript, Script Runtime, and Windows Script Host sections. Also check out the Regular Expressions section

ADODB - ActiveX Data Objects

  • How do I Use the Connection Object
  • Microsoft ActiveX Data Objects
  • How To Open ADO Connection and Recordset Objects
  • How to use ADO to read and write data in Excel workbooks
  • VBScript ADO Programming - because most of the ADO samples are in VB6/VBA

Office client development

Many of the samples on MSDN use VBA or VB6. For a short intro to porting VBA/VB6 to VBScript, see here. The primary point to remember is most of these topics (ADODB, Excel, Scripting Runtime) are not VBScript specific; they are object models available to any COM-enabled language, and their usage will look very similar (see here for an example in Python).

Google is your friend, as is StackOverflow.