Bad data selection using VBScript , on date field values

Question

I have a sheet which contains data as below:

enter image description here

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:\AravoVB\Copy of Original   Scripts\CopyofGEWingtoWing_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

If you had shown us from the beginning what your ultimate goal was instead of posting pieces here and there, you would have gotten a solution by now. ;) — bonCodigo
– bonCodigo, Commented Dec 15, 2012 at 16:49
No Dear Bon, these are not the things that i previously set.These are coming when i was doing scripting and calculating things. it comes into my focus when one of my sheet given a dramatic error and i did research on it, and got finally 10 rows out of 3200 rows,having such data.. thus i need to take care of.. hope you would help me :-) — Arup Rakshit
– Arup Rakshit, Commented Dec 15, 2012 at 17:02
I've read it and really don't know what you want. Is this a 'please write code for me' request? How do you need VBS here instead of VBA, in other words, do you use these terms correctly? — KekuSemau
– KekuSemau, Commented Dec 15, 2012 at 17:33
@VBSlover I suggest you start with the code I provided for your last question--it contains the framework you need to loop through the rows and task columns. If you get stuck, post the code you have written with details about what is not working correctly and perhaps you will get assistance here. FWIW, manipulating Excel worksheets is usually done with Excel macros, not VBScript. It is hard to imagine what you need to do that Excel VBA "couldn't handle". — Rachel Hettinger
– Rachel Hettinger, Commented Dec 16, 2012 at 16:45
@RachelHettinger Virtually anything that can be done with VBA can also be done with VBScript. If the code should not be associated with any particular workbook, it's understandable why VBScript should be used. VBScript also allows running from the Windows shell, as opposed to first opening Excel in order to run the script. — user20416
– user20416, Commented Dec 17, 2012 at 9:42

user20416 · Accepted Answer · 2018-05-03 05:10:13Z

4

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.

edited May 3, 2018 at 5:10

answered Dec 17, 2012 at 9:50

user20416

15.5k9 gold badges76 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Arup Rakshit Over a year ago

the raw data is present in the first sheet of the Excel, and my above script doing its calculation from their and dumping all the required rows that met the given conditions to another sheet("bad data") of the same excel book.

Arup Rakshit Over a year ago

But each row has larger data, how should i give here,here don't have any data file upload links,I beleieve..Please advice me.

Arup Rakshit Over a year ago

Can you suggest me any good tutorial to eanable myself also to write down such kind of coding? I want to learn such topics "ADODB.Connection" more,as you increased my interest upto sky ..:-)

Arup Rakshit Over a year ago

@Zev Spitz one query here that,My Excel first sheet has 2000 rows,each row has numbers of columns with such 36 task details. So will the above code run through for each rows or only the first row.

Arup Rakshit Over a year ago

@Zev I am getting an error "Could not find installable ISAM"

|

Collectives™ on Stack Overflow

Bad data selection using VBScript , on date field values

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related