使用 VBScript 在日期字段值上选择错误的数据 [英] Bad data selection using VBScript , on date field values

问题描述

我有一张包含以下数据的工作表:

I have a sheet which contains data as below:

现在 Excel 共有 36 个任务，每个任务有 4 列.第一个任务 .i.e Task1 名称将始终从 L 列开始.36 个任务已被描述为 144 列.现在我们需要逐行进行检查，并需要检查 TNStart Start date <;T(N+1) 开始日期.那么该行将被选为坏行.简而言之，当task#号从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: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

表现不佳

我的工作表有 2000 行，错误数据选择标准是 144 列.现在输出在 25 分钟后到来.所以它提高了整体性能.因此我请求你们帮助我它更快.

是否也可以在将坏行处理到另一张纸时，也将坏列标记为红色

推荐答案

我建议通过 ADODB 连接到 Excel 电子表格，并使用 SQL 检索数据.然后，您可以使用 CopyFromRecordset 方法非常简单地将数据导出到新的 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, 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	oexcelfile.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	oexcelfile.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 oexcelfile.xlsx 和 WorksheetName.

---

更新

Replace C:path oexcelfile.xlsx and WorksheetName with the appropriate values.

---

Updated

一些链接:

VBScript/WSH/脚本运行时

VBScript / WSH / Scripting Runtime

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

• 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 编程 - 因为大部分 ADO 示例都在 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 的语言都可用的对象模型，它们的用法看起来非常相似(参见 此处 中的示例蟒蛇).

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屋！