_ExcelWriteCell instantly closes program without error

[drtrann]

hey guys,

i've been trying to help a family member bring their investment spreadsheets into the 21st century.

i've created a simple app the parses the needed data from finance.google.com and i'm able to do everything but write that value back to a cell in the spreadsheet.

basically what happens is once i call _ExcelWriteCell() in GetInfo(), the program instantly shuts down without any error being tossed.

expandcollapse popup

#RequireAdmin
#include <ComboConstants.au3>
#include <GuiToolbar.au3>
#include <EditConstants.au3>
#include <GuiConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <_XLSXReadToArray.au3>
#include <file.au3>
HotKeySet("{ESC}", "_exit")
HotKeySet("{SPACE}", "GetInfo")
global $parsedata
global $parsestring
global $aData
global $EFile
$form1 = GUIcreate("test", 500,500, 200,200)
$go = GUICtrlCreateButton("Update", 100,50,200,40)
;$edit1 = GUICtrlCreateEdit($parsedata, 10,10,480,480)
;$grid1 = GUICtrlCreateListView("short|full name|bought value|current value|profit",-1,-1, 500,500 )
GUISetState(@SW_show, $form1)
PopulateGrid()
Func _exit()
   Exit
EndFunc
func FindExcelLength()
EndFunc
func PopulateGrid()
   ;$EFile = FileOpen(@ScriptDir & "\stocks.xlsx")
   if @error = 1 Then
      MsgBox("","","shits fucked")
   EndIf
   $EFile =_ExcelBookAttach(@ScriptDir & "\stocks.xlsx")
   if @error = 1 or @error = 2 or @error = 3 or @error = 4 or @error = 5 Then
      MsgBox("","","shits fucked "& @error)
   EndIf
   $FActivate = _ExcelSheetActivate($EFile, 1)
   $aData = _ExcelReadArray($EFile, 1, 1, 2, 1)
   _ArrayDisplay($aData)
   ;_ExcelWriteCell($EFile, $a[0],1,2)
   ;MsgBox("","", $a)
  ;GuiCtrlSetData($grid1,$a[0])
EndFunc
func GetInfo()
   MsgBox("","",$aData[0])
   for $i = 0 to 1
      MsgBox("","",$aData[$i])
      $parsedata = InetRead("http://www.google.com/finance?q="& $aData[$i])
      MsgBox("","","inet")
      $parsestring = BinaryToString($parsedata)
      MsgBox("","","split1")
      $parsesplit = StringSplit($parsestring, '<span class="pr"', 1)
      MsgBox("","","split2")
      $parsesplit2 = StringSplit($parsesplit[2], ">",1)
      MsgBox("","","split3")
      $parsesplit3 = StringSplit($parsesplit2[3], "<",1)
      MsgBox("","","splits done")
      MsgBox("","",$parsesplit3[1])
      _ExcelWriteCell($EFile, $parsesplite3[1], $i+1,2)
      if @error = 1 or @error = 2 or @error = 3 or @error = 4 or @error = 5 Then
         MsgBox("","","shits fucked "& @error)
      EndIf
      ;GuiCtrlSetData($edit1, $parsesplit3[1])
   Next
EndFunc

While 1
   $nMsg = GUIGetMsg()
   Switch $nMsg
      case $GUI_EVENT_CLOSE
         Exit
      case $go
         GetInfo()
   EndSwitch
WEnd

I'd appreciate any help you guys can toss my way.

thanks

ps. different issue all together, but i just installed the latest autoit+scite on my laptop to travel with and it doesn't seem to recognize/ autocomplete functions from #includes like it does on my other versions. 

Edited May 16, 2013 by drtrann

[water]

Replace

if @error = 1 or @error = 2 or @error = 3 or @error = 4 or @error = 5 Then

with

if @error <> 0 Then

and see if you get an error code.

[drtrann]

nope still just shuts down without any errors

[water]

You use a variable that doesn't exist:

$parsesplite3

should be replaced with

$parsesplit3

[drtrann]

....wow *facepalm. been looking at that for hours and never saw that. again you've saved me from my own stupidity

would you happen to have a solution to the autocomplete issue?

Edited May 16, 2013 by drtrann

[water]

To resolve the autocomplete issue you need to install the full Scite4AutoIt package.

[drtrann]

To resolve the autocomplete issue you need to install the full Scite4AutoIt package

THANK YOU!

hate to ask after such a stupid mistake, but would you know why the _ExcelWriteCell() doesnt actually edit the excel file, it returns that it does, but when i open the file, its the same as it was before.

[water]

The function edits the cell in memory. But you still need to write the workbook back to disk using _ExcelBookSave.

Edited May 16, 2013 by water

[drtrann]

after changing GetInfo() to

func GetInfo()
   for $i = 0 to 1
      MsgBox("","",$aData[$i])
      $parsedata = InetRead("http://www.google.com/finance?q="& $aData[$i])
      $parsestring = BinaryToString($parsedata)
      $parsesplit = StringSplit($parsestring, '<span class="pr"', 1)
      $parsesplit2 = StringSplit($parsesplit[2], ">",1)
      $parsesplit3 = StringSplit($parsesplit2[3], "<",1)
      MsgBox("","","splits done")
      MsgBox("","",$parsesplit3[1])
      _ExcelWriteCell($EFile, $parsesplit3[1], $i+1,2)
      if @error <> 0 Then
         MsgBox("","","shits fucked "& @error)
      EndIf
      ;GuiCtrlSetData($edit1, $parsesplit3[1])
  Next
  _ExcelBookSave($EFile)
  _ExcelBookClose($EFile)
EndFunc

when i attempt to open the XLSX file again in excel to check if it works, the file it totally blank. it doesnt even have an sheets in it. was hoping you could point me in the right direction

[water]

Unfortunately the Excel UDF that comes with AutoIt isn't able to handle the new XLSX file format.

Use the (alpha) version of my rewrite of the EXCEL UDF. For download please check my signature.

[drtrann]

Unfortunately the Excel UDF that comes with AutoIt isn't able to handle the new XLSX file format.

Use the (alpha) version of my rewrite of the EXCEL UDF. For download please check my signature.

thank you for your work on the UDF, i seem to be getting some errors from it though

expandcollapse popup

C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(15,40) : ERROR: $xlCenter previously declared as a 'Const'.
Global Const $xlCenter = -4108 ; Center
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(16,36) : ERROR: $xlLeft previously declared as a 'Const'.
Global Const $xlLeft = -4131 ; Left
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(17,38) : ERROR: $xlRight previously declared as a 'Const'.
Global Const $xlRight = -4152 ; Right
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(21,76) : ERROR: $xlCalculationAutomatic previously declared as a 'Const'.
Global Const $xlCalculationAutomatic = -4105 ; Excel controls recalculation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(22,90) : ERROR: $xlCalculationManual previously declared as a 'Const'.
Global Const $xlCalculationManual = -4135 ; Calculation is done when the user requests it
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(44,42) : ERROR: $xlCSVMSDOS previously declared as a 'Const'.
Global Const $xlCSVMSDOS = 24 ; MSDOS CSV
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(61,40) : ERROR: $xlHtml previously declared as a 'Const'.
Global Const $xlHtml = 44 ; HTML format
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(71,41) : ERROR: $xlTemplate previously declared as a 'Const'.
Global Const $xlTemplate = 17 ; Template
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(76,48) : ERROR: $xlTextWindows previously declared as a 'Const'.
Global Const $xlTextWindows = 20 ; Windows Text
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(90,57) : ERROR: $xlWorkbookNormal previously declared as a 'Const'.
Global Const $xlWorkbookNormal = -4143 ; Workbook normal
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(98,44) : ERROR: $xlFormulas previously declared as a 'Const'.
Global Const $xlFormulas = -4123 ; Formulas
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(99,40) : ERROR: $xlValues previously declared as a 'Const'.
Global Const $xlValues = -4163 ; Values
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(113,69) : ERROR: $xlPart previously declared as a 'Const'.
Global Const $xlPart = 2 ; Match against any part of the search text
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(114,71) : ERROR: $xlWhole previously declared as a 'Const'.
Global Const $xlWhole = 1 ; Match against the whole of the search text
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(118,89) : ERROR: $xlSheetHidden previously declared as a 'Const'.
Global Const $xlSheetHidden = 0 ; Hides the worksheet which the user can unhide via menu
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(119,187) : ERROR: $xlSheetVeryHidden previously declared as a 'Const'.
Global Const $xlSheetVeryHidden = 2 ; Hides the object so that the only way for you to make it visible again is by setting this property to True (the user cannot make the object visible)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(120,55) : ERROR: $xlSheetVisible previously declared as a 'Const'.
Global Const $xlSheetVisible = -1 ; Displays the sheet
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(124,72) : ERROR: $xlSortNormal previously declared as a 'Const'.
Global Const $xlSortNormal = 0 ; Sorts numeric and text data separately
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(125,80) : ERROR: $xlSortTextAsNumbers previously declared as a 'Const'.
Global Const $xlSortTextAsNumbers = 1 ; Treat text as numeric data for the sort
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(129,77) : ERROR: $xlAscending previously declared as a 'Const'.
Global Const $xlAscending = 1 ; Sorts the specified field in ascending order
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(130,79) : ERROR: $xlDescending previously declared as a 'Const'.
Global Const $xlDescending = 2 ; Sorts the specified field in descending order
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(134,50) : ERROR: $xlSortColumns previously declared as a 'Const'.
Global Const $xlSortColumns = 1 ; Sorts by column
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(135,71) : ERROR: $xlSortRows previously declared as a 'Const'.
Global Const $xlSortRows = 2 ; Sorts by row. This is the default value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(150,105) : ERROR: $xlGuess previously declared as a 'Const'.
Global Const $xlGuess = 0 ; Excel determines whether there is a header, and where it is, if there is one
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(151,68) : ERROR: $xlNo previously declared as a 'Const'.
Global Const $xlNo = 2 ; Default. The entire range should be sorted
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3(152,64) : ERROR: $xlYes previously declared as a 'Const'.
Global Const $xlYes = 1 ; The entire range should not be sorted
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Program Files (x86)\AutoIt3\Include\Excel rewrite.au3(174,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Users\steven\Documents\stockupdater.au3 - 27 error(s), 0 warning(s)
!>10:40:59 AU3Check ended. Press F4 to jump to next error.rc:2
>Exit code: 2    Time: 0.419

[water]

You can't have both includes in your script. Remove "#include <Excel.au3>" and the error should be gone.

BTW: To run my rewrite you need to run one of the 3.3.9.x beta versions of AutoIt - or edit the UDF.

[drtrann]

You can't have both includes in your script. Remove "#include <Excel.au3>" and the error should be gone.

BTW: To run my rewrite you need to run one of the 3.3.9.x beta versions of AutoIt - or edit the UDF.

 

well after talking to my dad about which excel file he uses, apparently he still uses the old .XLS format, so looks like i dont need to use the rewrite, but i still get the same issue with .XLS files. when it goes to save them the program just ends. even if i use _ExcelBookSaveAs it will just end the script without any error returned. 

nevermind.. apparently changing _ExcelBookAttach to _excelbookopen fixed that and its working properly now. really appreciate all the help you've given water.

Edited May 16, 2013 by drtrann

[water]

Glad you could solve the problem