1、QTP Script 33 - How to compare two excel sheets of the same excel workbook and highlight the cells with different values in the first sheet?Excel object is being created hereSet Exl_Obj = CreateObject(“Excel.Application“)Exl_Obj.Visible = TrueFile sac1 is being openedSet WB_Obj_1= Exl_Obj.Workbooks.
2、Open(“C:sac1.xls“)Cells in the first and second sheet of sac1 are comparedSet WS_Obj_1= WB_Obj_1.Worksheets(1)Set WS_Obj_2= WB_Obj_1.Worksheets(2)For Each cell In WS_Obj_1.UsedRangeIf cell.Value WS_Obj_2.Range(cell.Address).Value Thencell.Interior.ColorIndex = 6Elsecell.Interior.ColorIndex = 0End If
3、NextExl_Obj.workbooks(“sac1.xls“).saveExl_Obj.workbooks(“sac1.xls“).closeExl_Obj.workbooks(“sac2.xls“).saveExl_Obj.workbooks(“sac2.xls“).closeExl_Obj.Application.Quitset Exl_Obj=nothingQTP Script 31 - How to compare range of cells in two excel files and highlight the cells with different values in t
4、he first file?myrange=inputbox(“enter range of cells e.g. A1:A5“)Excel object is being created hereSet Exl_Obj = CreateObject(“Excel.Application“)Exl_Obj.Visible = TrueTwo files sac1 and sac2 are openedSet WB_Obj_1= Exl_Obj.Workbooks.Open(“C:sac1.xls“)Set WB_Obj_2= Exl_Obj.Workbooks.Open(“C:sac2.xls
5、“)Cells in the first sheet of both the files are comparedSet WS_Obj_1= WB_Obj_1.Worksheets(1)Set WS_Obj_2= WB_Obj_2.Worksheets(1)For Each cell In WS_Obj_1.Range(myrange)If cell.Value 0 Thenuu= Replace(File.Path, old_val, new_val)uu1= uu1 &vbcrlf &uuFile.Move Replace(File.Path,old_val,new_val)total_f
6、iles_renamed = total_files_renamed + 1elsetotal_files_skipped = total_files_skipped + 1End IfNextEnd SubQTP Script 29 - How to rename all the files in a folder?Type the below code in a notepad, save it with .vbs extension and run it from command prompt. You have to change the folder name (C:abc) and
7、 the strname to suit your requirements. Below code renames all the files in a given folder to Sac 1, Sac 2and so on.strComputer = “.“Set objWMIService = GetObject (“winmgmts:“ &strComputer &“rootcimv2“)Set colFileList = objWMIService.ExecQuery (“ASSOCIATORS OF Win32_Directory.Name=C:abc Where “ &“Re
8、sultClass = CIM_DataFile“)x=1For Each objFile In colFileListstrname = “Sac “ &xstrNewName = objFile.Drive &objFile.Path &strname &“.“ &objFile.ExtensionerrResult = objFile.Rename(strNewName)x=x+1Nextsource:http:/ know more about Windows Management Instrumentation go here.http:/ Script 28 - How to re
9、name all subfolders within a given folder?Type the below code in a notepad, save it with .vbs extension and run it from command prompt.In case nothing is entered for main_folder or search_string or replace_string, the script will quit.main_folder = inputbox (“Enter the main folder whose subfolders n
10、eed to be renamed“ &vbcrlf &“e.g. C:Folder“)if main_folder=“ then quit_functionsearch_string = inputbox (“Enter the string to be searched“)If search_string = “ then quit_functionreplace_string = inputbox (“Enter the string to be replaced“)If replace_string = “ then quit_functionSet object_FSO= Creat
11、eObject (“Scripting.FileSystemObject“)rename_function main_folderSub rename_function (byval Folder)GetFolder method is used to get the Folder object for the path that you specify. You can then use the new variable containing the folder object to access its various methods and properties.Set object_f
12、older = object_FSO.GetFolder (Folder)SubFolder property returns a Folders collection consisting of all the subfolders in a given folder.Set sub_folders = object_folder.SubfoldersFor each subfolder in sub_foldersnew_foldername = (Replace (subfolder.name, search_string, replace_string)If new_foldernam
13、e Options - Folders tab) through a script.How to add any path in the Search List (Tools - Options - Folders tab) through a script. We are opening a test called “call twra“ and adding a path C:Program FilesMercury InteractiveQuickTest ProfessionalTests in the Search List (Tools - Options - Folders ta
14、b)Dim qtp_appDim str_pathSet qtp_app = CreateObject (“QuickTest.Application“)qtp_app.Launchqtp_app.Visible = Trueqtp_app.Open “C:Program FilesMercury InteractiveQuickTest ProfessionalTestscall twra“, True, False If the folder C:Program FilesMercury InteractiveQuickTest ProfessionalTests is not there
15、 just add it.str_path = qtp_app.Folders.Locate (“C:Program FilesMercury InteractiveQuickTest ProfessionalTests“)If the folder is not found in the collection, add itIf qtp_app.Folders.Find (str_path) = -1 Thenqtp_app.Folders.Add str_path, 1 Add the folder to the collectionEnd IfIf you have moved “ downwards in the list, bring it back to first position.If qtp_app.Folders.Item (2) = “ Thenqtp_app.Folders.MoveToPos 1, 2 End If
