Most of the time, the automation suite needs to create result in excel format for easier portability. It’s a general practice that is being followed usually, that an automation suite when being executed on a machine it creates its own result file. The same suite when gets executed on multiple machines with different test sets creates multiple result files separately. which needs to be consolidated at a later point and consumes extra effort.
As we are into automation, its in our blood to have all the things automated and ready with a single click, isn’t it? I generally don’t recommend/want to put efforts in such activities and always try to automate even its a very simple process to do manually. But only if it saves your time 🙂
The main problem here, is to update the excel result file for a script when it tries to open the file while another script is already working on the same file. QTP/UFT/TestComplete or perhaps any other tool does not have any mechanism or built-in method to check whether the file is currently being edited by another test to prevent overwriting and errors. This is the reason, why we cannot use a single result file to be updated from several scripts simultaneously. However this can be achieved using Excel Object Model.
Excel object model has various properties and methods. There is one property as ‘Read Only’ which can be used here and should do the trick. Refer to MSDN – Microsoft Excel Object Model for overview and complete list of Excel Object Model methods and properties. Find more details about Read Only property of ExcelWorkBook here.
WorkBook.ReadOnly : Returns True if the object has been opened as read-only. Read-only Boolean.
To use this property, you will need to create an object for excel application. Using this object, you can try to open the desired excel workbook. Retrieve the readonly property and if its readonly, then close the file. Continue the same process until the file is available to edit.
Checkout the sample code below
The following example will loop until the file is not read-only (opened by another script). Within the loop, the file is opened and then the ReadOnly property is retrieved. If the file is read-only, the file is closed and released. The script will wait for a short time, then attempt to open the file again.
Set oExcel = CreateObject("Excel.Application")
bExcelReadOnly = True 'initialize a variable to check the status
' Loop while the file is read-only
Do While bExcelReadOnly
Set oWorkbook = oExcel.Workbooks.Open("C:\test\test.xlsx")
bExcelReadOnly = oWorkBook.ReadOnly
If bExcelReadOnly Then ' If readonly
' Close the file and release the obect
Set oWorkbook = Nothing
oWorkbook.visible = True
msgbox "Excel is now available"
'Do the process
Set oWorkbook = Nothing
Set oExcel = Nothing
Implementing this piece of statements in your code, where you open your file to update, should solve the purpose. Hope this will certainly help in your automation.