PoolGuy

New Excel Functions Crashing

11 posts in this topic

I have been using an older version of AutoIt for a while and recently updated to version 3.3.14.1.

I created some accounting software for my business and it uses an Excel template to create invoices.

With the new Excel functions I don't get any errors and it says it saves successfully but when I go to open them from a folder Excel crashes.

it says: "Microsoft Office Excel has stopped working"

 

I think I am using the new functions right, If not can someone point me in the right direction?

Is there any way to use the old Excel functions without going back to an earlier version?

This is an example of what I've been trying.

 

#include <Excel.au3>

Global $hDate = "9/4/2015"
Global $hInvoiceNum = "1000"
Global $hBillName = "Joe Shmoe"
Global $hBillAddress = ""
Global $hBillCity = ""

Global $BillDates[3] = [ 2, "9/4/2015", "9/4/2015"]
Global $BillDescriptions[3] = [ 2, "description", "description"]
Global $BillTotals[3] = [ 2, 4000, 5000]

Global $ItemCount = 0

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen($oExcel, @ScriptDir&"\Data\Invoice Template.xls", False, False)
_Excel_RangeWrite($oWorkBook, DEFAULT, $hDate, "F2")
_Excel_RangeWrite($oWorkBook, DEFAULT, $hInvoiceNum, "F3")
_Excel_RangeWrite($oWorkBook, DEFAULT, $hBillName, "A11")
_Excel_RangeWrite($oWorkBook, DEFAULT, $hBillAddress, "A12")
_Excel_RangeWrite($oWorkBook, DEFAULT, $hBillCity, "A13")

For $i = $BillDates[0] to 1 Step -1
    _Excel_RangeWrite($oWorkBook, DEFAULT, "[ "&$BillDates[$i]&" ]      "&$BillDescriptions[$i], "A"&(16+$ItemCount))
    _Excel_RangeWrite($oWorkBook, DEFAULT, $BillTotals[$i], "F"&(16+$ItemCount))
    $ItemCount += 1
Next
$hInvoiceNum += 1
$oSave = _Excel_BookSaveAs($oWorkBook, @ScriptDir&"\Compiled Invoices\#"&$hInvoiceNum&" - "&StringReplace($hBillName, ".", "")&".xls", $xlExcel8, True)
ConsoleWrite("+ BookSave = "&$oSave&"; ext: "&@extended&@CRLF)
_Excel_BookClose($oWorkBook, False)
_Excel_Close($oExcel)

 

If you need the excel template I can add one, any help is appreciated.

Share this post


Link to post
Share on other sites



Excel is working fine and it still works with a compiled version of my program running the old functions,

It only crashes when I try to open an Excel file I edited with the new functions.

I ran the repair and I'm still getting the same results.

I'm using Microsoft Office Enterprise 2007.

Share this post


Link to post
Share on other sites

Your script needs more error checking. If a function returns an error it goes unnoticed and the next function then might cause the crash.


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
#include <Excel.au3>

Global $hDate = "9/4/2015"
Global $hInvoiceNum = "1000"
Global $hBillName = "Joe Shmoe"
Global $hBillAddress = ""
Global $hBillCity = ""

Global $BillDates[3] = [ 2, "9/4/2015", "9/4/2015"]
Global $BillDescriptions[3] = [ 2, "description", "description"]
Global $BillTotals[3] = [ 2, 4000, 5000]

Global $ItemCount = 0

$oExcel = _Excel_Open(False)
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&": _Excel_Open(False)"&@CRLF)
$oWorkBook = _Excel_BookOpen($oExcel, @ScriptDir&"\Data\Invoice Template.xls", False, False)
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_BookOpen($oExcel, @ScriptDir&"\Data\Invoice Template.xls", False, False)'&@CRLF)
_Excel_RangeWrite($oWorkBook, DEFAULT, $hDate, "F2")
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_RangeWrite($oWorkBook, DEFAULT, $hDate, "F2")'&@CRLF)
_Excel_RangeWrite($oWorkBook, DEFAULT, $hInvoiceNum, "F3")
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_RangeWrite($oWorkBook, DEFAULT, $hInvoiceNum, "F3")'&@CRLF)
_Excel_RangeWrite($oWorkBook, DEFAULT, $hBillName, "A11")
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_RangeWrite($oWorkBook, DEFAULT, $hBillName, "A11")'&@CRLF)
_Excel_RangeWrite($oWorkBook, DEFAULT, $hBillAddress, "A12")
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_RangeWrite($oWorkBook, DEFAULT, $hBillAddress, "A12")'&@CRLF)
_Excel_RangeWrite($oWorkBook, DEFAULT, $hBillCity, "A13")
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_RangeWrite($oWorkBook, DEFAULT, $hBillCity, "A13")'&@CRLF)

For $i = $BillDates[0] to 1 Step -1
    _Excel_RangeWrite($oWorkBook, DEFAULT, "[ "&$BillDates[$i]&" ]      "&$BillDescriptions[$i], "A"&(16+$ItemCount))
    ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_RangeWrite($oWorkBook, DEFAULT, "[ "&$BillDates[$i]&" ]      "&$BillDescriptions[$i], "A"&(16+$ItemCount))'&@CRLF)
    _Excel_RangeWrite($oWorkBook, DEFAULT, $BillTotals[$i], "F"&(16+$ItemCount))
    ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_RangeWrite($oWorkBook, DEFAULT, $BillTotals[$i], "F"&(16+$ItemCount))'&@CRLF)
    $ItemCount += 1
Next
$hInvoiceNum += 1
$oSave = _Excel_BookSaveAs($oWorkBook, @ScriptDir&"\Compiled Invoices\#"&$hInvoiceNum&" - "&StringReplace($hBillName, ".", "")&".xls", $xlExcel8, True)
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_BookSaveAs($oWorkBook, @ScriptDir&"\Compiled Invoices\#"&$hInvoiceNum&" - "&StringReplace($hBillName, ".", "")&".xls", $xlExcel8, True)'&@CRLF)
_Excel_BookClose($oWorkBook, False)
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_BookClose($oWorkBook, False)'&@CRLF)
_Excel_Close($oExcel)
ConsoleWrite("Err: "&@Error&"; Ext: "&@extended&': _Excel_Close($oExcel)'&@CRLF)

My Return:

Err: 0; Ext: 1: _Excel_Open(False)
Err: 0; Ext: 0: _Excel_BookOpen($oExcel, @ScriptDir&"\Data\Invoice Template.xls", False, False)
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, $hDate, "F2")
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, $hInvoiceNum, "F3")
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, $hBillName, "A11")
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, $hBillAddress, "A12")
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, $hBillCity, "A13")
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, "[ "&$BillDates[$i]&" ]      "&$BillDescriptions[$i], "A"&(16+$ItemCount))
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, $BillTotals[$i], "F"&(16+$ItemCount))
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, "[ "&$BillDates[$i]&" ]      "&$BillDescriptions[$i], "A"&(16+$ItemCount))
Err: 0; Ext: 0: _Excel_RangeWrite($oWorkBook, DEFAULT, $BillTotals[$i], "F"&(16+$ItemCount))
Err: 0; Ext: 0: _Excel_BookSaveAs($oWorkBook, @ScriptDir&"\Compiled Invoices\#"&$hInvoiceNum&" - "&StringReplace($hBillName, ".", "")&".xls", $xlExcel8, True)
Err: 0; Ext: 0: _Excel_BookClose($oWorkBook, False)
Err: 0; Ext: 0: _Excel_Close($oExcel)

Share this post


Link to post
Share on other sites

Does opening the saved workbook crash as well when you save it as XSLX?


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

Does opening the saved workbook crash as well when you save it as XSLX?

I'm assuming you mean .xlsx

when i use $xlOpenXMLWorkbook it crashes and when I use $xlWorkbookDefault it doesn't crash but the workbook is blank.

Share this post


Link to post
Share on other sites

Can you please modify the workbook open statement to

$oWorkBook = _Excel_BookOpen($oExcel, @ScriptDir&"\Data\Invoice Template.xls")

so we get error messages if something happens there?


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

#9 ·  Posted (edited)

... when I use $xlWorkbookDefault it doesn't crash but the workbook is blank.

That's fine. Because you have set the workbook to invisible when you open it and you do not make it visible again before saving then it's still invisibile when you open it manually. You have to make it visible again.

It it described in the remarks of _Excel_BookOpen:

When setting $bVisible = False when opening a Workbook make sure to set the Workbook to visible again before saving the Workbook.
Use $oExcel.Windows($oWorkbook.Name).Visible = True to make the Workbook visible again.
Else the Workbook will not be shown when you manually open it using Excel.
Most of the time this parameter is not needed. Using $bVisible = False in _Excel_Open is the preferred way.

Edited by water
1 person likes this

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
 

That's fine. Because you have set the workbook to invisible when you open it and you do not make it visible again before saving then it's still invisibile when you open it manually. You have to make it visible again.

It it described in the remarks of _Excel_BookOpen:

I assumed that $bVisible meant something else, my bad for not reading the remarks carefully enough.

Problem solved! Thank you water.

Share this post


Link to post
Share on other sites

:)


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