excel - Bad data selection using VBScript , on date field values

Question

I have a sheet which contains data as below:

Now the Excel is having 36 total tasks,Each tasks has 4 columns with in it. first task .i.e Task1 name will always be started from the L column. 36 tasks has been described 144 columns. Now we need to go through row-wise and need to check if TNStart Start date < T(N+1) Start date.then that row would be selected as bad row. In brief when the task# number will be increased from 1 to 36,the start date should be respective needs to be in increasing order.If that fails anytime,row should be marked as bad data.

can you guys help me here to do this in good fashionable way?

Option Explicit

Dim objExcel1
Dim strPathExcel1
Dim objSheet1,objSheet2
Dim IntRow1,IntRow2
Dim ColStart

Set objExcel1 = CreateObject("Excel.Application")'Object for Condition Dump

strPathExcel1 = "D:AravoVBCopy of Original   ScriptsCopyofGEWingtoWing_latest_dump_21112012.xls"
objExcel1.Workbooks.Open strPathExcel1
Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)
Set objSheet2 = objExcel1.ActiveWorkbook.Worksheets("Bad Data")

objExcel1.ScreenUpdating = False
objExcel1.Calculation = -4135  'xlCalculationManual

IntRow2=2
IntRow1=4
Do Until IntRow1 > objSheet1.UsedRange.Rows.Count
    ColStart = objExcel1.Application.WorksheetFunction.Match("Parent Business Process ID", objSheet1.Rows(3), 0) + 1 
    Do Until ColStart > objSheet1.UsedRange.Columns.Count And objSheet1.Cells(IntRow1,ColStart) = ""
        If objSheet1.Cells(IntRow1,ColStart + 1) > objSheet1.Cells(IntRow1,ColStart + 5) and objsheet1.cells(IntRow,ColStart + 5) <> "" Then
            objSheet1.Range(objSheet1.Cells(IntRow1,1),objSheet1.Cells(IntRow1,objSheet1.UsedRange.Columns.Count)).Copy
            objSheet2.Range(objSheet2.Cells(IntRow2,1),objSheet2.Cells(IntRow2,objSheet1.UsedRange.Columns.Count)).PasteSpecial
            IntRow2=IntRow2+1
            Exit Do
        End If
        ColStart=ColStart+4
    Loop

    IntRow1=IntRow1+1
Loop

objExcel1.ScreenUpdating = True
objExcel1.Calculation = -4105   'xlCalculationAutomatic

Bad Performance

My sheet has 2000 rows and the bad data selection criterion is going on 144 columns.Now the output is coming after 25 mins.So it increasing the overall performance.Thus I am requesting you people to help me by making it more faster one.

Is it possible also when it is coping bad row to another sheet,also marked the bad columns in RED

Answer 1

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:pathoexcelfile.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:pathoexcelfile.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:pathoexcelfile.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.