Quick Learn Automation

'###################################################### '#  FunctionName : getQCParameters '# Purpose: To get  the QC Parameters from Test cases using QTP '# Created By :  Jay '###################################################### Function getQCParameters(strNodeByPath)     On Error Resume Next     'strNodeByPath =  "Subject\02 - System Test\TestCases\Reports\Client Reports"     Set TDC = QCUtil.QCConnection     set TSfact = TDC.TestSetFactory     Set TreeMgr = TDC.TreeManager     Set TestTree = TreeMgr.NodeByPath(strNodeByPath)     Set TestFactory = TestTree.TestFactory     Set TestList = TestFactory.NewList("")     Dim NodesList()     ReDim Preserve NodesList(0)     NodesList(0) = TestTree.Path                              Dim Row, Node, TestCase     irow = 1     For Each Node In NodesList            Set TestTree = TreeMgr.NodeByPath(Node)         Set TestFactory = TestTree.TestFactory         Set TestList = TestFactory.NewList(""

Column headings         By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes . If you do not have column headings, you need to specify HDR=No ; the provider names your fields F1, F2, etc. Because the Extended Properties string now contains multiple values, it must be enclosed in double quotes itself, plus an additional pair of double quotes to tell Visual Basic to treat the first set of quotes as literal values NOTE : Special characteristics can’t be accessed using ADODB Connection with Excel. deleting Excel data       You are more restricted in deleting Excel data than data from a relational data

Your Excel data may be contained in your workbook in one of the following: An entire worksheet. A named range of cells on a worksheet. An unnamed range of cells on a worksheet. Specify a Worksheet To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example: strQuery = "SELECT * FROM [Sheet1$]" You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example: strQuery = "SELECT * FROM `Sheet1$`" Microsoft prefers the square brackets, which are the standing convention for problematic database object names. If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message: ... the Jet database engine could not find the specified object If you use the dollar sign but omit the brackets, you will see

 The ConvertToLetter function works by using the following algorithm: Divide the column number by 27, and then put the resulting integer in the variable "i". Subtract the column number from "i" multiplied by 26, and then put the result in the variable "j". Convert the integer values into their corresponding alphabetical characters, "i" and "j" will range from 0 to 26 respectively. For example: The column number is 30. The column number is divided by 27: 30 / 27 = 1.1111, rounded down by the Int function to "1". i = 1 Next Column number - (i * 26) = 30 -(1 * 26) = 30 - 26 = 4. j = 4 Convert the values to alphabetical characters separately, i = 1 = "A" j = 4 = "D" Combined together, they form the column designator "AD". The following VBA function is just one way to convert column number values into their equivalent alphabetical charac

Function Comparedata(strFileName1,strFileName2,ColumnName)       Dim iShtCnt1,iShtCnt2,strSheetName,strSQLStatement1,iCurRow,Query,iColCnt,iCol,curColName,curColValue,CurQuery    Dim bFound,j,strSQLStatement2,RecSet2,iRecordCnt     Set oBook1 = oExcel.Workbooks.Open(strFileName1)     Set oBook2 = oExcel.Workbooks.Open(strFileName2)     iShtCnt1 = oBook1.Sheets.Count     iShtCnt2 = oBook2.Sheets.Count     If iShtCnt1 <> iShtCnt2 Then Reporter.ReportEvent micFail,"Compare Report - Sheets Count Mismatch":Exit Function     oBook1.Close     oBook2.Close     oExcel.Quit     Set oExcel = Nothing     Set objCon2 = ConnectToExcel(strFileName2)     For i = 1 to iShtCnt1         strSheetName = allSheetNames(i-1)         strSQLStatement1 = "" & "SELECT * FROM [" & Left(strSheetName,31) & "$]  WHERE ["  & ColumnName & "] IS NOT NULL AND ["  & ColumnName & "] <> ' '"         Set RecSet1 = GetC