How to close conseq. excel sheets?

[shreeks]

My issue might sound simple but I couldn’t resolve it. Here goes the explanation.

My AutoIt script should read a DB and write all DB records onto an excel sheet and save it([1]). Now I read a cell and if there is a value “abc”, I open another excel document and update a cell to “abc”, save and close it. Eventually I should close the former excel sheet too(I have already saved it – Refer [1]).

I’m able to read a DB, write records to excel and save. Subsequently, if there is a value “abc”, I could open another excel document and update a cell to “abc”, and save it. The PROBLEM is, I couldn’t close that active sheet(its saved one) as well as my former excel as well. How to close w.r.t this scenario? Could anyone help me out? Tx.

I tried using

_ExcelBookClose($oExcel, 1, 0)

but of no use.

[water]

Do you run AutoIt on a 64 bit System and is Office the 32 bit version? If yes, then please run the AutoIt script as 32 bit.

I once had the same problem and inserting #AutoIt3Wrapper_UseX64=n solved it.

[shreeks]

Do you run AutoIt on a 64 bit System and is Office the 32 bit version? If yes, then please run the AutoIt script as 32 bit.

I once had the same problem and inserting #AutoIt3Wrapper_UseX64=n solved it.

Hi Water,

Yup. I run the code on Win 7(32 bit OS) and Office version is 2007.

[Juvigy]

Check this:

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
                   MsgBox(0,"",$element.FullName)
                   $element.Close
           Next
$oExcel.Application.Quit

It loops all open excel WB , displays name and closes them.You can modify it to your needs.

[shreeks]

Check this:

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
                   MsgBox(0,"",$element.FullName)
                   $element.Close
           Next
$oExcel.Application.Quit

Tx Juvigy for your contribution.It loops all open excel WB , displays name and closes them.You can modify it to your needs.

Still I'm facing the same issue. Excel sheets are not closing yet.

I manually closed all the excel sheets and found the following in Console. Could you help me out?

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\246897\Desktop\ETL Testcases Automation\STG-ODS\AU3\Club1.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams    
+>13:01:11 Starting AutoIt3Wrapper v.2.0.1.24    Environment(Language:0409  Keyboard:00000409  OS:WIN_7/  CPU:X64 OS:X86)
>Running AU3Check (1.54.19.0)  from:C:\Program Files\AutoIt3
+>13:01:11 AU3Check ended.rc:0
>Running:(3.3.6.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Users\246897\Desktop\ETL Testcases Automation\STG-ODS\AU3\Club1.au3"    
C:\Program Files\AutoIt3\Include\excel.au3 (681) : ==> Variable must be of type "Object".:
Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value
Return $oExcel.Activesheet^ ERROR
->13:01:25 AutoIT3.exe ended.rc:1
>Exit code: 1    Time: 14.620

[shreeks]

Friends,

For further reference I'm posting the whole code. Suggestions/contributions are welcome.

expandcollapse popup

]
BlockInput(1)
#include <_sql.au3>
#include <array.au3>
#include <excel.au3>
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc");
$oconn = ObjCreate("ADODB.Connection")
$DSN = "DRIVER={PostgreSQL ANSI};SERVER=10.239.5.188;DATABASE=AIC_DEV;UID=aic_rp_usr;PWD=aicrp;"
$d = $oconn.Open($DSN)
$adors = ObjCreate( "ADODB.RecordSet" )
$adors.Open("SELECT distinct day_status FROM aic_worktime_daily_ods WHERE AVAILABLETIME = '99999.00' AND APPROVEDSTATUS <> 'A' AND (REASON <> 'NA' OR WEEKDAY IN ('Saturday','Sunday'))", $oConn )
Local $associate_id_column_name="associate_id"
      $city_column_name="city"
      $testcase_ID_column_name = "Testcase_ID"
        $associate_id_text="AIC_ETL_STG_ODS_TC_03"
      $testcase_column_name = "Test Case"
        $testcase_text="Validate if DAY_STATUS column is “HD”(Holiday)"
      $Execution_steps_column_name = "Steps to execute the test case"
        $Execution_steps_text="Check if AVAILABLETIME column of an associate is 'null' and the REASON column is not equal to “NA” and the WEEKDAY column is “Saturday” or “Sunday”"
      $Expected_result_column_name = "Expected Result"
        $Expected_result_text="DAY_STATUS should be 'HD'"
      $Query_Execution_column_name = "Query Execution"
        $Query_Execution_text="SELECT distinct day_status FROM aic_worktime_daily_ods WHERE AVAILABLETIME = '99999.00' AND APPROVEDSTATUS <> 'A' AND (REASON <> 'NA' OR WEEKDAY IN ('Saturday','Sunday'))"
      $Actual_result_column_name = "Actual Result"
      $Status_column_name = "PASS / FAIL"
       $actual_column_name="day_status"
$day_status=$adors.Fields("day_status").Value
$oExcel = ObjCreate("Excel.Application")
Local $oExcel = _ExcelBookNew(1)
_ExcelRowInsert($oExcel, 1, 1)
_ExcelWriteCell($oExcel, $testcase_ID_column_name, 1,1)
    _ExcelWriteCell($oExcel, $associate_id_text, 3,1)
_ExcelWriteCell($oExcel, $testcase_column_name, 1,2)
    _ExcelWriteCell($oExcel, $testcase_text, 3,2)
_ExcelWriteCell($oExcel, $Execution_steps_column_name, 1,3)
    _ExcelWriteCell($oExcel, $Execution_steps_text, 3,3)
_ExcelWriteCell($oExcel, $Expected_result_column_name, 1,4)
    _ExcelWriteCell($oExcel,$Expected_result_text, 3,4)
_ExcelWriteCell($oExcel, $Query_Execution_column_name, 1,5)
    _ExcelWriteCell($oExcel,$Query_Execution_text, 3,5)
_ExcelWriteCell($oExcel, $Actual_result_column_name, 1,6)
    _ExcelWriteCell($oExcel, $actual_column_name, 2,6)
_ExcelWriteCell($oExcel, $Status_column_name, 1,7)
_ExcelWriteCell($oExcel, $day_status, 3,7)  ;column title
For $i = 3 To 7
While not $adors.EOF
    $day_status = $adors.Fields("day_status").Value
    _ExcelWriteCell($oExcel, $day_status, $i,6)
    $sCellValue1 = _ExcelReadCell($oExcel, $i,6)
    If IsString($sCellValue1) Then
        $status_pass = _ExcelWriteCell($oExcel, "PASS", $i,7)
    Else
        $status_fail = _ExcelWriteCell($oExcel, "FAIL", $i,7)
    EndIf
    $i = $i + 1
$adors.MoveNext
WEnd
_ExcelBookSaveAs($oExcel, @ScriptDir & "\AIC_ETL_STG_ODS_TC_01-TEMP_3", "xls")
$Rowcount_Childtestcase = $oExcel.Worksheets(1).Usedrange.Rows.Count
Local $j=3
$parent_testcase = @ScriptDir & "\AIC_ETL_TestCase_V0 3.xls"
$parent_testcase_status_change= _ExcelBookOpen($parent_testcase)
$parent_testcase_status_change.Visible = 1
_ExcelSheetActivate($parent_testcase_status_change, "AIC ETL Testing-STG-ODS")
WinActivate('Microsoft Excel - AIC_ETL_TestCase_V0 3  [Compatibility Mode]')
WinWaitActive('Microsoft Excel - AIC_ETL_TestCase_V0 3  [Compatibility Mode]')
Sleep(2000)
    Do
    $sCellValue4 = _ExcelReadCell($oExcel, $j,7)
    If $sCellValue4 == "FAIL" Then
         _ExcelWriteCell($parent_testcase_status_change, "FAIL", 12,7)
         Exit
     ElseIf $sCellValue4 == "PASS" Then
        _ExcelWriteCell($parent_testcase_status_change, "PASS", 12,7)
    EndIf
    $j = $j + 1
    Until $j = $Rowcount_Childtestcase
    $oExcel.ActiveWorkBook.Saved = 1
    ;_ExcelBookClose($oExcel, 1, 0)
    ;If @error = 1 Then
    ;    ConsoleWrite("@error is 1")
    ;EndIf
    ;$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
                   MsgBox(0,"",$element.FullName)
                   $element.Close
           Next
    $oExcel.Application.Quit
Exit
Next

[Juvigy]

The error you get is somewhere in the include file.

Try my prev. post only and see if it works well.

If your code till the call of _ExcelBookClose works, just add my code at the end (maybe modify it a bit.

[water]

Hi Water,

Yup. I run the code on Win 7(32 bit OS) and Office version is 2007.

Did you insert

#AutoIt3Wrapper_UseX64=n

to run the program as 32 bit?