Sign in to follow this  
Followers 0
ijourneaux

Having problems with new Excel UDF

28 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (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 by water

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

Share this post


Link to post
Share on other sites

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

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

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

Share this post


Link to post
Share on other sites

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

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.

Share this post


Link to post
Share on other sites

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


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

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

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Can you check the number of worksheets before you delete the summary sheet as well? Is it the same number?

Edited by water

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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. 

Share this post


Link to post
Share on other sites

Which version of Excel do you run?

Which version of AutoIt do you run?


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

Should have given you that info earlier.

Office 2007

AutoIT 3.3.12.0

Share this post


Link to post
Share on other sites

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

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)

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