3 replies to this topic

[tes5884]

Hi Guys,
I made a script that runs a loop through an excel sheet, extracts info, fills in a web page, submits. Then it extracts the results and puts it back into the spreadsheet.

I tried adding a progress bar, and it seems to update only through 100.
Meaning, if I input 5 rows, it will only show ~5% of the progress bar. If I do 300 rows, it fills the bar and continues working.

Seems like I didn't give GUICtrlSetData the right parameters.

Any suggestions is greatly appreciated.

Plain Text        expandcollapse  popup

#Obfuscator_Parameters=/mergeonly
#include <Excel.au3>
#include <IE.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
#include <ProgressConstants.au3>

$Form1 = GUICreate("TaxCodes", 316, 200, -1, -1, -1, BitOR($WS_EX_ACCEPTFILES, $WS_EX_WINDOWEDGE))
$Label1 = GUICtrlCreateLabel("Tax Code Retriever", 16, 16, 283, 20)
GUICtrlSetFont(-1, 12, 800, 4, "MingLiU")
$file = GUICtrlCreateInput("", 16, 56, 225, 21)
$Input2 = GUICtrlCreateInput("", 16, 107, 49, 21)
$Label2 = GUICtrlCreateLabel("Source file", 16, 39, 54, 17)
$Label3 = GUICtrlCreateLabel("Amount of rows", 17, 88, 77, 17)
$Button1 = GUICtrlCreateButton("Browse", 256, 56, 49, 25)
$Button2 = GUICtrlCreateButton("Process", 113, 105, 89, 25)
$prgrs = GUICtrlCreateProgress(20, 138, 275, 20, $PBS_SMOOTH)
$Label4 = GUICtrlCreateLabel("Tzvi Spitz - v1 - July '12", 100, 178, 115, 17)
GUICtrlSetFont(-1, 5, 400, 0, "MS Sans Serif")
GUISetState(@SW_SHOW)

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
  Case $GUI_EVENT_CLOSE
   Exit
   ;Browse to select excel sheet
  Case $Button1
   $sfile = FileOpenDialog("Select enrollment file..", @WindowsDir & "\", "Excel File (*.csv;*.xlsx)", 1 + 4)
   GUICtrlSetData($file, $sfile)

  Case $Button2
   FileCopy($sfile, $sfile & ".bak", 1)
   $oIE = _IECreate(0, 0, 0)
   If $sfile = "" Then
    MsgBox(0, "", "Error!" & @CRLF & "You need to input the proper info!")
    Exit
   Else
    ;open sheet read info to vars
    Local $oExcel = _ExcelBookOpen($sfile, 0)
    For $row = 2 To GUICtrlRead($Input2)
     GUICtrlSetData($prgrs, $row)
     Local $strt = _ExcelReadCell($oExcel, $row, 3)
     Local $zip = _ExcelReadCell($oExcel, $row, 6)
     Local $type = _ExcelReadCell($oExcel, $row, 16)
     ;Open IE instance, get elements
     _IENavigate($oIE, "<a href='http://www8.tax.ny.gov/UTLR/utlrHome' class='bbc_url' title='External link' rel='nofollow external'>http://www8.tax.ny.gov/UTLR/utlrHome"</a>)
     $o_form = _IEFormGetObjByName($oIE, "UTLRForm")
     $o_addr = _IEFormElementGetObjByName($o_form, "UTLR_STREETADDRESS_KEY")
     $o_zip = _IEFormElementGetObjByName($o_form, "UTLR_ZIPCODE_KEY")
     $o_type = _IEFormElementGetObjByName($o_form, "UTLR_SERVICETYPE_KEY")

     ; Set IE field values and submit the form
     _IEFormElementSetValue($o_addr, $strt)
     _IEFormElementSetValue($o_zip, $zip)
     If $type = "Commercial" Then
      _IEFormElementSetValue($o_type, "Commercial energy services")
     Else
      _IEFormElementSetValue($o_type, "Residential energy services")
     EndIf
     _IEFormSubmit($o_form)
     _IELoadWait($oIE)
     ;if not successfull
     If StringRegExp(_IEBodyReadText($oIE), 'No matches were found for the address you entered.') Then
      _ExcelWriteCell($oExcel, "ERROR", $row, 23)
     Else ;if succesfull write output to excel
      $aJurisdictionCodes = StringRegExp(_IEBodyReadText($oIE), 'Jurisdiction code:(.*)', 1)
      _ExcelWriteArray($oExcel, $row, 23, $aJurisdictionCodes, 1, 0)
      $o_form2 = _IEFormGetObjByName($oIE, "utlrHome")
      _IEFormSubmit($o_form2)
     EndIf
    Next
    _ExcelBookSave($oExcel)
    _ExcelBookClose($oExcel)
    _IEQuit($oIE)
   EndIf
   MsgBox(0, "yawn", "All done here, G'nite!")
   Exit
EndSwitch
WEnd

[BrewManNH]

You need to use a percentage and not an absolute value. Try this change

     Local $Percent = ($row / GUICtrlRead($Input2)) * 100 ; add this line
     GUICtrlSetData($prgrs, $Percent) ; change this line

[tes5884]

You need to use a percentage and not an absolute value. Try this change

     Local $Percent = ($row / GUICtrlRead($Input2)) * 100 ; add this line
     GUICtrlSetData($prgrs, $Percent) ; change this line

Thanks BrewManNH that fixed it!

By now you probably know my project better then myself

[Zedna]

This is antiflicker optimization important especially when there are many rows (loops)
In such case there should be done updating of progressbar only when percent (round to 0) is changed:

Global $Percent, $Percent_prev
...

                 $Percent = Round(($row / GUICtrlRead($Input2)) * 100, 0)
                    If $Percent <> $Percent_prev Then
                        GUICtrlSetData($prgrs, $Percent)
                        $Percent_prev = $Percent
                    EndIf