Jump to content
Sign in to follow this  
tes5884

For, Next loop ?

Recommended Posts

tes5884

Hi Guys,

I'm in the process of writing a script that takes the street address and zip from a spreadsheet and inputs it into NYS TAX site. Then it extracts the tax code and puts it into the table next to the appropriate address.

I think I wrote most of it (with lot's of help from you guys!!). However, 2 problems remain.

  • No matter how many rows I specify in the GUI (or for loop) it only loops about 5 times.

  • It writes the first result to the first row, even if the first row returned a error. So it will write the result for the second row, on the first row.
Sorry, if i'm not being clear.

Any help/suggestions/criticism is appreciated, I'm here to learn..

Thanks guys!

#include <Excel.au3>
#include <IE.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Array.au3>

$Form1 = GUICreate("TaxCodes", 316, 157, -1, -1, -1, BitOR($WS_EX_ACCEPTFILES, $WS_EX_WINDOWEDGE))
$Label1 = GUICtrlCreateLabel("Major Energy 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)
$Label4 = GUICtrlCreateLabel("Tzvi Spitz - v1 - July '12", 100, 138, 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
$oIE = _IECreate()
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, 1)
For $row = 2 To $Input2
     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, "http://www8.tax.ny.gov/UTLR/utlrHome")
     $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
     Beep(500)
     Else ;if succesfull write output to excel
     $aJurisdictionCodes = StringRegExp(_IEBodyReadText($oIE), 'Jurisdiction code:(.*)', 1)
     _ExcelWriteArray($oExcel, 2, 23, $aJurisdictionCodes, 1, 0)
     ;_ExcelBookSave($oExcel)
     $o_form2 = _IEFormGetObjByName($oIE, "utlrHome")
     _IEFormSubmit($o_form2) ;returns to original form
     EndIf
Next
_ExcelBookClose($oExcel)
EndIf
EndSwitch
WEnd

Share this post


Link to post
Share on other sites
BrewManNH

Change this line:

For $row = 2 To $Input2
; change to this
For $row = 2 To GUICtrlRead($Input2)
  • Like 1

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
JohnOne

I dont know anything about excel or the UDF but with a glance over this might need changing.

_ExcelWriteArray($oExcel, 2, 23, $aJurisdictionCodes, 1, 0)

To

_ExcelWriteArray($oExcel, $row, 23, $aJurisdictionCodes, 1, 0)

  • Like 1

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites
tes5884

I dont know anything about excel or the UDF but with a glance over this might need changing.

_ExcelWriteArray($oExcel, 2, 23, $aJurisdictionCodes, 1, 0)

To

_ExcelWriteArray($oExcel, $row, 23, $aJurisdictionCodes, 1, 0)

JohnOne you're the man!! Thanks that fixed it!!!

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  

  • Similar Content

    • SkysLastChance
      By SkysLastChance
      I am trying to grab the id "in2xk_26" however it the characters before the underscore always change. (in the name too)
      Is there a way I can find a id or name by the last 3 charcters?
      Using something like "stringright?"
      So I would want to search for just "_26" in this case.
      This is assuming that there are no other _26
       

      #include <Excel.au3> #include <IE.au3> #include <GUIConstantsEx.au3> Global $iMousespeed = 25,$target = "",$TagName = "",$Value = "",$Atrribute = "" $oIE = _IEAttach("MEDITECH") $TagName = "input" $Value = "in2xk_26" $Attribute = "id" $tags = $oIE.document.GetElementsByTagName($TagName) ;TagName ------ MAKE SURE TO NAME THESE For $tag in $tags $class_value = $tag.GetAttribute($Attribute) ;Attribute ------ MAKE SURE TO NAME THESE If string($class_value) = $Value Then ;Value ------ MAKE SURE TO NAME THESE $target = $tag $iScreenX = _IEPropertyGet($target, "screenx") $iScreenY = _IEPropertyGet($target, "screeny") $iWidth = _IEPropertyGet($target, "width") $iHeight = _IEPropertyGet($target, "height") $oMouseCords = MouseMove($iScreenX + $iWidth / 2, $iScreenY + $iHeight / 2, $iMousespeed) MouseClick($MOUSE_CLICK_LEFT) ExitLoop EndIf Next  
       
    • SkysLastChance
      By SkysLastChance
      I was wondering how I read data inside of a iframe. I would like to be able to click something inside a iframe. However, I can't even find the tag. 
      The id and name change all the time so I can't use those. 
      The code highlighted in blue is what I am trying to click. (second picture)
      Here is what I have tried.
      $target = "" $tags = $oIE.document.GetElementsByTagName("div") For $tag in $tags $class_value = $tag.GetAttribute("class") If string($class_value) = "s_92 altstyle s_93 s_94" Then $target = $tag ConsoleWrite("Tag Found " & $target.outerText&@CRLF) ExitLoop EndIf Next  

       
       

    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I need to open excel files that may or may not need a password and finally move the files that needs password to manual queue.
      Is there a fastest way to do this?
       
      PS: I have a huge respect for the rules of this forum. I am not asking assistance to override any security measure. I just need to segregate the files that needs passwords.
    • SkysLastChance
      By SkysLastChance
       
      WinActivate("MEDITECH - Internet Explorer") Sleep (500) $oIE = _IEAttach("MEDITECH") $oDiv1 = _IEGetObjById($oIE, "sysmenu-searchbarbutton") _IEAction($oDiv1, "click") I am just trying to click the little magnifying glass, next to the gear button with no luck. I was hoping someone might have an idea why this is not working?
       

    • Iceburg
      By Iceburg
      Hi everyone, I'm at best a noobie.  I have read through the Array helps, and specifically the 2D array help file, and I'm struggling to get my code working.
      I have an array that is read from a file, thats working great.  I'm trying to do some math on the array, so I can find the largest, average, lowest, day over day change %, etc.
      The array read working fine, I get 43 lines, line 0 is 44, and then I get data that looks like
      0519 $10,000
      0520 $10,001
      0521 $10,002
      The data in this array is a single 1D array, breaking it out into 2 columns so I can do the math is what I can get to happen.  
      How do I reference the array to store this data?  Second, how do I assign this data to the appropriate row/column?
      Thanks in advance.
      Dim $all_cash_amounts[UBound($aInput)][2] Dim $max_amount_in_account Dim $min_amount_in_account _FileReadToArray($LC_Check_file_path, $aInput) _ArrayDisplay($aInput) local $date = StringRegExp($aInput[1], "(\d\d\d\d)", 1) local $cash = StringRegExp($aInput[1], "\d+\s(-?[0-9\.\,]+)", 1) ConsoleWrite("Date is: " & $date & @CRLF) For $i = 1 To UBound($aInput)-1     $date = StringRegExp($aInput[$i], "(\d\d\d\d)", 1)     $all_cash_amounts[$i][2] = $date[$i][0], $cash[$i][1]      Next _ArrayDisplay($all_cash_amounts)  
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.