QTP和excel的一些备用方法 和其他

1. 创建唯一标识符：

function GenerateGuid                dim typeLib : set typeLib = CreateObject("Scriptlet.TypeLib")                GenerateGuid = typeLib.Guid end function

2. 杀掉windows进程

Sub KillExcelProcess                Set objService = getobject("winmgmts:")                For each Process in objService.InstancesOf("Win32_process")                               'print Process.Name & vbTab & Process.processid                               If Instr(1,""&Process.Name, "Excel",1)>0 Then                                              Process.Terminate()                               End If                Next                Set objService = Nothing End Sub

3. excel是否有这个sheet?

Function bCheckSheetExsit(ByVal objWorkbookFunc, ByVal sSheetNameFunc)    Dim SheetExists : SheetExists = False    For Each objWorksheet In objWorkbookFunc.Worksheets                               'print objWorksheet.Name                               If objWorksheet.Name = sSheetNameFunc Then                                              SheetExists = True                                              Exit For                               End If                  Next                  bCheckSheetExsit = SheetExists End Function

4. 从一个range 复制（by value）值到另一个range （两个range 可以使不同excel）

'Function to copy Sheet1-Range to Sheet2-Range Dim FirSheetRange, SecSheetRange Function CopyRangeBetweenSheets (FirSheetRange, SecSheetRange) '              FirSheetRange.Copy  SecSheetRange                FirSheetRange.Select                FirSheetRange.Copy                SecSheetRange.PasteSpecial -4163     ‘by value   End Function

5.用正则表达式去掉特殊字符

Set regEx = New RegExp   ' Create a regular expression.                               regEx.Pattern =  "[\$\.\&\/\+\(\)]"   ' Set pattern.                               regEx.IgnoreCase = True   ' Set case insensitivity.                               regEx.Global = True   ' Set global applicability.                               CellValue = regEx.Replace(CellValue, "")

6.查找字符串，返回字符串所在单元格的行号或列号：

Function iGetRowCmnFromStr (objSheetFunc, sSearchString, bSearchRowFlag)    If IsEmpty(bSearchRowFlag)  Then                   bSearchRowFlag = False    End If                sSearchString = Trim("" & sSearchString)                  Set objFind = objSheetFunc.Range("A1:CC500").Find(sSearchString)       If objFind is Nothing Then                                                 iGetRowCmnFromStr = -1                Reporter.ReportEvent  micFail, "Search text in excel", "The string " & sSearchString &" is not found in the sheet"                Else                                              If  bSearchRowFlag Then                                                             iGetRowCmnFromStr = CInt(objFind.Row)                                                             'print "Row = " & iGetRowCmnFromStr                                                                 Else                                                             iGetRowCmnFromStr = CInt(objFind.Column)                                                             'print "Column= " & iGetRowCmnFromStr                                                                                          End If                End If End Function

7. QC解锁

Set QCConnection=QCUtil.QCConnection Set con=QCConnection.command con.CommandText="DELETE FROM LOCKS WHERE LK_USER ='frank.tan'"  Set recset=con.execute

8. 用坐标比较认可的方式：

Set objCheckBoxElement = Browser("a").Page("a").WebElement("a") intX = objCheckBoxElement.GetROProperty("abs_x") intY = objCheckBoxElement.GetROProperty("abs_y") Set objDeviceReplayClick = CreateObject("mercury.devicereplay") objDeviceReplayClick.MouseClick intX+5, intY+5, 0