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

    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • ternal
      By ternal
      Hi,
      Recently I have had the need to do a sort and then do a second sort while the item of the first sort stays the same ( double sorting , first on column x then while column x is the same sort column y).
      I did not put much efffort into error checking but so far I did not need it.
      For my applications so far it works perfectly however if someone is willing I want to test this extensivly.
      If anyone has big lists of random stuff to sort could you try this out please?
      #include <Array.au3> ; #FUNCTION# ==================================================================================================================== ; Name ..........: _ArraySort_Double ; Description ...: ; Syntax ........: _ArraySort_Double (Byref $array[, $first_index = Default[, $second_index = Default[, $ascending = Default]]]) ; Parameters ....: $array - 2d array to sort. ; $first_index - [optional] first column to sort. Default is 0. ; $second_index - [optional] second column to sort. Default is 1. ; $ascending - [optional] ascending/descending. Default is 1. ; Return values .: 1 if no errors occured , -1 if errors occured ; Author ........: Ternal ; Remarks .......: Needs excessive testing. ; Related .......: _arraysort() ; =============================================================================================================================== Func _ArraySort_Double (byref $array, $first_index = Default, $second_index = Default, $ascending = Default) Local $temp_value Local $counter = 1 If UBound($array, $UBOUND_DIMENSIONS) <> 2 Then MsgBox(0, "error", "error") return -1 EndIf If $first_index = Default Then $first_index = 0 If $second_index = Default Then $second_index = 1 If $ascending = Default Then $ascending = 1 _ArraySort($array, $ascending, 0, 0, $first_index); you can alter settings of primary sort here If @error Then MsgBox(0, "error", @error) return -1 EndIf $temp_value = $array[0][$first_index] For $x = 1 to UBound($array, 1) - 1 If Mod( $x, 10000) = 0 Then ConsoleWrite("at " & $x & " of a total : " & UBound($array, 1) & @CRLF) If $array[$x][$first_index] = $temp_value Then $counter+= 1 If $x = UBound($array, 1) - 1 Then; do last line here(if last line is not a new item) _ArraySort($array, $ascending, $x - $counter, $x, $second_index);you can alter settings of secondary sort here(don't forget to place line 34 the exact same) If @error Then MsgBox(0, "error", @error) return -1 EndIf EndIf Else If $counter > 0 Then ;at least 2 of the same _ArraySort($array, $ascending, $x - $counter, $x - 1, $second_index);you can alter settings of secondary sort here(don't forget to place line 29 the exact same) If @error Then MsgBox(0, "error", @error) return -1 EndIf $counter = 1 EndIf EndIf $temp_value = $array[$x][$first_index] Next Return 1 EndFunc Kind regards, Ternal
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
    • tuffgong
      By tuffgong
      Good morning. I have a system I am trying to automate that works like this: user fills a column in an Excel spreadsheet with values they would like printed and saves it to a folder on their desktop, they start the script and it formats their data into a text file (adding a prefix) and sends the text file as a .bch file where it needs to go. This is working:
      #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) barcodePrint() Func barcodePrint() $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) FileOpen($sFilename, $FO_OVERWRITE) ;Global $oWorkbook = _Excel_BookAttach($oExcel) Global $sSTCArray = _Excel_RangeRead($Read) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, "!StaticShelving1x3_ZPL," & $sSTCArray[$i]) Next _Excel_Close($oExcel) FileMove($sFilename, "***file path***\print.bch") EndFunc However, it only works if the user first formats the spreadsheet to text. I want to automate that. From what I have read it appears AutoIt does not like formatting cells that already have values. True? Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file? I have also considered opening a second spreadsheet, formatting it, and copying the values over. Like this:
        
      Global $sPrefix Global $oPath Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) Global $aArray Global $bExcel _Excel_BookNew($bExcel) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 $aArray = IniReadSection("***File path***\barcode.ini", "stc/rvt/mgm") $sPrefix = $aArray[1][1] $oPath = $aArray[2][1] barcodePrint() EndSwitch WEnd Func barcodePrint() $oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx") $oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@" Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) $oCopy = _Excel_RangeRead($sWorkbook) _Excel_RangeWrite($oWorkbook, Default, $oCopy) FileOpen($sFilename, $FO_OVERWRITE) $sSTCArray = _Excel_RangeRead($Read,"Default","Default",3) ;_ArrayDisplay($sSTCArray) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, $sPrefix & $sSTCArray[$i]) Next _Excel_Close($oExcel) ;FileMove($sFilename, $oPath) Exit EndFunc This does not like the formatting of $oWorkbook: "Variable must be of type 'Object'".  Do I need this second sheet? If so, how can I format it? Is there a better way to get the Excel values into a .txt file? Any ideas would be appreciated. Thanks!
    • TrashBoat
      By TrashBoat
      So I've made this script that detects how long i have held down my left mouse button for and stores the information in an array and then sorts its using _ArraySort but the output is half sorted half broken.
      Here's my script:
      HotKeySet("{F1}","_exit") #include <Misc.au3> #include <Timers.au3> #include <Array.au3> Local $dll = DllOpen("user32.dll") $on = False Global $array[0] While(1) If _IsPressed(01,$dll) Then $timer = _Timer_Init() While _IsPressed(01,$dll) Sleep(1) WEnd $time = _Timer_Diff($timer) _ArrayAdd($array,"Time: " & Floor($time) & " ms") ;~ ConsoleWrite("Time: " & Floor($time) & " ms" & @CRLF) EndIf Sleep(50) WEnd Func _exit() _ArraySort($array) _ArrayDisplay($array) Exit EndFunc And the output:

      See how its not sorted?  What is the problem here?
×