Sign in to follow this  
Followers 0
shreeks

How to close conseq. excel sheets?

8 posts in this topic

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.


Learning is Lifelong!

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.


Learning is Lifelong!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


Learning is Lifelong!

Share this post


Link to post
Share on other sites

Friends,

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

]
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


Learning is Lifelong!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0