-2

I have a sheet which contains data as below:

enter image description here

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

Community
  • 1
  • 1
Arup Rakshit
  • 116,827
  • 30
  • 260
  • 317
  • 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 Dec 15 '12 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 Dec 15 '12 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 Dec 15 '12 at 17:33
  • i need VBS,,I am trying to write a code for that,couldn't handle all things....i have implemented all such scripts in VBS... – Arup Rakshit Dec 15 '12 at 18:00
  • Did you understand my requirement? otherwise i would explain to you – Arup Rakshit Dec 15 '12 at 18:02
  • Can anyone please look into the same? – Arup Rakshit Dec 16 '12 at 16:06
  • 1
    @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 Dec 16 '12 at 16:45
  • I was looking for any such functionality by which i can check if the each row Started date for the tasks are sorted acending order or not.If not then i can mark it as "Bad" data. But looping is too time consuming process in this case.Thus looking for an alternative one. – Arup Rakshit Dec 16 '12 at 16:56
  • In order to check each row, you actually have to **check each row**, this is done with a **loop**. If you don't understand this then perhaps you would be wise to read an intro to Excel VBA book. – Rachel Hettinger Dec 16 '12 at 18:00
  • I Understand what you are talking about "Sir",:-) But i wanted to lower down the searching of column to coulmn date comparison for each row. – Arup Rakshit Dec 16 '12 at 18:17
  • @KekuSemau If you want then a VBA would be sufficient for me... – Arup Rakshit Dec 16 '12 at 19:48
  • Can anyone help me here? – Arup Rakshit Dec 17 '12 at 04:05
  • May I get some more advanced code than me? please see my updated code. – Arup Rakshit Dec 17 '12 at 05:42
  • 2
    @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. – Zev Spitz Dec 17 '12 at 09:42
  • Please post the data as text, not as an image, so it can be pasted into a workbook. – Zev Spitz Dec 17 '12 at 09:51
  • @ZevSpitz Can you resolve my issue realted to performance,by doing a bit engineering on my code? – Arup Rakshit Dec 17 '12 at 09:52
  • See my answer. It will be far more performant than manually looping. In order to provide you with some code I need some test data; please provide the data as TEXT, not as an IMAGE. – Zev Spitz Dec 17 '12 at 09:57
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21257/discussion-between-vbslover-and-zev-spitz) – Arup Rakshit Dec 17 '12 at 10:09

1 Answers1

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

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.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • 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 Dec 17 '12 at 09:58
  • 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 Dec 17 '12 at 10:01
  • 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 Dec 17 '12 at 17:30
  • @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 Dec 20 '12 at 15:45
  • @Zev I am getting an error `"Could not find installable ISAM"` – Arup Rakshit Dec 20 '12 at 15:51
  • @VBSlover It will run over all the rows; it's treating the Excel sheet as a database table. – Zev Spitz Dec 20 '12 at 17:08
  • @VBSlover re: `Could not find installable ISAM` - Try putting the `Data Source` value inside double-quotes, as well as the `Extended Properties` value. I'll correct in the post. – Zev Spitz Dec 20 '12 at 17:12
  • @VBSlover re: `Could not find installable ISAM` I suggest you post this as a separate question, with the exact code you're using. Also note whether you're running 32-bit or 64-bit Windows and Office, and if your file is a Excel 2007+ file (with an extension of `xlsx`,`xlsm','xlsb', or and extension of `xls`). – Zev Spitz Dec 20 '12 at 17:29
  • @ZevSpitz I am using Office 2010. – Arup Rakshit Dec 20 '12 at 18:05
  • @ZevSpitz now I am getting an error **"No value given for one or more required parameters"** `Set rs = cmd.Execute` line. – Arup Rakshit Dec 21 '12 at 14:04
  • I'm sorry for the delay. This means that one of the columns in the SELECT statement doesn't exist in the spreadsheet. Has this been resolved in the meantime? – Zev Spitz Dec 22 '12 at 20:21