New Excel Functions Crashing

[PoolGuy]

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.

[Bert]

run a repair on excel application from the control panal. I don't think it is Auto It.

[PoolGuy]

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.

[water]

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

[PoolGuy]

expandcollapse popup

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

[water]

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

[PoolGuy]

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.

[water]

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?

[water]

... 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 September 5, 2015 by water

[PoolGuy]

 

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.

[water]