shreeks Posted June 14, 2011 Share Posted June 14, 2011 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! Link to comment Share on other sites More sharing options...
water Posted June 14, 2011 Share Posted June 14, 2011 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
shreeks Posted June 14, 2011 Author Share Posted June 14, 2011 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! Link to comment Share on other sites More sharing options...
Juvigy Posted June 14, 2011 Share Posted June 14, 2011 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. Link to comment Share on other sites More sharing options...
shreeks Posted June 14, 2011 Author Share Posted June 14, 2011 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! Link to comment Share on other sites More sharing options...
shreeks Posted June 14, 2011 Author Share Posted June 14, 2011 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 Learning is Lifelong! Link to comment Share on other sites More sharing options...
Juvigy Posted June 14, 2011 Share Posted June 14, 2011 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. Link to comment Share on other sites More sharing options...
water Posted June 14, 2011 Share Posted June 14, 2011 Hi Water, Yup. I run the code on Win 7(32 bit OS) and Office version is 2007. Did you insert#AutoIt3Wrapper_UseX64=nto run the program as 32 bit? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now