JayEmmet Posted January 2, 2009 Share Posted January 2, 2009 (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 January 2, 2009 by JayEmmet Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 2, 2009 Share Posted January 2, 2009 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 Link to comment Share on other sites More sharing options...
JayEmmet Posted January 2, 2009 Author Share Posted January 2, 2009 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? Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 2, 2009 Share Posted January 2, 2009 (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: expandcollapse popup#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 January 2, 2009 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 Link to comment Share on other sites More sharing options...
JayEmmet Posted January 3, 2009 Author Share Posted January 3, 2009 Debug your issue with a short test script that just reads the file by various means: expandcollapse popup#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. Link to comment Share on other sites More sharing options...
JayEmmet Posted January 5, 2009 Author Share Posted January 5, 2009 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) Link to comment Share on other sites More sharing options...
JayEmmet Posted January 5, 2009 Author Share Posted January 5, 2009 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" Link to comment Share on other sites More sharing options...
JayEmmet Posted January 5, 2009 Author Share Posted January 5, 2009 I forgot to include some sample Master License numbers which are in "C:\Temp\MyList.xls"62084686208894620709662032606209172620798362071146207744620609562084806208459620851062098886204836620537162098946201437620753862054436203910 Link to comment Share on other sites More sharing options...
bundyxc Posted January 5, 2009 Share Posted January 5, 2009 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. ^^ Link to comment Share on other sites More sharing options...
JayEmmet Posted January 5, 2009 Author Share Posted January 5, 2009 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][ÝËKJThanks 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). Link to comment Share on other sites More sharing options...
bundyxc Posted January 5, 2009 Share Posted January 5, 2009 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. ^^ Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 5, 2009 Share Posted January 5, 2009 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now