Skip to main content

Export Object Repository to Excel





Script to export Object Repository to Excel

 '############################################
'Description  : Exporting Object Repository to Excel
'What Concepts used : Object Repository Model, Excel Object Model
'Required Parameters: Object Repository, Excel Path (Creates Excel File)
'Required Software : QTP/UFT Installed
'Developed By  : Vivek Kumar
'############################################

 'Declare Variables
'************************************
Dim srcRepository
Dim ParentObject
Dim excel, workbooks, rIndex, objRange
Dim ObjectRepositoryPath,orExcelPath
'************************************

 'Specify file Paths
'************************************
'OR must be created before executing this
'You can use .TSR, .XML and .BDB Object Repositories
'An excel file will be created automatically with the name excel.xls
ObjectRepositoryPath="C:\Users\sudhakar7\Desktop\PracticeScripts\OR\createrequest.tsr"
orExcelPath="D:\excel.xls"
'************************************

 'Load Object Repository
'************************************
'Creating Object Repository  utility Object
Set srcRepository = CreateObject("Mercury.ObjectRepositoryUtil")

 'Load Object Repository
srcRepository.Load ObjectRepositoryPath
'************************************

 'Create Excel file with columns, colors and Autofit
'************************************
'Create Excel Object  
Set excel=createobject("excel.application")   

 'Add New Workbook  
Set workbooks=excel.Workbooks.Add()

 excel.Cells(1,1).value="ParentObjectLogicalName"
excel.Cells(1,2).value="ParentObjectProperties"
excel.Cells(1,3).value="ObjectLogicalName"
excel.Cells(1,4).value="ObjectProperties"

 Set objRange = Excel.Range("A1:D1")

 objRange.Font.Bold = True
objRange.Font.ColorIndex = 1
objRange.Interior.ColorIndex=15

 ' rIndex variable used to enter values row by row
rIndex=2 
'************************************

 'fnExportORtoExcel will get objects and properties from OR
'************************************
'Calling a fnExportORtoExcel Function
fnExportORtoExcel ParentObject
'************************************

 'Auto Fit all other columns & save excel
'************************************
'Autofit all columns
excel.Worksheets("Sheet1").UsedRange.Columns.AutoFit

 With excel.Worksheets("Sheet1").UsedRange.Borders
        .LineStyle = 1
        .Color = vbblack
        .Weight = 2
End With
    
'Save Work Book  
workbooks.saveas orExcelPath,true 

 'Close Work Book  
workbooks.Close  
  
'Quit from Excel Application  
excel.Quit  
'************************************

 'Release Variables  
'************************************
Set workbooks=Nothing  
Set excel=Nothing  
Set srcRepository=Nothing
'************************************
msgbox "Exporting Completed. Open "&orExcelPath&" to view the Excel Object Repository"

 '************************************

 Function fnExportORtoExcel(ParentObject)

 'Get Objects by parent From loaded Repository
'If parent not specified all objects in OR will be captured

 Set fTOCollection = srcRepository.GetChildren(ParentObject)

     For RepObjIndex = 0 To fTOCollection.Count - 1

         'Get object by index
        Set fTestObject = fTOCollection.Item(RepObjIndex)

   Props=""
  
            'Check whether the object is having child objects
            If srcRepository.GetChildren (fTestObject).count<>0 then
                    'Get TO Properties List
                    Set PropertiesColl=fTestObject.GetTOProperties
       
                    For pIndex=0 to PropertiesColl.count-1
           'Store properties in a variable with a comma delimiter
                                Set ObjectProperty=PropertiesColl.Item(pIndex)
                                Props=Props&","&ObjectProperty.name&":="&ObjectProperty.value
                                
                    Next
    
    If InStr(1,Props,",")=1 Then
     Props=Mid(Props,2)
    End If
    
    
                'Write Logical name and Properties in Excel Sheet
    excel.Cells(rIndex,1).value=srcRepository.GetLogicalName(fTestObject)
    excel.Cells(rIndex,2).value=Props
    
    'Color the cells based class name
    If InStr(LCase(Props),"micclass:=browser")<>0 Then
     Set objRange = Excel.Range("A"&rIndex&":B"&rIndex)     
     objRange.Font.Bold = True
     objRange.Font.ColorIndex = 1
     objRange.Interior.ColorIndex=36 
     
    ElseIf InStr(LCase(Props),"micclass:=page")<>0 Then
     Set objRange = Excel.Range("A"&rIndex&":B"&rIndex)     
     objRange.Font.Bold = True
     objRange.Font.ColorIndex = 1
     objRange.Interior.ColorIndex=35

     ElseIf InStr(LCase(Props),"micclass:=frame")<>0 Then
     Set objRange = Excel.Range("A"&rIndex&":B"&rIndex)     
     objRange.Font.Bold = True
     objRange.Font.ColorIndex = 1
     objRange.Interior.ColorIndex=40
    End If    

     'increase rIndex to enter data in next lines of excel
    rIndex=rIndex+1
    
    'Calling Recursive Function
                fnExportORtoExcel fTestObject

             else
              'This else block will execute when the object is not having any childs

      'Get TO Properties List
                    Set PropertiesColl=fTestObject.GetTOProperties
                    For pIndex=0 to PropertiesColl.count-1
                       'Store properties in a variable with a comma delimiter
                                Set ObjectProperty=PropertiesColl.item(pIndex)
                                Props=Props&","&ObjectProperty.name&":="&ObjectProperty.value
                    Next
                
    If InStr(1,Props,",")=1 Then
     Props=Mid(Props,2)
    End If
    
                'Write Logical name and Properties in Excel Sheet
    excel.Cells(rIndex,3).value=srcRepository.GetLogicalName(fTestObject)
    excel.Cells(rIndex,4).value=Props
    
    'increase rIndex to enter data in next lines of excel
    rIndex=rIndex+1
            End if
    Next

 End Function
'************************************
Labels:

Comments

Post a Comment

Popular posts from this blog

SSO with SAML login scenario in JMeter

SAML(Security Assertion Markup Language) is increasingly being used to perform single sign-on(SSO) operations. As WikiPedia puts it, SAML is an XML-based open standard data format for exchanging authentication and authorization data between parties, in particular, between an identity provider and a service provider. With the rise in use of SAML in web applications, we may need to handle this in JMeter. This step-by-step tutorial shows SAML JMeter scenario to perform login operation. First request from JMeter is a GET request to fetch Login page. We need to fetch two values ‘SAMLRequest’ and ‘RelayState’ from the Login page response data. We can do this by using  Regular Expression Extractor . These two values need to be sent in POST request to service provider. Refer below image to see how to do this. We will get an HTML login page as a response to the request sent in 1st step. We need to fetch values of some hidden elements to pass it in the next request. We can do this b
Post a Comment
Read more

Reading data from text file in SoapUI using groovy script

How to read data from text file in SoapUI. SoapUI Pro has some advance feature which is not in SaopUI as data fetching from external sources so in SoapUI we use Groovy script for that. Following are the peace of groovy script code for reading data from text file. 1. Reading all data from text file. //reading all txt file at once File file = new File("E://TestData.txt") fileContent = file.getText()                  log.info fileContent 2. Reading data line by line from text file. //reading text line by line File file1 = new File(" E://TestData.txt ") List textLine = file1.readLines() log.info textLine 3. Reading data randomly of any line from text file. //reading text randon line number File file2 = new File(" E://TestData.txt ") List textLine2 = file2.readLines() rowIndex  =  Math.abs(new Random().nextInt() % 4 + 1) log.info textLine2[rowIndex]
Post a Comment
Read more

VBScript Code - Function to convert CSV file into excel and viceversa in QTP using VBScript

We at times are required to convert excel files into csv to read as flat files and sometime require to convert a csv file into excel file to use excel features on the data.   Below function shows how to convert an csv file into excel file and vice versa. We can also convert to other formats based on constants Here constant value 23 is used to create a csv file and constant -4143 to save a file as xls file. Once the destination file is created, we can delete the source file as shown below.  In case of any issue in understanding the code, please add in comment section Call func_ConversionCSVExcel("E:\Test.csv", "E:\Test_converted.xls", "csvtoexcel") Public Function func_ConversionCSVExcel(strSrcFile, strDestFile, Conversion) on error resume next Set objExcel = CreateObject("Excel.application") set objExcelBook = objExcel.Workbooks.Open(strSrcFile) objExcel.application.visible=false objExcel.application.displayalerts=
Post a Comment
Read more