Bad data selection using VBScript , on date field values

by

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

ADODB – ActiveX Data Objects

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.

More Related Contents:

  1. Run Excel Macro from Outside Excel Using VBScript From Command Line
  2. How to set recurring schedule for xlsm file using Windows Task Scheduler
  3. Export each sheet to a separate csv file
  4. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  5. SumIf using Excel VBA
  6. How can I call a macro which contains a loop but only run one iteration of that loop?
  7. Test or check if sheet exists
  8. Copy from one workbook and paste into another
  9. How to copy Outlook mail message into excel using VBA or Macros
  10. For Loop not fully cycling in excel VBA [duplicate]
  11. Unique Random Numbers using VBA
  12. Populate unique values into a VBA array from Excel
  13. Excel VBA App stops spontaneously with message “Code execution has been halted”
  14. How to remove all non alphanumeric characters from a string except period and space in excel?
  15. Closing Excel Application using VBA
  16. How to clear memory to prevent “out of memory error” in excel vba?
  17. Assign on-click VBA function to a dynamically created button on Excel Userform
  18. What are the benefits of setting objects to “Nothing”
  19. Using a wildcard to open an excel workbook
  20. How can I kill task manager processes through VBA code?
  21. VBA: Usage of parentheses for a method
  22. Fully reference a worksheet by codename
  23. Extract Data from Word Document to an Excel SpreadSheet
  24. VBA equivalent to Excel’s mod function
  25. Pointers needed for speeding up nested loop macro in VBA
  26. ‘1004’: “The sort reference is not valid.”
  27. Select entire column in table using Excel VBA
  28. Create hyperlink to another sheet
  29. Reducing WithEvent declarations and subs with VBA and ActiveX
  30. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts

Leave a Comment