使用VBScript，对日期字段值进行不良数据选择 [英] Bad data selection using VBScript , on date field values

问题描述

我有一张表格，其中包含以下数据：

I have a sheet which contains data as below:

现在Excel有36个总任务，每个任务都有4列。第一个任务.i任务1的名称将始终从L列开始。已经描述了136个任务。现在我们需要逐行执行，需要检查TNStart的开始日期< T（N + 1）开始日期，那行将被选为坏行。简单来说，当任务＃号从1增加到36时，开始日期应该分别是按照增加的顺序排列。如果任何时候失败，行应该被标记为不良数据。

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

不良成绩

我的工作表有2000行，错误的数据选择标准正在进行144列。现在输出是在25分钟后进行的。因此，它提高了整体性能。因此，我要求你帮助我更快一个。

也可能是当它将坏行应对另一张表时，也标记了红色的不良列

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

推荐答案

我建议通过ADODB连接到Excel电子表格，并使用SQL检索数据。然后，您可以简单地将数据导出到新的Excel电子表格。

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.

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

替换 C：\path\to\excel\file.xlsx code> WorksheetName 与适当的值。

---

已更新

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

---

Updated

某些链接：

VBScript / WSH / Scripting Runtime

VBScript / WSH / Scripting Runtime

• JScript和VBScript ，特别是 VBScript ，脚本运行时和 Windows Script Host 部分。另请查看正则表达式

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

ADODB - ActiveX数据对象

ADODB - ActiveX Data Objects

• 如何使用连接对象


• Microsoft ActiveX数据对象


• 如何打开ADO连接和记录集对象


• 如何使用ADO在Excel中读取和写入数据工作簿


• VBScript ADO Programming - 因为大部分的A DO示例在VB6 / VBA中

• 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客户端开发

MSDN上的许多示例使用VBA或VB6。有关将VBA / VB6移植到VBScript的简短介绍，请参阅此处。要记住的主要方面是这些主题（ADODB，Excel，脚本运行时）中没有特定的VBScript;它们是可用于任何支持COM的语言的对象模型，并且它们的用法看起来非常相似（请参阅此处，以获取示例Python）。

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 是您的 朋友，和 StackOverflow 一样。

这篇关于使用VBScript，对日期字段值进行不良数据选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！