Sign in to follow this  
Followers 0
JayEmmet

List loop to IE form and returne to next empty row in Excel

12 posts in this topic

#1 ·  Posted (edited)

This is my first post.

I have been trying to make this program to loop through a list of license numbers (in a text file or Excel column A:A) and grab the information contained in a table on the website.

I have been able to get it to work for (hard coded sample numbers) but am not able to get it to grab each number in a list, Nor can I figure out how to dump each search result into the next empty row in excel before continuing to the next number to search for. Any help would be appreciated.

CODE
#cs ----------------------------------------------------------------------------

AutoIt Version: 3.3.0.0

Author: Jay

Script Function:

Template AutoIt script.

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here

; How do I loop this?

; How do I speed this up?

; Can I hide the IE window?

#include <IE.au3>

#include <Array.au3>

#include <ExcelCom.au3>

;#include <Excel.au3>

; -----OPEN IE & NAVIGATE TO STARTING WEB PAGE-----------------------------------------------

; The program will start by going to the main velocityhall website and

; navigating to the "Search by License Number" form

$oIE =_IECreate ("http://www.velocityhall.com/velohall/index.cfm?STATE=MICHIGAN",0,1, 1, 1)

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "Check / Research Permits")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

; <SECTION END>

;------INITIATE MASTERNUMBER LOOKUP LOOP--------------------------------------------------------------------

; At this point the program should get the list "Master License Numbers" from ("MasterNumber.txt")

; and load it into an array.

; I HAVE TO BUILD THIS CODE SECTION BUT DON'T KNOW HOW TO YET

; <SECTION END>

;-----ENTER THE LICENSE NUMBER TO SEARCH FOR--------------------------------------------------

; The program shoulf loop through each number in the list and perform the following:

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

_IEFormElementSetValue ($oText, "6202967"); I have to figure out how to get this

;number from an Excel range

$oBotton = _IEGetObjByName($oForm, "Submit")

_IEAction($oBotton, "click")

; <SECTION END>

;------GET THE RETURNED DATA TABLE INFO INTO AN ARRAY-----------------------------------------------------------------------------------

$oFrame = _IEFrameGetObjByName ($oIE,"iFrameTwo")

$sHTML = _IEDocReadHTML($oIE)

$oTable = _IETableGetCollection($oIE,11)

$aData = _IETableWriteToArray($oTable, True)

;_ArrayDisplay($aData)

; I have to figure out how to get this array into Excel Sheet

; Read the table cells into a 2-D array

;$XLArray = _IETableWriteToArray ($oTable)

; Close the browser window

;_IEQuit ($oIE)

; <SECTION END>

;----------------------------------------------------------------------------------------

;----To Excel----------------------------------------------------------------------------

;----this works but not exactly what i want------

$sFilePath = @ScriptDir & "\temp.xls"

; Create a blank excel file

_XLCreateBlank ($sFilePath)

; Write the array to the excel file

_XLArrayWrite ($aData, $sFilePath, 1, 1, 5, 1)

Sleep(3000)

MsgBox(0, "", "The excel file will now close.")

; Close the excel file

_XLExit ($sFilePath)

;-------------------------

;-------NAVIGATE BACK TO SEARCH FORM FOR THE NEXT NUMBER----------------------------------------------------

_IELinkClickByText ($oIE, "Back")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

;---------------------------------------------------------------------------------------------

_IEFormElementSetValue ($oText, "6202968") ;I have to figure out how to get this

;"Next number" from the next row in the Excel range

;IEQuit($o_IE)

Edited by JayEmmet

Share this post


Link to post
Share on other sites



This is my first post.

I have been trying to make this program to loop through a list of license numbers (in a text file or Excel column A:A) and grab the information contained in a table on the website.

I have been able to get it to work for (hard coded sample numbers) but am not able to get it to grab each number in a list, Nor can I figure out how to dump each search result into the next empty row in excel before continuing to the next number to search for. Any help would be appreciated.

CODE
#cs ----------------------------------------------------------------------------

AutoIt Version: 3.3.0.0

Author: Jay

Script Function:

Template AutoIt script.

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here

; How do I loop this?

; How do I speed this up?

; Can I hide the IE window?

#include <IE.au3>

#include <Array.au3>

#include <ExcelCom.au3>

;#include <Excel.au3>

; -----OPEN IE & NAVIGATE TO STARTING WEB PAGE-----------------------------------------------

; The program will start by going to the main velocityhall website and

; navigating to the "Search by License Number" form

$oIE =_IECreate ("http://www.velocityhall.com/velohall/index.cfm?STATE=MICHIGAN",0,1, 1, 1)

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "Check / Research Permits")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

; <SECTION END>

;------INITIATE MASTERNUMBER LOOKUP LOOP--------------------------------------------------------------------

; At this point the program should get the list "Master License Numbers" from ("MasterNumber.txt")

; and load it into an array.

; I HAVE TO BUILD THIS CODE SECTION BUT DON'T KNOW HOW TO YET

; <SECTION END>

;-----ENTER THE LICENSE NUMBER TO SEARCH FOR--------------------------------------------------

; The program shoulf loop through each number in the list and perform the following:

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

_IEFormElementSetValue ($oText, "6202967"); I have to figure out how to get this

;number from an Excel range

$oBotton = _IEGetObjByName($oForm, "Submit")

_IEAction($oBotton, "click")

; <SECTION END>

;------GET THE RETURNED DATA TABLE INFO INTO AN ARRAY-----------------------------------------------------------------------------------

$oFrame = _IEFrameGetObjByName ($oIE,"iFrameTwo")

$sHTML = _IEDocReadHTML($oIE)

$oTable = _IETableGetCollection($oIE,11)

$aData = _IETableWriteToArray($oTable, True)

;_ArrayDisplay($aData)

; I have to figure out how to get this array into Excel Sheet

; Read the table cells into a 2-D array

;$XLArray = _IETableWriteToArray ($oTable)

; Close the browser window

;_IEQuit ($oIE)

; <SECTION END>

;----------------------------------------------------------------------------------------

;----To Excel----------------------------------------------------------------------------

;----this works but not exactly what i want------

$sFilePath = @ScriptDir & "\temp.xls"

; Create a blank excel file

_XLCreateBlank ($sFilePath)

; Write the array to the excel file

_XLArrayWrite ($aData, $sFilePath, 1, 1, 5, 1)

Sleep(3000)

MsgBox(0, "", "The excel file will now close.")

; Close the excel file

_XLExit ($sFilePath)

;-------------------------

;-------NAVIGATE BACK TO SEARCH FORM FOR THE NEXT NUMBER----------------------------------------------------

_IELinkClickByText ($oIE, "Back")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

;---------------------------------------------------------------------------------------------

_IEFormElementSetValue ($oText, "6202968") ;I have to figure out how to get this

;"Next number" from the next row in the Excel range

;IEQuit($o_IE)

If I understand you, all the IE stuff is a distractor. Your basic issue is reading the numbers from a file. Did you try a loop with FileReadLine("MasterList.txt") or read it all into an array with _FileReadToArray()?

If it is a spreadsheet, then you could use the _Excel* functions (see help file) to read the column one at a time with _ExcelReadCell() or get the entire column at once with _ExcelReadArray().

And welcome to AutoIt.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

If I understand you, all the IE stuff is a distractor. Your basic issue is reading the numbers from a file. Did you try a loop with FileReadLine("MasterList.txt") or read it all into an array with _FileReadToArray()?

If it is a spreadsheet, then you could use the _Excel* functions (see help file) to read the column one at a time with _ExcelReadCell() or get the entire column at once with _ExcelReadArray().

And welcome to AutoIt.

:)

I thought I tried it but will do so again. I have tied a bunch of different things but ended up messing up the things that were working, could I just do a seperate program for the array loop and call my IE program within it?

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

I thought I tried it but will do so again. I have tied a bunch of different things but ended up messing up the things that were working, could I just do a seperate program for the array loop and call my IE program within it?

Debug your issue with a short test script that just reads the file by various means:
#include <File.au3>
#include <Array.au3>
#include <Excel.au3>

Global $sTxtFile = "C:\Temp\MyList.txt"
Global $sXlsFile = "C:\Temp\MyList.xls"

; Example 1: Read one line at a time
While 1
    $sText = FileReadLine($sTxtFile)
    If @error Then ExitLoop
    ConsoleWrite("$sText = " & $sText & @LF)
WEnd

; Example 2: Read file to an array:
Global $avFile
_FileReadToArray($sTxtFile, $avFile)
For $n = $avFile[0] To 1 Step -1
; Remove blank lines from the array
    If StringStripWS($avFile[$n], 8) = "" Then _ArrayDelete($avFile, $n)
Next
_ArrayDisplay($avFile, "$avFile")


; Example 3: Read spreadsheet column one cell at a time
$oExcel = _ExcelBookOpen($sXlsFile)
$iRow = 1
$iCol = 1; 1 = A
While 1
    $sText = _ExcelReadCell($oExcel, $iRow, $iCol)
    If StringStripWS($sText, 8) = "" Then ExitLoop
    ConsoleWrite("Cell at row " & $iRow & ", col " & $iCol & " = " & $sText & @LF)
WEnd
_ExcelBookClose($oExcel)

; Example 4: Read spreadsheet column into and array
$oExcel = _ExcelBookOpen($sXlsFile)
$iRow = 1
$iCol = 1; 1 = A
$avColumn = _ExcelReadArray($oExcel, $iRow, $iCol, 256, 1)
_ExcelBookClose($oExcel)
For $n = UBound($avColumn) - 1 To 0 Step -1
; Remove blank lines from the array
    If StringStripWS($avColumn[$n], 8) = "" Then _ArrayDelete($avColumn, $n)
Next
_ArrayDisplay($avColumn, "$avColumn")

Once you have a method that works and you understand, incorporate it into the rest of your project.

:)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Debug your issue with a short test script that just reads the file by various means:

#include <File.au3>
#include <Array.au3>
#include <Excel.au3>

Global $sTxtFile = "C:\Temp\MyList.txt"
Global $sXlsFile = "C:\Temp\MyList.xls"

; Example 1: Read one line at a time
While 1
    $sText = FileReadLine($sTxtFile)
    If @error Then ExitLoop
    ConsoleWrite("$sText = " & $sText & @LF)
WEnd

; Example 2: Read file to an array:
Global $avFile
_FileReadToArray($sTxtFile, $avFile)
For $n = $avFile[0] To 1 Step -1
; Remove blank lines from the array
    If StringStripWS($avFile[$n], 8) = "" Then _ArrayDelete($avFile, $n)
Next
_ArrayDisplay($avFile, "$avFile")


; Example 3: Read spreadsheet column one cell at a time
$oExcel = _ExcelBookOpen($sXlsFile)
$iRow = 1
$iCol = 1; 1 = A
While 1
    $sText = _ExcelReadCell($oExcel, $iRow, $iCol)
    If StringStripWS($sText, 8) = "" Then ExitLoop
    ConsoleWrite("Cell at row " & $iRow & ", col " & $iCol & " = " & $sText & @LF)
WEnd
_ExcelBookClose($oExcel)

; Example 4: Read spreadsheet column into and array
$oExcel = _ExcelBookOpen($sXlsFile)
$iRow = 1
$iCol = 1; 1 = A
$avColumn = _ExcelReadArray($oExcel, $iRow, $iCol, 256, 1)
_ExcelBookClose($oExcel)
For $n = UBound($avColumn) - 1 To 0 Step -1
; Remove blank lines from the array
    If StringStripWS($avColumn[$n], 8) = "" Then _ArrayDelete($avColumn, $n)
Next
_ArrayDisplay($avColumn, "$avColumn")

Once you have a method that works and you understand, incorporate it into the rest of your project.

:)

Ok PsaltyDS I'll try those thanks for replying.

Share this post


Link to post
Share on other sites

Ok PsaltyDS I'll try those thanks for replying.

Ok. I have a working prototype program working but it works rather slowly. Could someone please take a look at this and show me how to make it more efficient?

CODE

#include <File.au3>

#include <Array.au3>

#include <Excel.au3>

#include <IE.au3>

Global $sXlsFile = "C:\Temp\MyList.xls"

$oIE =_IECreate ("http://www.velocityhall.com/velohall/index.cfm?STATE=MICHIGAN",0,1, 1, 1)

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "Check / Research Permits")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

$CellNums = InputBox("Number of cells","Type here the number of cells",20)

$oExcel = _ExcelBookOpen("C:\Temp\MyList.xls")

; Gets an array of MasterNumbers from the Excel file

For $INDEX = 1 To $CellNums

$sCellValue = _ExcelReadCell($oExcel,$INDEX)

; MsgBox(0,"Cell: A" & $INDEX,$sCellValue)

_IEFormElementSetValue ($oText, $sCellValue)

$oBotton = _IEGetObjByName($oForm, "Submit")

_IEAction($oBotton, "click")

$oFrame = _IEFrameGetObjByName ($oIE,"iFrameTwo")

$sHTML = _IEDocReadHTML($oIE)

$oTable = _IETableGetCollection($oIE,11)

$aData = _IETableWriteToArray($oTable, True)

;MsgBox(0,"",($sCellValue))

Dim $sCheck

$sCheck = $aData[2][0]

;MsgBox(0,"",($sCellValue))

;MsgBox(0,"",($sCheck))

;----------------------------------------

; I have to figure out how to dump these arrays (Table elements) into Excel

;If( Not( _ArraySearch($aData, 61 )))Then

;Select

;If($sCheck <> $sCellValue)Then

;MsgBox(0,"",($sCheck))

;MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

;EndIf

;~ If($sCheck <> $sCellValue)Then

;~ MsgBox(0,"",($sCheck))

;~ MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3]&","&$aData[3][0] & ","& $aData[3][1] & ","& $aData[3][2]& ","& $aData[3][3])

;~ EndIf

;_ArrayDisplay($oTable,1)

; -------------------------------------------------------------

; Define data to be written:

Global $sData = ($aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

; Open new book, make it visible

;Global $sFilePath = "C:\Temp\MyList.xls"

;Global $oExcel = _ExcelBookOpen($sFilePath)

; Read column A to an array

Global $avData = _ExcelReadArray($oExcel, 1, 2, 1000, 1, 1)

; find the last used cell in this column

Global $iLastUsed = 0, $iNextRow = 0

For $n = UBound($avData) - 1 To 1 Step -1

If StringStripWS($avData[$n], 8) <> "" Then

$iLastUsed = $n

ExitLoop

EndIf

Next

If $iLastUsed Then

$iNextRow = $iLastUsed + 1

;MsgBox(64, "Results", "Last used cell in column A was: " & $iLastUsed & @CRLF & _

; "Next row will be: " & $iNextRow)

Else

$iNextRow = 1

;MsgBox(64, "Results", "There were no used cells in column A." & @CRLF & _

; "Next row will be: 1")

EndIf

; Write the the next row in column A

_ExcelWriteCell($oExcel, $sData, $iNextRow, 2)

; ---------------------------------------

_IELinkClickByText ($oIE, "Back")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

Next

;_ExcelBookClose($oExcel,0,0)I have to figure out how to save this before closing

_IEQuit($oIE)

Share this post


Link to post
Share on other sites

Ok. I have a working prototype program working but it works rather slowly. Could someone please take a look at this and show me how to make it more efficient?

CODE

#include <File.au3>

#include <Array.au3>

#include <Excel.au3>

#include <IE.au3>

Global $sXlsFile = "C:\Temp\MyList.xls"

$oIE =_IECreate ("http://www.velocityhall.com/velohall/index.cfm?STATE=MICHIGAN",0,1, 1, 1)

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "Check / Research Permits")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

$CellNums = InputBox("Number of cells","Type here the number of cells",20)

$oExcel = _ExcelBookOpen("C:\Temp\MyList.xls")

; Gets an array of MasterNumbers from the Excel file

For $INDEX = 1 To $CellNums

$sCellValue = _ExcelReadCell($oExcel,$INDEX)

; MsgBox(0,"Cell: A" & $INDEX,$sCellValue)

_IEFormElementSetValue ($oText, $sCellValue)

$oBotton = _IEGetObjByName($oForm, "Submit")

_IEAction($oBotton, "click")

$oFrame = _IEFrameGetObjByName ($oIE,"iFrameTwo")

$sHTML = _IEDocReadHTML($oIE)

$oTable = _IETableGetCollection($oIE,11)

$aData = _IETableWriteToArray($oTable, True)

;MsgBox(0,"",($sCellValue))

Dim $sCheck

$sCheck = $aData[2][0]

;MsgBox(0,"",($sCellValue))

;MsgBox(0,"",($sCheck))

;----------------------------------------

; I have to figure out how to dump these arrays (Table elements) into Excel

;If( Not( _ArraySearch($aData, 61 )))Then

;Select

;If($sCheck <> $sCellValue)Then

;MsgBox(0,"",($sCheck))

;MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

;EndIf

;~ If($sCheck <> $sCellValue)Then

;~ MsgBox(0,"",($sCheck))

;~ MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3]&","&$aData[3][0] & ","& $aData[3][1] & ","& $aData[3][2]& ","& $aData[3][3])

;~ EndIf

;_ArrayDisplay($oTable,1)

; -------------------------------------------------------------

; Define data to be written:

Global $sData = ($aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

; Open new book, make it visible

;Global $sFilePath = "C:\Temp\MyList.xls"

;Global $oExcel = _ExcelBookOpen($sFilePath)

; Read column A to an array

Global $avData = _ExcelReadArray($oExcel, 1, 2, 1000, 1, 1)

; find the last used cell in this column

Global $iLastUsed = 0, $iNextRow = 0

For $n = UBound($avData) - 1 To 1 Step -1

If StringStripWS($avData[$n], 8) <> "" Then

$iLastUsed = $n

ExitLoop

EndIf

Next

If $iLastUsed Then

$iNextRow = $iLastUsed + 1

;MsgBox(64, "Results", "Last used cell in column A was: " & $iLastUsed & @CRLF & _

; "Next row will be: " & $iNextRow)

Else

$iNextRow = 1

;MsgBox(64, "Results", "There were no used cells in column A." & @CRLF & _

; "Next row will be: 1")

EndIf

; Write the the next row in column A

_ExcelWriteCell($oExcel, $sData, $iNextRow, 2)

; ---------------------------------------

_IELinkClickByText ($oIE, "Back")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

Next

;_ExcelBookClose($oExcel,0,0)I have to figure out how to save this before closing

_IEQuit($oIE)

I forgot to include some sample Master License numbers which are in "C:\Temp\MyList.xls"

Share this post


Link to post
Share on other sites

I forgot to include some sample Master License numbers which are in "C:\Temp\MyList.xls"

6208468

6208894

6207096

6203260

6209172

6207983

6207114

6207744

6206095

6208480

6208459

6208510

6209888

6204836

6205371

6209894

6201437

6207538

6205443

6203910

Share this post


Link to post
Share on other sites

To hide the IE window, change this line:

$oIE =_IECreate ("http://www.velocityhall.com/velohall/index.cfm?STATE=MICHIGAN",0,1, 1, 1)oÝ÷ Ù:-+ºÚ"µÍÌÍÛÒQHWÒQPÜX]H
    ][ÝÚËÝÝÝË[ØÚ]Z[ÛÛKÝ[Ú[Ú[^ÙOÔÕUOSRPÒQÐS][ÝËKJ

Global $arr[2]

$arr[0]="hip"
$arr[1]="hip"
;^^ hip hip array. ^^

Share this post


Link to post
Share on other sites

To hide the IE window, change this line:

$oIE =_IECreate ("http://www.velocityhall.com/velohall/index.cfm?STATE=MICHIGAN",0,1, 1, 1)oÝ÷ Ù:-+ºÚ"µÍÌÍÛÒQHWÒQPÜX]H
    ][ÝÚËÝÝÝË[ØÚ]Z[ÛÛKÝ[Ú[Ú[^ÙOÔÕUOSRPÒQÐS][ÝËKJ
Thanks bundyxc, I was wondering about that. You don't happen to know how to make this section work do you?

CODE
;If($sCheck = $sCellValue)Then

;MsgBox(0,"",($sCheck))

;MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

;EndIf

;~ If($sCheck <> $sCellValue)Then

;~ MsgBox(0,"",($sCheck))

;~ MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3]&","&$aData[3][0] & ","& $aData[3][1] & ","& $aData[3][2]& ","& $aData[3][3])

;~ EndIf

The program currently only gets the first row of the table data. I would like to get all rows for each number into one row in Excel. I tried the above but it would error out if there was only one table row returned. (which will be the case if the Master License holder does not also have a Contractor number).

Share this post


Link to post
Share on other sites

Thanks bundyxc, I was wondering about that. You don't happen to know how to make this section work do you?

CODE
;If($sCheck = $sCellValue)Then

;MsgBox(0,"",($sCheck))

;MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

;EndIf

;~ If($sCheck <> $sCellValue)Then

;~ MsgBox(0,"",($sCheck))

;~ MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3]&","&$aData[3][0] & ","& $aData[3][1] & ","& $aData[3][2]& ","& $aData[3][3])

;~ EndIf

The program currently only gets the first row of the table data. I would like to get all rows for each number into one row in Excel. I tried the above but it would error out if there was only one table row returned. (which will be the case if the Master License holder does not also have a Contractor number).

Could you show me an example of what you're doing please? I just don't think I understand.


Global $arr[2]

$arr[0]="hip"
$arr[1]="hip"
;^^ hip hip array. ^^

Share this post


Link to post
Share on other sites

Ok. I have a working prototype program working but it works rather slowly. Could someone please take a look at this and show me how to make it more efficient?

CODE
#include <File.au3>

#include <Array.au3>

#include <Excel.au3>

#include <IE.au3>

Global $sXlsFile = "C:\Temp\MyList.xls"

$oIE =_IECreate ("http://www.velocityhall.com/velohall/index.cfm?STATE=MICHIGAN",0,1, 1, 1)

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "Check / Research Permits")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

$CellNums = InputBox("Number of cells","Type here the number of cells",20)

$oExcel = _ExcelBookOpen("C:\Temp\MyList.xls")

; Gets an array of MasterNumbers from the Excel file

For $INDEX = 1 To $CellNums

$sCellValue = _ExcelReadCell($oExcel,$INDEX)

; MsgBox(0,"Cell: A" & $INDEX,$sCellValue)

_IEFormElementSetValue ($oText, $sCellValue)

$oBotton = _IEGetObjByName($oForm, "Submit")

_IEAction($oBotton, "click")

$oFrame = _IEFrameGetObjByName ($oIE,"iFrameTwo")

$sHTML = _IEDocReadHTML($oIE)

$oTable = _IETableGetCollection($oIE,11)

$aData = _IETableWriteToArray($oTable, True)

;MsgBox(0,"",($sCellValue))

Dim $sCheck

$sCheck = $aData[2][0]

;MsgBox(0,"",($sCellValue))

;MsgBox(0,"",($sCheck))

;----------------------------------------

; I have to figure out how to dump these arrays (Table elements) into Excel

;If( Not( _ArraySearch($aData, 61 )))Then

;Select

;If($sCheck <> $sCellValue)Then

;MsgBox(0,"",($sCheck))

;MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

;EndIf

;~ If($sCheck <> $sCellValue)Then

;~ MsgBox(0,"",($sCheck))

;~ MsgBox(0,"",$aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3]&","&$aData[3][0] & ","& $aData[3][1] & ","& $aData[3][2]& ","& $aData[3][3])

;~ EndIf

;_ArrayDisplay($oTable,1)

; -------------------------------------------------------------

; Define data to be written:

Global $sData = ($aData[2][0] & ","& $aData[2][1] & ","& $aData[2][2]& ","& $aData[2][3])

; Open new book, make it visible

;Global $sFilePath = "C:\Temp\MyList.xls"

;Global $oExcel = _ExcelBookOpen($sFilePath)

; Read column A to an array

Global $avData = _ExcelReadArray($oExcel, 1, 2, 1000, 1, 1)

; find the last used cell in this column

Global $iLastUsed = 0, $iNextRow = 0

For $n = UBound($avData) - 1 To 1 Step -1

If StringStripWS($avData[$n], 8) <> "" Then

$iLastUsed = $n

ExitLoop

EndIf

Next

If $iLastUsed Then

$iNextRow = $iLastUsed + 1

;MsgBox(64, "Results", "Last used cell in column A was: " & $iLastUsed & @CRLF & _

; "Next row will be: " & $iNextRow)

Else

$iNextRow = 1

;MsgBox(64, "Results", "There were no used cells in column A." & @CRLF & _

; "Next row will be: 1")

EndIf

; Write the the next row in column A

_ExcelWriteCell($oExcel, $sData, $iNextRow, 2)

; ---------------------------------------

_IELinkClickByText ($oIE, "Back")

_IELoadWait($oIE)

_IELinkClickByText ($oIE, "By License Number")

_IELoadWait($oIE)

$oForm = _IEFormGetObjByName ($oIE, "formSearch")

$oText = _IEFormElementGetObjByName ($oForm, "LICENSE_NBR")

Next

;_ExcelBookClose($oExcel,0,0)I have to figure out how to save this before closing

_IEQuit($oIE)

Notes:

1. _IELoadWait() is redundant after _IECreate() because it is called by default unless you set the parameter $f_wait = 0.

2. Same as above after _IELinkClickByText().

3. After _IEAction($oElement, "click") you should do _IELoadWait().

4. The variable $sHTML is never used, so why bother with _IEDocReadHTML($oIE)?

5. There is no reason to re-declare $sCheck every loop, just declare it global at the top. Same for $sData, $aData, $avData, and $iLastUsed.

6. I can't say I fully understand what you're doing, but I don't think you need to access the spreadsheet over and over in the loop. Read the column to an array once before the loop, then add to the array in the loop, write the resulting array back to the column on the spreadsheet after the loop is finished.

Doing everything possible in memory (array) and accessing external apps (Excel) only when necessary will speed things up.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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