Forum Discussion

Seetal's avatar
Seetal
Occasional Contributor
13 years ago

Releasing Excel Processes

Hi,



We are connecting to an Excel File by using the following code:


Set objConn = CreateObject("ADODB.Connection")



objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _



"Data Source="+ strExcelFileName + ";Extended Properties='Excel 8.0;HDR=YES'"



objConn.Open



Set ConnectDatabase = objConn



When test execution completes, successfully the Excel file is closed as required as the following statements of code are executed:

 



objConn.Close



Set objConn = Nothing




However, if Test Execution fails at any point during execution and an Excel file has been opened but has not been able to close then the Excel Instance remains open.



This means that I cannot go and open the required Excel file manually to investigate the contents of the Excel file as the Excel file is locked due to it being open by TestComplete.



The only was we have found to resolve this is to Close the project and Close TestComplete and restart TestComplete and re-open the project to release the open Excel process.



Is there better way we can manage this? Or a command that can be run to release the open connections to Excel? Or any other options?



It would be great to have some ideas as it is getting very tedious to have to close and restart TestComplete each time an Excel file is left open during execution.



Thanks
  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    13 years ago

    Hi Seetal,


     


    I would add handling exceptions to the script. In this case, the test won't fail somewhere in the middle of the execution, but, for example, it will post an error to the test log. Go through the "Handling Exceptions in Scripts" article.


     

  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    13 years ago
    Hi Seetal,



    The bad news is that VBScript does not support runtime exceptions handling and thus you cannot implement something like 'finally' code block that is guaranteed to be executed.

    In my opinion, you have only two options available (not counting the third obvious one when you check and close Excel manually):

    a) Execute the On Error Resume Next command before opening connection to Excel and check for (If 0 <> Err.Number) error condition after *any* code line that makes use of the opened connection. After closing the connection execute the On Error GoTo 0 command to restore normal error handling in VBScript;

    b) Check if the Excel is already running before opening the connection and terminate it (Excel) if it is running. The drawback of this solution is that you might lose unsaved data in Excel.
  • Seetal's avatar
    Seetal
    Occasional Contributor
    13 years ago
    Hi.. thanks to both of you for replying.



    We are using several different Excel Files during test execution as we are driving our tests from a framework, so that would require added the On Error Resume in so many places and we may not trap actual errors in that case when they do occur.



    I was just looking to find a way if TestComplete can release any objects that it has opened without having to Close and reopen TestComplete. 
  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    13 years ago
    Hi,



    As you are using Excel via COM (Set objConn = CreateObject("ADODB.Connection")), it is COM environment that do reference counting and keeps Excel in memory but not TestComplete. So I am not sure that you have other options except mentioned by me or the test project close/TestComplete restart approach used by you.