i have wrote below code to close and quit after updating some rows, but while running tests its failing and crashing.
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
'Quit Excel
objExcel.Application.Quit
thanks
Mahender
I'm not 100% certain the "Application.Quit" is necessary. Have you tried running without it?
I think you need to quit the application as otherwise it leaves an instance of the process floating around after the run has completed? (If running it VIA COM - which I assume this is)
I save workbook, close workbook and quit excel when complete. Never crashes for me. Using TC 11.20, Win 7 Pro. Office 2010.
Only difference I can see is that I have: objExcel.Quit
Whereas you have: objExcel.Application.Quit
Try removing ".Application"?
The other possibility is that you don't have the right permissions to save the file. This could be the case if you're trying to save it somewhere "sensitive" on the C: drive or something?
Im using TC 9.3 with server 2012,iam doing with below code, attached screenshot
On Error Resume Next
strSheetName = "TestCase"
Set objExcel = CreateObject("Excel.Application")
objExcel.visible = False
Set objWorkBook = objExcel.Workbooks.Open(strResultFilePath)
Set objSheet = objExcel.ActiveWorkbook.Worksheets(strSheetName)
Rem Creating ADODB connection object
Set objConn = CreateObject("ADODB.Connection")
Rem connecting to Excel db
With objConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strResultFilePath & ";" & "Extended Properties=Excel 8.0;"
.Open
End With
Err.Clear
if Err.Number <> 0 Then
Call Sub_voidTestLog("Sub_voidTestResult - failed to connect to Result file. Error Description: "&Err.Description,False)
Call Fun_EnvWrite("strGenericFunctionStatus","Fail")
Exit Sub
End if
Rem Creating ADODB record set object
Set objRS = CreateObject("ADODB.Recordset")
strResult = Replace(strResult,"'","")
arrTC = Split(strResult,"||")
strPerformanceResult = Fun_objEnvRead("strPerformanceResult")
Rem Query to update the excel file
if arrTC(1) = "PASS" Then
strSQL = "UPDATE [" & strSheetName & "$] SET Time_Stamp="&arrTC(0)&",ExecutionStatus='"&arrTC(1)&"',TimeToExecute="&arrTC(2)&",ResultDescrption='"&strPerformanceResult&"' WHERE TestCaseID='"&arrTC(4)&"'"
Else
strSQL = "UPDATE [" & strSheetName & "$] SET Time_Stamp="&arrTC(0)&",ExecutionStatus='"&arrTC(1)&"',TimeToExecute="&arrTC(2)&",ResultDescrption='"&arrTC(3)&"' WHERE TestCaseID='"&arrTC(4)&"'"
End if
Rem loading data to the object objRS
objRS.open strSQL, objConn
if Err.Number <> 0 Then
strSQL = "UPDATE [" & strSheetName & "$] SET Time_Stamp="&arrTC(0)&",ExecutionStatus='"&arrTC(1)&"',TimeToExecute="&arrTC(2)&",ResultDescrption='"&Err.Description&". Please see log file"&"' WHERE TestCaseID='"&arrTC(4)&"'"
objRS.open strSQL, objConn
Call Sub_voidTestLog("Sub_voidTestResult - failed to execute the query. Error Description: "&Err.Description,False)
Call Sub_objEnvWrite("strGenericFunctionStatus","Fail")
Exit Sub
End if
objExcel.Application.DisplayAlerts = False
Rem Save the Test Results excel file
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.DisplayAlerts = True
objExcel.ActiveWorkbook.Quit
Rem Killing the instance
Set objExcel = Nothing
Set objWorkBook = Nothing
Set objSheet = Nothing
Set objRS = nothing
objConn.close
Set objConn = Nothing
End Sub
