Sign in to follow this  
Followers 0
tes5884

For, Next loop ?

6 posts in this topic

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

Change this line:

For $row = 2 To $Input2
; change to this
For $row = 2 To GUICtrlRead($Input2)
1 person likes this

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

Thanks BrewManNH & JohnOne! That fixed problem 1!

Any ideas for problem 2?

Share this post


Link to post
Share on other sites

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)

1 person likes this

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

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  
Followers 0

  • Similar Content

    • ShawnW
      By ShawnW
      I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct.
      This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc.
      If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted.
      I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file.
      I've included a test excel file with a single line and test script to create a csv demonstrating the problem.
      test.xlsx
      test.au3
    • Ascer
      By Ascer
      1. Description.
      Udf working with MSDN System.Collections.ArrayList. Allow you to make fast operations on huge arrays, speed is even x10 better than basic _ArrayAdd.  Not prefered for small arrays < 600 items. 2. Requirements
      .NET Framework 1.1 - 4.5 (on this version Microsoft destroy old rules) System Windows 3. Possibilities.
      ;=============================================================================================================== ; UDF Name: List.au3 ; ; Date: 2018-02-17, 10:52 ; Description: Simple udf to create System Collections as ArrayList and make multiple actions on them. ; ; Function(s): _ListCreate -> Creates a new list ; _ListCapacity -> Gets a list size in bytes ; _ListCount -> Gets items count in list ; _ListIsFixedSize -> Get bool if list if fixed size ; _ListIsReadOnly -> Get bool if list is read only ; _ListIsSynchronized -> Get bool if list is synchronized ; _ListGetItem -> Get item on index ; _ListSetItem -> Set item on index ; ; _ListAdd -> Add item at end of list ; _ListClear -> Remove all list items ; _ListClone -> Duplicate list in new var ; _ListContains -> Get bool if item is in list ; _ListGetHashCode -> Get hash code for list ; _ListGetRange -> Get list with items between indexs ; _ListIndexOf -> Get index of item ; _ListInsert -> Insert a new item on index ; _ListInsertRange -> Insert list into list on index ; _ListLastIndexOf -> Get index last of item ; _ListRemove -> Remove first found item ; _ListRemoveAt -> Remove item in index ; _ListRemoveRange -> Remove items between indexs ; _ListReverse -> Reverse all items in list ; _ListSetRange -> Set new value for items in range ; _ListSort -> Sort items in list (speed of reading) ; _ListToString -> Get list object name ; _ListTrimToSize -> Remove unused space in list ; ; Author(s): Ascer ;=============================================================================================================== 4. Downloads
      List.au3 5. Examples
      SpeedTest _ArrayAdd vs ListAdd SpeedTest ArraySearch vs ListIndexOf Basic usage - crating guild with members  
    • ronmage
      By ronmage
      So I have a loop that keeps reading data from an array and searching it for the same value. If the value is no there it does work then adds the value to the array to prevent it from doing the same work.
      If _ArraySearch($ID,$filearray[$i]) = -1 Then Work.... _ArrayAdd($ID,$filearray[$i]) EndIf This is in a for loop hence $i
      So what is happening is the code works great for several hours. After a period of time _ArraySearch($ID,$filearray[$i]) will result in -1 even if $ID = $filearray. So it ready as if there is no data in the array. Anyone have this problem? 
       
      Also I am just running in using F5 not compiling it and running it if that makes a difference.
       
    • Robdog1955
      By Robdog1955
      I'm trying to click a button on a web page. I have added a couple of MsgBox lines to allow me to watch what happens on the page. As you can see the first half of my script enters data into text boxes on the page. I have no problem there. I just cannot click on the region buttons. The "set focus" line causes an outline to appear around the EU button and the "click button" line causes the "Pick a Region" text to disappear. Here is the code I have so far.
      #include <IE.au3> Local $oIE = _IECreate("http://questchecker.com/") Local $iQuestID = "123456" Local $sCharacterName = "CharacterName" Local $colForms = _IEFormGetCollection($oIE) $iCount = 0 For $oForm In $colForms $oFormElements = _IEFormElementGetCollection($oForm) For $oFormElement In $oFormElements $iCount = $iCount + 1 Local $sTagName = StringLower($oFormElement.tagName) Local $sElementType = $oFormElement.type Local $sElementName = $oFormElement.name Switch $iCount Case 6 _IEFormElementSetValue($oFormElement, "MyRealm", 0) ; realm Case 7 _IEFormElementSetValue($oFormElement, $sCharacterName, 0) Case 8 _IEFormElementSetValue($oFormElement, $iQuestID, 0) EndSwitch Next Next Local $oButtons = _IEGetObjByName($oIE, "questForm") For $oButton In $oButtons If _IEFormElementGetValue($oButton) = "US" Then MsgBox(0, "", "Click Okay to set focus") _IEAction($oButton, "focus") MsgBox(0, "", "Click Okay to click button") _IEAction($oButton, "click") ExitLoop EndIf Next MsgBox(0, "", "Click Okay to quit") _IEQuit($oIE) Exit  
    • Dent
      By Dent
      As the title says. I open IE11 and navigate to a page with the following HTML5 file upload container in Capture0.JPG
      I want to click Add Files but I haven't been able to. Here's the code that handles the Add Files button in Capture.JPG
      _IEGetObjectById and then using _IEAction to click it doesn't work. I can't give access to the actual page unfortunately because it's password protected.
      All suggestions welcome.