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
tes5884

Thanks BrewManNH & JohnOne! That fixed problem 1!

Any ideas for problem 2?

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

    • Skeletor
      By Skeletor
      Hi Virtual People,
      My array works perfectly fine. However, what is the best practice if the line in the array doesn't have the correct amount of columns and if I can add a placeholder?

       
      For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") Next  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
×