Having problems with new Excel UDF

[ijourneaux]

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

[water]

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

[ijourneaux]

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

[water]

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

[water]

Can you please post the error message you get from Excel when you try to open the corrupted workbook?

[ijourneaux]

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.xlsx

test.xlsx

[water]

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.

[ijourneaux]

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.

[water]

I have added a remark to the help file of function _Excel_BookOpen.

[ijourneaux]

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

[water]

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

[ijourneaux]

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

[water]

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

[ijourneaux]

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

[water]

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?

[ijourneaux]

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. 

[water]

Which version of Excel do you run?

Which version of AutoIt do you run?

[ijourneaux]

Should have given you that info earlier.

Office 2007

AutoIT 3.3.12.0

[water]

I'm running Office 2010 and will test tomorrow as soon as I'm in my office again.

[ijourneaux]

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)