ijourneaux Posted July 12, 2014 Share Posted July 12, 2014 I am struggling a little converting over to the new Excel UDF. After I execute the following script (open workbook and delete the "Summary" sheet and save workbook), the excel file seems to be corrupted since I can not open it in Excel. Appreciate any thoughts. Ian #include <excel.au3> $oExcel = _Excel_Open(False) $oExcelWB = _Excel_BookOpen($oExcel, "c:\temp\test.xlsx", False, False) _Excel_SheetDelete($oExcelWB, "Summary") _Excel_booksave($oExcelWB) _Excel_Close($oExcel) Exit Link to comment Share on other sites More sharing options...
water Posted July 12, 2014 Share Posted July 12, 2014 (edited) You need to add some error checking to your script. Every _Excel* function sets @error when a problem occcurred during execution. So what are the values of @error after each call? #include <excel.au3> $oExcel = _Excel_Open(False) ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF) $oExcelWB = _Excel_BookOpen($oExcel, "c:\temp\test.xlsx", False, False) ConsoleWrite("_Excel_BookOpen: @error = " & @error & ", @extended = " & @extended & @CRLF) _Excel_SheetDelete($oExcelWB, "Summary") ConsoleWrite("_Excel_SheetDelete: @error = " & @error & ", @extended = " & @extended & @CRLF) _Excel_booksave($oExcelWB) ConsoleWrite("_Excel_BookSave: @error = " & @error & ", @extended = " & @extended & @CRLF) _Excel_Close($oExcel) ConsoleWrite("_Excel_Close: @error = " & @error & ", @extended = " & @extended & @CRLF) Exit Edited July 12, 2014 by water 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...
ijourneaux Posted July 12, 2014 Author Share Posted July 12, 2014 Before I run the script, I confirmed that i can open the workbook and it looks correct. after the script runs, the sheet is nolonger valid. The extended error on the _Excel_open is unexpected. _Excel_Open: @error = 0, @extended = 1 _Excel_BookOpen: @error = 0, @extended = 0 _Excel_SheetDelete: @error = 0, @extended = 0 _Excel_BookSave: @error = 0, @extended = 1 _Excel_Close: @error = 0, @extended = 0 Link to comment Share on other sites More sharing options...
water Posted July 12, 2014 Share Posted July 12, 2014 According to the help file: _Excel_Open: @extended = 1 - Excel was not running or $bForceNew was set to True. A new Excel instance has been created ==> No error, just an information _Excel_BookSave: @error = 1 - File has been saved because it has been changed since the last save or file open ==> No error, just an information 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...
water Posted July 12, 2014 Share Posted July 12, 2014 Can you please post the error message you get from Excel when you try to open the corrupted workbook? 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...
ijourneaux Posted July 12, 2014 Author Share Posted July 12, 2014 Unfortunately I don't get any error message. Excel just comes up as if there was no sheet opened. I attached the original spreadsheet and the one saved after the autoit script completes testorg.xlsxtest.xlsx Link to comment Share on other sites More sharing options...
water Posted July 12, 2014 Share Posted July 12, 2014 That's simply because you have set the Workbook to invisible when opened by _Excel_BookOpen. When you save the Workbook the book is saved as invisible and hence opened invisible when manually opened. Change $oExcelWB = _Excel_BookOpen($oExcel, "c:\temp\test.xlsx", False, False) to $oExcelWB = _Excel_BookOpen($oExcel, "c:\temp\test.xlsx", False) and run the script again. 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...
ijourneaux Posted July 12, 2014 Author Share Posted July 12, 2014 I would never have found that as I had misinterpreted the permanence of setting visible to false when applied to a workbook. Thanks for the help. Link to comment Share on other sites More sharing options...
water Posted July 12, 2014 Share Posted July 12, 2014 I have added a remark to the help file of function _Excel_BookOpen. 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...
ijourneaux Posted July 13, 2014 Author Share Posted July 13, 2014 I think I am almost converted over to the new Excel interface. I ran into another small problem. I have a workbook that has a summary sheet. I want to delete it so that I can rebuild it from scratch. Unfortunately the delete of the Summary sheet doesn't seem to work all of the time. It does work some of the time though. The sample is derived from the example I sent earlier. #include <excel.au3> $oExcel = _Excel_Open(False) ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF) $oExcelWB = _Excel_BookOpen($oExcel, "c:\temp\test.xlsx", False, True) ConsoleWrite("_Excel_BookOpen: @error = " & @error & ", @extended = " & @extended & @CRLF) _Excel_SheetDelete($oExcelWB, "Summary") ConsoleWrite("_Excel_SheetDelete: @error = " & @error & ", @extended = " & @extended & @CRLF) $SheetListArray = _Excel_SheetList($oExcelWB) consolewrite("UBound: " & UBound($SheetListArray,$UBOUND_ROWS) & @crlf) _Excel_booksave($oExcelWB) ConsoleWrite("_Excel_BookSave: @error = " & @error & ", @extended = " & @extended & @CRLF) _Excel_Close($oExcel) ConsoleWrite("_Excel_Close: @error = " & @error & ", @extended = " & @extended & @CRLF) Exit Here is the console output. In this case UBound should be 8; 9 sheets minus the Summary sheet. _Excel_Open: @error = 0, @extended = 0 _Excel_BookOpen: @error = 0, @extended = 0 _Excel_SheetDelete: @error = 0, @extended = 0 UBound: 9 _Excel_BookSave: @error = 0, @extended = 0 _Excel_Close: @error = 0, @extended = 0 +>21:23:26 AutoIt3.exe ended.rc:0 >Exit code: 0 Time: 1.104 As always, appreciate any help. test.xlsx Link to comment Share on other sites More sharing options...
water Posted July 13, 2014 Share Posted July 13, 2014 (edited) Can you check the number of worksheets before you delete the summary sheet as well? Is it the same number? Edited July 13, 2014 by water 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...
ijourneaux Posted July 13, 2014 Author Share Posted July 13, 2014 I added the check for ubound before I tried to delete the sheet. Results below. The interesting thing is that if I run the script 2 or 3 times in a row it will eventually delete the Summary worksheet. _Excel_Open: @error = 0, @extended = 0 _Excel_BookOpen: @error = 0, @extended = 0 UBound: 9 _Excel_SheetDelete: @error = 0, @extended = 0 UBound: 9 _Excel_BookSave: @error = 0, @extended = 0 _Excel_Close: @error = 0, @extended = 0 +>10:19:25 AutoIt3.exe ended.rc:0 >Exit code: 0 Time: 1.210 Link to comment Share on other sites More sharing options...
water Posted July 13, 2014 Share Posted July 13, 2014 Very, very strange. _Excel_Open: @error = 0, @extended = 0 shows that Excel was running when you started the script. _Excel_BookSave: @error = 0, @extended = 0 shows that the workbook hasn't been saved because nothing has been changed. Can you please give this extended version a try? #include <excel.au3> #include <Debug.au3> _DebugSetup() _DebugCOMError() $oExcel = _Excel_Open(False) ConsoleWrite("_Excel_Open: @error = " & @error & ", @extended = " & @extended & @CRLF) $oExcelWB = _Excel_BookOpen($oExcel, "c:\temp\test.xlsx", False, True) ConsoleWrite("_Excel_BookOpen: @error = " & @error & ", @extended = " & @extended & @CRLF) $SheetListArray = _Excel_SheetList($oExcelWB) Consolewrite("UBound 1: " & UBound($SheetListArray, 1) & @CRLF) $iReturnValue = _Excel_SheetDelete($oExcelWB, "Summary") ConsoleWrite("_Excel_SheetDelete: $iReturnValue = " & $iReturnValue & ", @error = " & @error & ", @extended = " & @extended & @CRLF) $SheetListArray = _Excel_SheetList($oExcelWB) Consolewrite("UBound 2: " & UBound($SheetListArray, 1) & @CRLF) $iReturnValue = _Excel_BookSave($oExcelWB) ConsoleWrite("_Excel_BookSave: $iReturnValue = " & $iReturnValue & ", @error = " & @error & ", @extended = " & @extended & @CRLF) $iReturnValue = _Excel_Close($oExcel) ConsoleWrite("_Excel_Close: $iReturnValue = " & $iReturnValue & ", @error = " & @error & ", @extended = " & @extended & @CRLF) Exit 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...
ijourneaux Posted July 13, 2014 Author Share Posted July 13, 2014 The console window output is _Excel_Open: @error = 0, @extended = 0 _Excel_BookOpen: @error = 0, @extended = 0 UBound 1: 9 _Excel_SheetDelete: $iReturnValue = 1, @error = 0, @extended = 0 UBound 2: 9 _Excel_BookSave: $iReturnValue = 1, @error = 0, @extended = 0 _Excel_Close: $iReturnValue = 1, @error = 0, @extended = 0 The Com Debug window is blank >>>>>> Please close the "Report Log Window" to exit <<<<<<< This Left Excel running in the task manager. Closed out running Excel tasks reran the script. Produced the same result. After closing the excel task again running the task successfully deleted the Summary tab. This time the @@ DEBUG COM Error encountered in TestExcel3.au3 (66) : Number = 0x80020006 (-2147352570) WinDescription = Unknown name. Description = Source = HelpFile = HelpContext = LastDllError = 0 Retcode = 0x00000000 >>>>>> Please close the "Report Log Window" to exit <<<<<<< _Excel_Open: @error = 0, @extended = 1 _Excel_BookOpen: @error = 0, @extended = 0 UBound 1: 9 _Excel_SheetDelete: $iReturnValue = 1, @error = 0, @extended = 0 UBound 2: 8 _Excel_BookSave: $iReturnValue = 1, @error = 0, @extended = 1 _Excel_Close: $iReturnValue = 1, @error = 0, @extended = 0 +>12:36:53 AutoIt3.exe ended.rc:0 >Exit code: 0 Time: 34.821 Link to comment Share on other sites More sharing options...
water Posted July 13, 2014 Share Posted July 13, 2014 Can you verify that the sheet is always deleted when Excel is not running when you start the script. And that it doesn't get deleted when Excel was up and running before starting the script? 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...
ijourneaux Posted July 13, 2014 Author Share Posted July 13, 2014 If excel is running with no workbook open, the sheet is not deleted, the Excel program icon disappears but Excel is still running in task manager. If I kill Excel in task manager and rerun the script the Summary Sheet is deleted as it should. Link to comment Share on other sites More sharing options...
water Posted July 13, 2014 Share Posted July 13, 2014 Which version of Excel do you run? Which version of AutoIt do you run? 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...
ijourneaux Posted July 13, 2014 Author Share Posted July 13, 2014 Should have given you that info earlier. Office 2007 AutoIT 3.3.12.0 Link to comment Share on other sites More sharing options...
water Posted July 13, 2014 Share Posted July 13, 2014 I'm running Office 2010 and will test tomorrow as soon as I'm in my office again. 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...
ijourneaux Posted July 13, 2014 Author Share Posted July 13, 2014 I appreciate the help on this. Here is a little more info. I added a msgbox before the _Excel_BookSave command. If Excel is open and visible before the script is run, the _excel_open(false) seems to make the Excel window disappear but it remains in the task manager which seems the expected behavior. At that point, I get the following com debug text. @@ DEBUG COM Error encountered in TestExcel3.au3 (1056) : Number = 0x80020009 (-2147352567) WinDescription = Exception occurred. Description = Source = HelpFi = HelpContext = 0 LastDllError = 0 Retcode = 0x8002000B >>>>>> Please close the "Report Log Window" to exit <<<<<<< If I let the script complete, the Summary sheet is not deleted. One comment on the UDF. I really miss the ability to access the cells with row, column. When I build the Summary sheet, I have a bunch of do loops that iterate on row and column. It is possible to do it with the RangeRead and RangeWrite but the syntax is messier _Excel_RangeWrite($oExcelWB, "Summary", $SheetListArray[$i][0], _Excel_ColumnToLetter($i + 4) & $WireRow) vs _Excel_CellWrite($oExcelWB, "Summary", $SheetListArray[$i][0], $WireRow, $i) 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