Sign in to follow this  
Followers 0
SorryButImaNewbie

AutoIt script turns off, because of array is out of bound

1 post in this topic

Hello all!

I wrote a little script to automate some of my work (for which I'm greatfull).

I have little problem now. My script get data of currency values from specified time period. The problem, if XY day is a holiday or something, then the bank doesn't have new values for that day, thus my script can ask for "till that date" if there is only data for "till that date-1".

I should be done with this for tomorrow and I should learn for my test paper tomorrow, make a genetic algorhytm homework, and at least look at a ppt...

So here is my code, pleas ask if you need more information!

InternetRead2() ;Creating Date and Arfolyam arrays, using DateInterval read from above (_ArraysDisplayed and function is "tested")
;Calculate and write HUF*OSSZEG
Do
    Local $SZAMLATELJOlvaso = _Excel_RangeRead($ExcelObject, Default, $SZAMLATELJoszlop & $CellaOlvasoSzamlalo)
    Local $Time = StringTrimRight($SZAMLATELJOlvaso, 6)
    $DateArrayTimeIndex = _ArraySearch($DateArray, $Time, 0, 0, 0, 0, 1)
    ;MsgBox(64, "Értesítés", $DateArrayTimeIndex)

        If @error = 6 Then
            $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-1, 0, 0, 0, 0, 1)
            ;MsgBox(64, "Értesítés", $DateArrayTimeIndex)
        EndIf
        If @error = 6 Then
            $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-2, 0, 0, 0, 0, 1)
            ;MsgBox(64, "Értesítés", $DateArrayTimeIndex)
        EndIf
        If @error = 6 Then
            $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-3, 0, 0, 0, 0, 1)
            ;MsgBox(64, "Értesítés", $DateArrayTimeIndex)
        EndIf
        If @error = 6 Then
            $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-4, 0, 0, 0, 0, 1)
            ;MsgBox(64, "Értesítés", $DateArrayTimeIndex)
        EndIf

    $DateArrayTimeIndex = $DateArrayTimeIndex+1 ;Egyébként arraysearch -1et ad vissza, 0nál. Passz
    Sleep(200)

    If Not $SZAMLATELJOlvaso = "" Then
    _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$DateArrayTimeIndex]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo)
        If @error Then
            _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$oXML_Node.Length]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo)
            MsgBox(64, "Értesítés", "Valamilyen okból, nem találok az adott dátumhoz árfolyamot, a keresési idők közül a legutolsó árfolyammal töltöttem ki!" & @CRLF & Number($oXML_Node_Datum))
        EndIf
    EndIf


    $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1

    Until $SZAMLATELJOlvaso = ""

also my internetread2 function :

Func InternetRead2() ;ezt használja az excelmove
;-----------------------------------
;Original Source: https://www.autoitscript.com/forum/topic/184884-solved-basic-com-help-working-on-string-returned-from-api-mi-doing-this-okey/
;by Genius257

;TO DO: Some error handling maybe: what if there is no $MinTime/$MaxTime?
;Read the returns in to array for future use --> Right now, 2 arrays one with the dates and one with the exchangerates
;-----------------------------------
    ;Globals decleared to avoid warnings
    Global $MinTime ;20160601000000
    Global $MaxTime ;20160610000000
    Global $MinTimeFormated = StringTrimRight($MinTime, 6)
    Global $MaxTimeFormated = StringTrimRight($MaxTime, 6)


    $oHTTP = ObjCreate("WinHttp.WinHttpRequest.5.1")
    If $MaxTimeFormated = "" and $MinTimeFormated = "" Then
        MsgBox(64, "Értesítés", "Nincsenek beolvasott dátumok a memóriában, dummy dátumokat adok meg!" & @CRLF & "20160901, 20160910")
        $MinTimeFormated = "20160901"
        $MaxTimeFormated = "20160905"
    EndIf
    $oHTTP.Open("GET", "http://api.napiarfolyam.hu/?bank=mnb&valuta=eur&datum="&$MinTimeFormated&"&datumend="&$MaxTimeFormated&"", False)
    $oHTTP.Send()
    $sXML = $oHTTP.responseText
    Global $oXML = ObjCreate("Microsoft.XMLDOM")
    $oXML.loadXML( $sXML )
    $oXML_Nodes = $oXML.SelectNodes("./arfolyamok/deviza/item")

    Global $ArfolyamArray[$oXML_Nodes.Length]
    ;MsgBox(64, "Title", "" & $oXML_Nodes.Length & "")
    ;_ArrayDisplay($ArfolyamArray)
    Global $DateArray[$oXML_Nodes.Length]

For $i=0 To $oXML_Nodes.Length-1

    Global $oXML_Node = $oXML_Nodes.Item($i)
    $oXML_Node_Bank = $oXML_Node.SelectNodes("./bank")
    $oXML_Node_Bank = $oXML_Node_Bank.Length>0?$oXML_Node_Bank.Item(0).text:""
    Global $oXML_Node_Datum = $oXML_Node.SelectNodes("./datum")
    $oXML_Node_Datum = $oXML_Node_Datum.Length>0?$oXML_Node_Datum.Item(0).text:""
    $oXML_Node_Penznem = $oXML_Node.SelectNodes("./penznem")
    $oXML_Node_Penznem = $oXML_Node_Penznem.Length>0?$oXML_Node_Penznem.Item(0).text:""
    $oXML_Node_Kozeps = $oXML_Node.SelectNodes("./kozep")
    $oXML_Node_Kozep01 = $oXML_Node_Kozeps.Length>0?$oXML_Node_Kozeps.Item(0).text:""
    $oXML_Node_Kozep02 = $oXML_Node_Kozeps.Length>1?$oXML_Node_Kozeps.Item(1).text:""

    $ArfolyamArray[$i] = $oXML_Node_Kozep01
    $DateArray[$i] = StringTrimRight(StringReplace($oXML_Node_Datum, "-", ""), 8)

    ConsoleWrite( "Match [" & StringFormat("%02i", $i+1) & "]:"&@CRLF& _
        @TAB&"Bank: "&@TAB&$oXML_Node_Bank&@CRLF& _
        @TAB&"Datum: "&@TAB&$oXML_Node_Datum&@CRLF& _
        @TAB&"Penznem: "&@TAB&$oXML_Node_Penznem&@CRLF& _
        @TAB&"Kozep01: "&@TAB&$oXML_Node_Kozep01&@CRLF& _
        @TAB&"Kozep02: "&@TAB&$oXML_Node_Kozep02&@CRLF _
    )
Next
;_ArrayDisplay($ArfolyamArray)
;_ArrayDisplay($DateArray)
EndFunc

My problem is that I have dates in my excel till 2016.10.30, but I only have dates till 28.

(see the api, according to aoutit console, I have the same data: http://api.napiarfolyam.hu/?bank=mnb&valuta=eur&datum=20161001&datumend=20161030 )

My idea/goal (with the If @error then... after @SZAMLATELJolvaso part) is that if this happens, I want it to automaticly jump to the last date of the array (I will be able to write something more elaborate, but for now thats my goal, to save the sinking ship :D )

Thank yu for your help and insight!

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

  • Similar Content

    • Nareshm
      By Nareshm
      How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name.
      I Tried with
      Winactivate ("[CLASS:XLMAIN]") but not working
    • willichan
      By willichan
      Here is another one from my archives that filled a specific need.
       
      Here is the back story if you are interested.
       
      Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.
      #cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global $SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global $sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database") $SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError $sOccurenceName = StringReplace($sOccurenceName, "\", "") $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName) $lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] = $ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile, $vResult, $iErrLoop $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf $vResult = $SQLITE_IOERR $iErrLoop = 5 While $vResult = $SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF) $vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0, $MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery, $aCount, $iErrLoop, $vResult ConsoleWrite($strFileName & @CRLF) Local $iLoop, $iExists Local $aLinks Local Const $xlExcelLinks = 1 Local $oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local $err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " & $err & @CRLF) Exit EndIf EndIf $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For $iLoop = 0 To UBound($aLinks) - 1 If $aLinks[$iLoop] <> $strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop], $iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName, $aLinks[$iLoop]) If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult, $hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If $SQLITE_OK And UBound($vCount) > 1 Then $vCount = $vCount[1] + 1 Else $vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then $bReadOnly = False If $bVisible = Default Then $bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows If $aResult[($iLoop * 2) + 2] > 0 Then $sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)], $sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName, $sStyle = Default) If $sStyle = Default Then $sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle, $aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2, $iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs, $sText) Local $iLoop If $iTabs > 0 Then For $iLoop = 1 To $iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ") $sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName  
    • Dimmae
      By Dimmae
      Hello,
      at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future.
      Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array.
      I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array.
      The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4.
       
      Hope you can help me, and sry again for this 'unlucky illustration'.
       
      btw: how can i add code shown as code here, instead of posting it as a attached file?.
       
       
       
      autoit-select-column.au3
      defects.xlsx
    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have an error:
      ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR  
      My code is:
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution.
      But i didn't solve the issue.
      Has anyone an idea of what the problem might be?
      Thanks in advance.