我们可以使用VBScript过滤值并将它们放入数组吗? [英] Can we filter the values and put them into an array using VBScript?

问题描述

请考虑以下Excel工作表:

Consider below Excel Sheet:

  Emp Id    Job Status
    741         P
    788         T
    111         T
    124         P
    136         P

我想要一个高级的VBScript代码，它可以一次完成这样的筛选，就像我们在Excel中手动进行的方式一样.过滤后，我只希望将第一列过滤后的值放入数组中.

I want an advanced VBScript Code which Can do such filtering in one shot,the way we did manually in Excel. After the filtering I want first column filtered values only to put into an Array.

代码

Dim IntRow1,Arr,Index
Index=0

Do while objSheet1.Cells(IntRow1,1) <> ""
    If objSheet1.Cells(IntRow1,2) = "P" Then
        Arr(Index)=objSheet1.Cells(IntRow1,1)
        Index=Index+1
    End If
IntRow1=IntRow1=1
Loop

* 更新

Option Explicit

Dim Dic, DicItems, Dickeys
Dim objExcel1
Dim strPathExcel1
Dim objSheet1, lastRow

Set objExcel1 = CreateObject("Excel.Application")

strPathExcel1 = "D:\VA\CopyofGEWingtoWing_latest_dump_21112012.xls"
objExcel1.Workbooks.Open strPathExcel1
Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)

Set Dic = CreateObject("Scripting.Dictionary")

lastRow = 1
Do Until lastRow > objExcel1.Application.WorksheetFunction.CountA(objSheet1.Columns(1))
    Dic.Add objSheet1.Cells(lastRow, 1).Value, objSheet1.Cells(lastRow, 2).Value
    lastRow = lastRow + 1
Loop

DicItems = Dic.Items
Dickeys = Dic.Keys

For lastRow = 0 To Dic.Count - 1
    If DicItems(lastRow) = "P" Then
        Dic.Remove (Dickeys(lastRow))
    End If
Next

DicItems = Dic.Items
'=======================
objExcel1.ActiveWorkbook.SaveAs strPathExcel1
objExcel1.Workbooks.Close
objExcel1.Application.Quit
'======================

推荐答案

请参阅我的答案:

显式选项

 Dim Dic,DicItems,Dickeys
 Dim objExcel1
 Dim strPathExcel1
 Dim objSheet1,lastRow

 Set objExcel1 = CreateObject("Excel.Application")

 strPathExcel1 = "D:\VA\CopyofGEWingtoWing_latest_dump_21112012.xls"
 objExcel1.Workbooks.open strPathExcel1
 Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)

 Set Dic = CreateObject("Scripting.Dictionary")

 lastRow=1
  Do Untill lastRow >         objExcel1.Application.WorksheetFunction.CountA(objSheet1.Columns(1))

  Dic.Add objSheet1.Cells(lastRow,1).Value,objSheet1.Cells(lastRow,2).Value

 lastRow=lastRow+1
 Loop

DicItems=Dic.Items
Dickeys=Dic.Keys

 For lastRow=0 to Dic.Count -1

 If DicItems(lastRow)="P" Then

    Dic.Remove(Dickeys(lastRow))

 End If

  Next

 DicItems=Dic.Items

 '=======================
  objExcel1.ActiveWorkbook.SaveAs strPathExcel1
  objExcel1.Workbooks.close
  objExcel1.Application.Quit
 '======================

这篇关于我们可以使用VBScript过滤值并将它们放入数组吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！