父母和子女过程隔离和子女上市 [英] Parent and child process segregation and child listing
问题描述
请阅读以下模板：
PID Status LPID
10 Closed 25
11 Open 25
31 Open 31
25 Closed 25
54 Open 31
17 Open 17
20 Closed 31
88 closed 77
77 closed 77
现在当PID！= LPID时，该PID被定义为CPID（子进程ID） ，否则它是一个PPID（父进程ID）
Now when PID!= LPID, that PID is defined as CPID(Child Process ID),Otherwise it is a PPID(Parent process ID)
现在我正在寻找一个代码，它将告诉哪个是父代，哪个是子代 - 在另一个表中标记它们同时我想列出所有CPID，PPID在同一行，如果任何PPID有子进程自己。输出将如下所示：
Now I am looking for a code which will tell which is parent and Which is child- means marking them in another sheet.At the Same time i want to list down all CPID,with PPID in the same row,If any PPID has child Processes themselves. Output would be look like below
PID Type Of Process? Child List
10 Child
11 Child
31 Parent 54 20
25 Parent 10 11
54 Child
17 Parent
20 Child
88 Child
77 Parent 88
我用VBScript编写了一个代码，实际的纸张太慢了对于2500数据，它需要接近1个小时。因此，我想要一个比我更快的过程。
I have written a code using VBScript,but with the actual sheet it is too slow. For 2500 data it is taking close to 1 hour.So I Want a more faster process than my one.
你可以在这里使用VBscript帮助吗？
Could you help here using VBscript?
Code1：
Code1:
Set objExcel1 = CreateObject("Excel.Application")'Object for W2W Report Dump
strPathExcel1 = "D:\VA\CopyofGEWingtoWing_latest_dump_21112012.xls"
objExcel1.Workbooks.open strPathExcel1
Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(2)
Set objSheet2 = objExcel1.ActiveWorkbook.Worksheets(1)
IntRow1=1
Do While objSheet1.Cells(IntRow1, 1).Value <> ""
IntRow2=4
IntChildListColumn=3
If objSheet1.Cells(IntRow1,2).Value="Parent" Then
Do While objSheet2.Cells(IntRow2, 1).Value <> ""
If objSheet2.Cells(IntRow2,11).Value=objSheet1.Cells(IntRow1,1).Value And objSheet2.Cells(IntRow2,11).Value <> objSheet2.Cells(IntRow2,1).Value Then
objSheet1.Cells(IntRow1,IntChildListColumn).Value=objSheet2.Cells(IntRow2,1).Value
IntChildListColumn=IntChildListColumn+1
End If
IntRow2=IntRow2+1
Loop
End If
IntRow1=IntRow1+1
Loop
Code2：
Code2:
Flag=0
IntColTemp=1
IntRowTemp=3
Set objExcel1 = CreateObject("Excel.Application")'Object for Condition Dump
strPathExcel1 = "D:\VA\CopyofGEWingtoWing_latest_dump_21112012.xls"
objExcel1.Workbooks.open strPathExcel1
Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)
Set objSheet2 = objExcel1.ActiveWorkbook.Worksheets(2)
IntRow1=4
IntRow2=1
Do While objSheet1.Cells(IntRow1, 1).Value <> ""
objSheet2.Cells(IntRow2, 1).Value = objSheet1.Cells(IntRow1, 1).Value
IntColTemp=1
Flag=0
'This will travarse to the Parent Business Process ID column horizantally in the excel.
Do While Flag=0
If objSheet1.Cells(IntRowTemp,IntColTemp).Value="Parent Business Process ID" Then
Flag=1
End If
IntColTemp=IntColTemp+1
Loop
IntColTemp=IntColTemp-1
'MsgBox(IntColTemp)
Strcmp1=trim(objSheet1.Cells(IntRow1, 1).Value)
Strcmp2=trim(objSheet1.Cells(IntRow1,IntColTemp).Value)
If Strcmp1=Strcmp2 Then
objSheet2.Cells(IntRow2, 2).Value="Parent"
Else
objSheet2.Cells(IntRow2, 2).Value="child"
End If
IntRow1=IntRow1+1
IntRow2=IntRow2+1
Loop
推荐答案
一些建议的改进：
如果你真的有屏幕打开excel：set Application.ScreenUpdating = False 在你的代码开始（和 True 结束）
In case you really have the screen open the excel: set Application.ScreenUpdating = False at start of your code (and True and the end)
设置t他的应用程序。在代码开始时：
Application.Calculation = xlCalculationManual 并在结束时返回自动： Application.Calculation = xlCalculationAutomatic 。如果这不是自动的，您还可以将原始设置存储到本地变量中，并在最后将其恢复。
Set the Application.Calculation to manual. At start of your code:
Application.Calculation = xlCalculationManual and at the end back to automatic: Application.Calculation = xlCalculationAutomatic. In case this was not automatic per se, you could also first store the original setting into a local variable and restore that value at the end.
使用VBA查看性能在这里更好（如果VBA是您的有效选项）
Use VBA to see if performance is better there (if VBA would be a valid option for you)
使用 Value2 代替值，此属性的查询速度更快。
Use Value2 in stead of Value, this property is faster to consult.
减少循环次数，特别是在寻找范围中的特定值时。
您可以选择使用Excel Lookup&参考功能（如VLookup），可能运行效率更高，速度更快。
Reduce the number of Loops especially when you are looking for specific values in a range. You could alternatively use Excel Lookup & Reference functions (such as VLookup), those might run more efficient and faster.
这篇关于父母和子女过程隔离和子女上市的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
