Jump to content

Sometimes it'll work, sometimes it'll crash

SLip023
 Share

Recommended Posts

SLip023 Seeker

SLip023

Posted
Posted

Hi all,

I'm fairly new to AutoIt, and this is my first time posting in the forums. So far all my issues have been resolved using Google search, but this time around I don't understand why my script will sometimes run, and sometimes give me the error: 

"C:\Users\MyUser\Desktop\Fed Bal FTM\Fed Bal FTM.au3" (134) : ==> The requested action with this object has failed.:
$sBatchAmount[0] = $oExcel.Application.Sheets("Batch").Range("E12").Text
$sBatchAmount[0] = $oExcel.Application^ ERROR

What this script does is get data from the opened Excel sheet, and enters it in a website. Here's the code: 

#include <IE.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include "MetroGUI-UDF\MetroGUI_UDF.au3"
#include "MetroGUI-UDF\_GUIDisable.au3"
Opt("WinTitleMatchMode", 2)

#Region Variables
Global $sBatchAmount[4], $sDesc[8], $sToday, $oExcel, $oIE

$sToday = @MON & "-" & @MDAY & "-" & @YEAR

#EndRegion Variables

;ESC key will stop this bot
HotKeySet("{ESC}", "_Exit")
Func _Exit()
Exit
EndFunc ;==>_Exit

_Metro_EnableHighDPIScaling()
_SetTheme("DarkTeal")

#Region Main GUI
$GUIThemeColor = 0x1d1d1d
$Form1 = _Metro_CreateGUI("Fed Bal FTM", 310, 175, -1, -1, True)
$ButtonBKColor = 0x603cba
$Button1 = _Metro_CreateButtonEx2("GO", 120, 110, 80, 30)
$Label1 = GUICtrlCreateLabel("Fed Bal FTM", 50, 7, 90, 17)
GUICtrlSetFont(-1, 10, 400, 0, "Segoe UI")
GUICtrlSetColor(-1, 0x603cba)
$Checkbox1 = _Metro_CreateCheckbox("493", 60, 48, 97, 24)
GUICtrlSetFont(-1, 11, 400, 0, "Segoe UI")
$Checkbox2 = _Metro_CreateCheckbox("513", 172, 48, 97, 24)
GUICtrlSetFont(-1, 11, 400, 0, "Segoe UI")
$Control_Buttons = _Metro_AddControlButtons(True, False, True, False, True) ;CloseBtn = True, MaximizeBtn = True, MinimizeBtn = True, FullscreenBtn = True, MenuBtn = True
$GUI_CLOSE_BUTTON = $Control_Buttons[0]
$GUI_MINIMIZE_BUTTON = $Control_Buttons[3]
$GUI_MENU_BUTTON = $Control_Buttons[6]
GUISetState(@SW_SHOW)
#EndRegion Main GUI

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE, $GUI_CLOSE_BUTTON
            Exit
        Case $Form1
        Case $GUI_MINIMIZE_BUTTON
            GUISetState(@SW_MINIMIZE, $Form1)
        Case $Button1
            _Metro_DisableButton($Button1)
            If _Metro_CheckboxIsChecked($Checkbox1) Then
                Batch493()
            ElseIf _Metro_CheckboxIsChecked($Checkbox2) Then
                Batch513()
            Else
                Sleep(1)
            EndIf
            _Metro_EnableButton($Button1)
        Case $Checkbox1
            If _Metro_CheckboxIsChecked($Checkbox1) Then
                _Metro_CheckboxUnCheck($Checkbox1)
            Else
                _Metro_CheckboxCheck($Checkbox1)
                _Metro_CheckboxUnCheck($Checkbox2)
            EndIf
        Case $Checkbox2
            If _Metro_CheckboxIsChecked($Checkbox2) Then
                _Metro_CheckboxUnCheck($Checkbox2)
            Else
                _Metro_CheckboxCheck($Checkbox2)
                _Metro_CheckboxUnCheck($Checkbox1)
            EndIf
        Case $GUI_MENU_BUTTON
            Local $MenuButtonsArray[2] = ["About", "Exit"]
            Local $MenuSelect = _Metro_MenuStart($Form1, 50, $MenuButtonsArray)
            Switch $MenuSelect ;Above function returns the index number of the selected button from the provided buttons array.
                Case "0"
                Case "1"
                    Exit
            EndSwitch
    EndSwitch
WEnd
Exit

Func Batch493()
    $oExcel = _Excel_BookAttach("ACH FED Balancing Bank 493 " & $sToday & ".xlsx", "filename")
    $sBatchAmount[0] = $oExcel.Application.Sheets("Batch").Range("E12").Text
    $sBatchAmount[1] = $oExcel.Application.Sheets("Batch").Range("E14").Text
    $sBatchAmount[2] = $oExcel.Application.Sheets("Batch").Range("E16").Text
    $sBatchAmount[3] = $oExcel.Application.Sheets("Batch").Range("E18").Text
    For $i = 0 To 4 - 1 Step 1
        $sBatchAmount[$i] = StringStripWS($sBatchAmount[$i], $STR_STRIPLEADING +  $STR_STRIPTRAILING)
        $sBatchAmount[$i] = StringReplace($sBatchAmount[$i], ",", "")
    Next
    $sDesc[0] = $oExcel.Application.Sheets("Batch").Range("G12").Text
    $sDesc[1] = $oExcel.Application.Sheets("Batch").Range("G13").Text
    $sDesc[2] = $oExcel.Application.Sheets("Batch").Range("G14").Text
    $sDesc[3] = $oExcel.Application.Sheets("Batch").Range("G15").Text
    $sDesc[4] = $oExcel.Application.Sheets("Batch").Range("G16").Text
    $sDesc[5] = $oExcel.Application.Sheets("Batch").Range("G17").Text
    $sDesc[6] = $oExcel.Application.Sheets("Batch").Range("G18").Text
    $sDesc[7] = $oExcel.Application.Sheets("Batch").Range("G19").Text
    WinActivate("Process Financial Transactions")
    $oIE = _IEAttach("Process Financial Transactions")
    ControlClick("Process Financial Transactions", "", "", "", 1, 75)
    Send("{HOME}")
    _Send($oIE, "trans_amt_17080_i1_p1_activity", $sBatchAmount[0])
    _Send($oIE, "gl_desc_17087_i1_p1_activity", $sDesc[0])
    _Send($oIE, "trans_amt_17080_i1_p2_activity", $sBatchAmount[0])
    _Send($oIE, "gl_desc_17087_i1_p2_activity", $sDesc[1])
    _Send($oIE, "trans_amt_17080_i1_p3_activity", $sBatchAmount[1])
    _Send($oIE, "gl_desc_17087_i1_p3_activity", $sDesc[2])
    _Send($oIE, "trans_amt_17080_i1_p4_activity", $sBatchAmount[1])
    _Send($oIE, "gl_desc_17087_i1_p4_activity", $sDesc[3])
    _Send($oIE, "trans_amt_17080_i1_p5_activity", $sBatchAmount[2])
    _Send($oIE, "gl_desc_17087_i1_p5_activity", $sDesc[4])
    _Send($oIE, "trans_amt_17080_i1_p6_activity", $sBatchAmount[2])
    _Send($oIE, "gl_desc_17087_i1_p6_activity", $sDesc[5])
    _Send($oIE, "trans_amt_17080_i1_p7_activity", $sBatchAmount[3])
    _Send($oIE, "gl_desc_17087_i1_p7_activity", $sDesc[6])
    _Send($oIE, "trans_amt_17080_i1_p8_activity", $sBatchAmount[3])
    _Send($oIE, "gl_desc_17087_i1_p8_activity", $sDesc[7])
EndFunc ;==>Batch493

Func Batch513()
    $oExcel = _Excel_BookAttach("ACH FED Balancing Bank 513 " & $sToday & ".xlsx", "filename")
    $sBatchAmount[0] = $oExcel.Application.Sheets("Batch").Range("E12").Text
    $sBatchAmount[1] = $oExcel.Application.Sheets("Batch").Range("E14").Text
    $sBatchAmount[2] = $oExcel.Application.Sheets("Batch").Range("E16").Text
    $sBatchAmount[3] = $oExcel.Application.Sheets("Batch").Range("E18").Text
    For $i = 0 To 4 - 1 Step 1
        $sBatchAmount[$i] = StringStripWS($sBatchAmount[$i], $STR_STRIPLEADING +  $STR_STRIPTRAILING)
        $sBatchAmount[$i] = StringReplace($sBatchAmount[$i], ",", "")
    Next
    $sDesc[0] = $oExcel.Application.Sheets("Batch").Range("G12").Text
    $sDesc[1] = $oExcel.Application.Sheets("Batch").Range("G13").Text
    $sDesc[2] = $oExcel.Application.Sheets("Batch").Range("G14").Text
    $sDesc[3] = $oExcel.Application.Sheets("Batch").Range("G15").Text
    $sDesc[4] = $oExcel.Application.Sheets("Batch").Range("G16").Text
    $sDesc[5] = $oExcel.Application.Sheets("Batch").Range("G17").Text
    $sDesc[6] = $oExcel.Application.Sheets("Batch").Range("G18").Text
    $sDesc[7] = $oExcel.Application.Sheets("Batch").Range("G19").Text
    WinActivate("Process Financial Transactions")
    $oIE = _IEAttach("Process Financial Transactions")
    ControlClick("Process Financial Transactions", "", "", "", 1, 75)
    Send("{HOME}{PGDN}{PGDN}{DOWN}")
    _Send($oIE, "trans_amt_17080_i1_p9_activity", $sBatchAmount[0])
    _Send($oIE, "gl_desc_17087_i1_p9_activity", $sDesc[0])
    _Send($oIE, "trans_amt_17080_i1_p10_activity", $sBatchAmount[0])
    _Send($oIE, "gl_desc_17087_i1_p10_activity", $sDesc[1])
    _Send($oIE, "trans_amt_17080_i1_p11_activity", $sBatchAmount[1])
    _Send($oIE, "gl_desc_17087_i1_p11_activity", $sDesc[2])
    _Send($oIE, "trans_amt_17080_i1_p12_activity", $sBatchAmount[1])
    _Send($oIE, "gl_desc_17087_i1_p12_activity", $sDesc[3])
    _Send($oIE, "trans_amt_17080_i1_p13_activity", $sBatchAmount[2])
    _Send($oIE, "gl_desc_17087_i1_p13_activity", $sDesc[4])
    _Send($oIE, "trans_amt_17080_i1_p14_activity", $sBatchAmount[2])
    _Send($oIE, "gl_desc_17087_i1_p14_activity", $sDesc[5])
    _Send($oIE, "trans_amt_17080_i1_p15_activity", $sBatchAmount[3])
    _Send($oIE, "gl_desc_17087_i1_p15_activity", $sDesc[6])
    _Send($oIE, "trans_amt_17080_i1_p16_activity", $sBatchAmount[3])
    _Send($oIE, "gl_desc_17087_i1_p16_activity", $sDesc[7])
EndFunc;==>Batch513

#Region MyFunctions ===================================================================
Func _Click($Tab, $ObjIdOrName)
    $Obj = _IEGetObjById($Tab, $ObjIdOrName)
    _IEAction($Obj, "click")
    $Obj = 0
EndFunc ;==>_Click

Func _Send($Tab, $ObjIdOrName, $Text)
    $Obj = _IEGetObjById($Tab, $ObjIdOrName)
    _IEFormElementSetValue($Obj, $Text)
    $Obj = 0
EndFunc ;==>_Send
#EndRegion MyFunctions ===================================================================

Exit

 

Any help is appreciated. Also, any code critique is welcome too! I love to make my code as efficient as possible.

Link to comment
Share on other sites
SLip023 Seeker

SLip023

Posted
  • Author
Posted
11 hours ago, Danp2 said:

You should add some error checking after the call to _Excel_BookAttach.

You mean like if Attach errors, try again and keep trying? I don't understand why it crashes about 20% of the time

Link to comment
Share on other sites
Subz Universalist

Subz

Posted
Posted

You need to know why its crashing which is where the error checking comes in handy, in your instance above it could be one of two see _Excel_BookAttach, so use something like below to find out where the error is: 

$oExcel = _Excel_BookAttach("ACH FED Balancing Bank 513 " & $sToday & ".xlsx", "filename")
Switch @error
    Case 1
        MsgBox("Error", "Error : " & @error & " - An error occurred or $sString can't be found in any of the open workbooks. @extended is set to the COM error code" & @CRLF & "Extended Error: " & @extended)
    Case 2
        MsgBox("Error", "Error : " & @error & "$sMode is invalid")
EndSwitch

 

Link to comment
Share on other sites
SLip023 Seeker

SLip023

Posted
  • Author
Posted
11 hours ago, Subz said:

You need to know why its crashing which is where the error checking comes in handy, in your instance above it could be one of two see _Excel_BookAttach, so use something like below to find out where the error is: 

$oExcel = _Excel_BookAttach("ACH FED Balancing Bank 513 " & $sToday & ".xlsx", "filename")
Switch @error
    Case 1
        MsgBox("Error", "Error : " & @error & " - An error occurred or $sString can't be found in any of the open workbooks. @extended is set to the COM error code" & @CRLF & "Extended Error: " & @extended)
    Case 2
        MsgBox("Error", "Error : " & @error & "$sMode is invalid")
EndSwitch

 

Thanks for the suggestion. I tried it but I'm still getting the same error message randomly and none of your MsgBoxes are popping up, so I think the error is not coming from the _Excel_BookAttach. Other times the application stops responding and Windows closes it and sometimes it just works...

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...