ExcelLockedMost 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.

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim bExcelReadOnly
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
msgbox bExcelReadOnly
If bExcelReadOnly Then ' If readonly
' Close the file and release the obect
oExcel.Application.Quit
Set oWorkbook = Nothing
wait 0,10
End If
Loop
oWorkbook.visible = True
msgbox "Excel is now available"
'Do the process
'...
'...
'Kill Objects
oExcel.Application.Quit
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.

See also  Test Log - TestComplete

2 COMMENTS

  1. Got a chance to work under Saket’s leadership. He is super talented and had answers for all the issues I raised. Best thing i learned from him is Learning how to learn. Best luck!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.