Jump to content
Sign in to follow this  
drtrann

_ExcelWriteCell instantly closes program without error

Recommended Posts

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.

#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 by drtrann

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
drtrann

nope still just shuts down without any errors

Share this post


Link to post
Share on other sites
water

You use a variable that doesn't exist:

$parsesplite3

should be replaced with

$parsesplit3
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
drtrann

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

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

Edited by drtrann

Share this post


Link to post
Share on other sites
water

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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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.

Share this post


Link to post
Share on other sites
water

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

Edited by water
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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 by drtrann

Share this post


Link to post
Share on other sites
water

Glad you could solve the problem :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Sign in to follow this  

×