andomatic

Bill, Thanks for the link, I've incorporated the cursor move piece and it seems to work, to write in the data, I'm thinking that I need to create an array out of my info and then somehow write that across rather than down so to speak

Hi, I've some data that I need to write into an existing excel sheet.The sheet has 24 active columns (a-x) and some rows populated, 30 or so. To get my data I read a locally saved htm file and populate some vars, no problem. The question is how to then find the first blank row, write my data, move to the next row, read the next data set and write it. I've got the code done for the data loop piece, just can not for the life of me figure out the excel. I don't care if I use the excel UDF or COM calls, just need a push in the right direction please... Here is what I have so far, any help is appreciated. #include <Constants.au3> #include <Excel.au3> #include <Array.au3> #include <Date.au3> #include <File.au3> #include <String.au3> ;WinSetState("Excel","",@SW_MINIMIZE) Const $log = @ScriptDir & "\CreateDB.log" Const $sSrcPath = @ScriptDir & "\result" Const $xlPath = "\xxx\upload.xls" Local $fTgtFile Local $sCompName Local $sCompStreetAdd Local $sCompCity Local $sCompState Local $sCompZip Local $sCompCounrty Local $sCompTele Local $sNAICS Local $sNAICSDesc Local $sSIC2 Local $sSIC4 Local $sSICM Local $sSIC2Desc Local $sSIC4Desc Local $sSICMDesc Local $sMemberVar Local $sMemberTitle Local $sMemberFunc Local $sMemberPosition Local $sMemberSIC2 Local $sMemberSIC4 Local $sMemberSICM Local $sMemberRev Local $sMemberEmp _FileWriteLog($log, "***Run Start***") Local $oExcel = _ExcelBookOpen($xlPath) ;Setup Excel WinSetState("Excel","",@SW_MAXIMIZE) Sleep (1000) _ExcelSheetActivate($oExcel, "Sheet1") Local $iRows = $oExcel.ActiveSheet.UsedRange.Rows.Count Local $aHandle = _FileListToArray($sSrcPath, "*.htm",1) ; Work through the folder If @error Then MsgBox(0, "Error", "No files matched") Else ; Loop through the found files For $i = 1 To 5 ;$aHandle[0] MsgBox(4096, "File:", $aHandle[$i]) $fTgtFile = FileOpen($sSrcPath & "\" & $aHandle[$i],0) If $fTgtFile = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf While 1 ;MsgBox(0, "Company City:", $sCompCity[0]) Local $line = FileReadLine($fTgtFile) If @error = -1 Then ExitLoop ;Company Name If StringInStr ($line,'companyName: "') Then $sCompName = _StringBetween($line,'companyName: "','",') EndIf ;Company Street If StringInStr ($line,'itemprop="streetAddress">') Then $sCompStreetAdd = _StringBetween($line,'itemprop="streetAddress">','</div>') EndIf ;Company City If StringInStr ($line,'city: "') Then $sCompCity = _StringBetween($line,'city: "','",') EndIf ;Company State If StringInStr ($line,'state: "') Then $sCompState = _StringBetween($line,'state: "','",') EndIf ;Company Zip If StringInStr ($line,'<span class="addresspostalCode" itemprop="postalCode">') Then $sCompZip = _StringBetween($line,'itemprop="postalCode">','</span>') EndIf ;Company Country If StringInStr ($line,'"addressCountry" class="">') Then $sCompCountry = _StringBetween($line,'class="">','<!--country-->') EndIf ;Company Telephone If StringInStr ($line,'itemprop="telephone">') Then $sCompTele = _StringBetween($line,'itemprop="telephone">','</dd>') EndIf ;NACIS If StringInStr ($line,'naics":"') Then $sNAICS = _StringBetween($line,'naics":"','"}') EndIf ;NACIS Desc If StringInStr ($line,'naics_desc":"') Then $sNAICSDesc = _StringBetween($line,'naics_desc":"','"}') EndIf ;SIC2 If StringInStr($line,'{"sic2":') Then $sSIC2 = _StringBetween($line,'{"sic2":','}') EndIf ;SIC4 If StringInStr($line,'{"sic4":') Then $sSIC4 = _StringBetween($line,'{"sic4":','}') EndIf ;SIC4 If StringInStr($line,'{"sicm":') Then $sSICM = _StringBetween($line,'{"sicm":','}') EndIf ;SIC2Desc If StringInStr($line,'{"sic2_desc":') Then $sSICM = _StringBetween($line,'{"sic2_desc":','}') EndIf ;SIC4Desc If StringInStr($line,'{"sic4_desc":') Then $sSICM = _StringBetween($line,'{"sic4_desc":','}') EndIf ;SICM_Desc If StringInStr($line,'{"sicm_desc":') Then $sSICM = _StringBetween($line,'{"sicm_desc":','}') EndIf ;Member Var If StringInStr($line,'{"member_s_var":') Then $sMemberVar= _StringBetween($line,'{"member_s_var":','}') EndIf ;Member Title If StringInStr($line,'member_attrs_title":"') Then $sMemberVar= _StringBetween($line,'member_attrs_title":"','}') EndIf ;Member Function If StringInStr($line,'{"member_function":"') Then $sMemberFunc= _StringBetween($line,'{"member_function":"','}') EndIf ;Member Position If StringInStr($line,'{"member_position":"') Then $sMemberPosition= _StringBetween($line,'{"member_position":"','}') EndIf ;Member SIC2 If StringInStr($line,'{"member_sic2":"') Then $sMemberSIC2= _StringBetween($line,'{"member_sic2":"','}') EndIf ;Member SIC4 If StringInStr($line,'{"member_sic4":') Then $sMemberSIC4= _StringBetween($line,'{"member_sic4":','}') EndIf ;Member SICM If StringInStr($line,'{"member_sicm":') Then $sMemberSICM= _StringBetween($line,'{"member_sicm":','}') EndIf ;Member Revenue If StringInStr($line,'{"member_revenue":"') Then $sMemberRev= _StringBetween($line,'{"member_revenue":','}') EndIf ;Member Employees If StringInStr($line,'{"member_employees":"') Then $sMemberRev= _StringBetween($line,'{"member_employees":','}') EndIf ;Stuck here on how to get those vars into excel :( WEnd Next EndIf Exit ; Open the workbook ;~ $oExcel = ObjCreate("Excel.Application") ;~ $oExcel = ObjCreate("Excel.Application") ;~ $oBook = $oExcel.Workbooks.Open("xxx\upload.xls") ;~ $oExcel.Visible = True ;~ $oSheet = $oBook.Worksheets(1) ;~ $oSheet.Activate ;~ $oRange = $oSheet.UsedRange ;~ $oRange.SpecialCells($xlCellTypeLastCell).Activate ;~ $newRow = $oExcel.ActiveCell.Row + 1 ;~ $oExcel.Range("A" & $newRow).Activate

Hi, I'm very new to excel manipulation and have a sheet I need to process some data from. The sheet has 29 columns. I need to read by row the information in, for example, columns 2,3,6,9,13,19. I am looking at the _ExcelReadArray and _ExcelReadSheetToArray but neither seems to offer a way to read only certain columns. or read in all the columns and then get an index? I'm feeling like it is right in front of me but for the life of me I'm stumped. Any direction is greatly appreicated Thanks Andy

Great Idea, and worked like a champ. Thanks very very much!

Hello, I'm working on pulling some data from a table in Internet Explorer and writing the contents, on a row by row basis, into a csv file. . I am able to loop through to an extent but have the following questions: 1. How do I determine the end of a row 2. Sometimes the table data will have a hyperlink attached. I'd very much like to capture that url in my csv file. If I use the _IETableWriteToArray function, I am able to do everything I need except for getting the URL. Does anyone know a way to pull the URL for certain cells in the table? I have looked at a For...Next using _IETagnameGetCollection but am not successful. Thanks in advance for any assistance! Code below: $oTable = _IETableGetCollection($oIE,0) $oTRs = _IETagnameGetCollection($oTable, "TR") For $oTR In $oTRs $oTDs = _IETagnameGetCollection($oTR, "TD") For $oTD In $oTDs $sRowCont = _IEPropertyGet($oTD, "innertext") msgbox(0,"Cell Text:",$sRowCont) $oTD = _IETagnameGetCollection($oTR, "TD", 0) $oLink = _IETagnameGetCollection($oTD, "a", 0) msgbox(0,"Link Text",$oLink ) ;ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $blnFound = ' & $blnFound & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console Next Next

Hi, I am trying to come up with a way to navigate my way through an IE based web app. I can do it by moving the mouse etc, but I'd rather not depend on things being in a specific location. Unfortunately I am not seeing collections that I can identify. The app has right click menus for example, please see the attached screen shot. Ideas welcome !!! This to me looks like a Win32 app running inside of a browser? Thanks, Andy

Thanks, perfect. some times you overlook things 2 inches away. Thanks Again, Andy

Hello. I am trying to accomplish a (looks like) simple task. I need to read a column from an excel sheet into an array and then loop through the values to test against them. My workbook has multiple sheets. When I run my code: #include <File.au3> #include <Excel.au3> #include <Array.au3> Const $sKeyWordPath = "p:\autoitsrc\code\keywords.xls"; Excel file with search terms Const $sTextFilePath = "p:\autoitsrc\bystate\AL"; State pdf to text conversions Dim $sTextFileContents Dim $sPaymentAmt Dim $aFileList ; Open up the keywords and populate and array $oExcel =_ExcelBookOpen($sKeyWordPath,0,"True") _ExcelSheetActivate($oExcel, "duration") $aArray = _ExcelReadSheetToArray($oExcel) _ArrayDisplay($aArray, "Array using Default Parameters") ; Read in each text file and then loop the array until we get a hit $aFileList = _FileListToArray($sTextFilePath, "*.txt") If @error=1 Then MsgBox (0, "", "No Files\Folders Found.") Exit EndIf Global $aFileStrings[$aFileList[0] + 1][2] = [[$aFileList[0]]] For $i = 1 To $aFileList[0] $sFile = FileRead($sTextFilePath & "\" & $aFileList[$i]) ; Check for the keywords... For $kw = 1 to $aArray[0] msgbox(0,"Keywords...",$kw) Next ;msgbox(0,"File Results",$sFile) Next _ExcelBookClose($oExcel) I get an error as below: P:\AutoItSrc\code\GOLD\exceltest.au3 (33) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: For $kw = 1 to $aArray[0] For $kw = 1 to ^ ERROR ->14:32:14 AutoIT3.exe ended.rc:1 >Exit code: 1 Time: 8.243 when I run the code. A kick in the correct direction is appreciated. Thanks, Andy

Dale, I can not thank you enough for this help. You took an unmanageable task and transformed it instantly as well as taught me some skills along the way. Thanks so much for your time and help, this works perfectly ! Andy

Dale, I've setup a test using the information you provided and I am getting stuck now on only one piece. It seems (to my rather inexperienced self in any case) that the complete row is being returned, rather than cell by cell? I am thinking I can get what I need by taking the first few chars of the row and storing them in a variable, then formating and passing the text to _IEClickLinkByText. But I feel like I am missing something and should not have to do that. It feels like passing the "a" tag is generating an unrecognized response? Any more light that you can shed would be great. There is a test file attached. To run the test drop the attached .htm file on the root of C. Here is my code... Thanks again for all of your help! ; #AutoIt3Wrapper_run_debug_mode=Y #include <IE.au3> Dim $oIE Dim $sHTML Dim $sSearchText Dim $oTable Dim $oTR Dim $oTD Dim $sTblCont Dim $oLink ;Launch IE $oIE = _IECreate () _IENavigate($oIE, "c:\test.htm") WinSetState("Test","",@SW_MAXIMIZE) $sSearchText = "Chapter 13 Plan" ;Find the all Chapter 13 in description and DL each PDF ;Grab a reference to the table... $oTable = _IETableGetCollection($oIE,0) ; select first table, index 1 $oTR = _IETagnameGetCollection($oTable, "TR") $oTD = _IETagnameGetCollection($oTR, "TD") For $oTD in $oTR ;each cell in the current row $sTblCont = _IEPropertyGet($oTD, "innertext") If StringInStr($sTblCont,$sSearchText) Then msgbox(0,"Contents",$sTblCont) $oTD = _IETagnameGetCollection($oTR, "TD", 0) $oLink = _IETagnameGetCollection($oTD, "a") ConsoleWrite ("Link Ref: " & $oLink & @CRLF) _IEAction($oLink, "click") EndIf Next msgbox (0,"Status","Complete") _IEQuit($oIE) Exittest.htm

Dale, Thanks very much for this. I am going to take a crack on Monday and will let you know how I make out. I'd toyed with saving the body text to a txt file, getting the value and passing it back. Your solution seems MUCH more elegant and I am excited to give it a try! Andy

Hi, I am trying to do some work with a web page. The need is to search through the page, and download some document each time I find a certain text string. The problem I am having is that the text I am searching for is not the link, the link I need is two back in the document. This is true for all occurrences. So if my text has 3 appearances in the doc, find first, back up 2 links, click, save html, go back to original page, find next occurrence of my text and repeat. As one can see from the included code, I am at a loss on successfully navigating the html. Any help is most appreciated ! My code is below and I have attached the htm file that I am trying to search. Thanks Very Much. ;AutoIt Version: 3.0 ;Language: English ;Platform: Win x86 ;Author: Andy Folz ;Script Function: ;Scrape data from Pacer ;Globals ;#AutoIt3Wrapper_run_debug_mode=Y #include-once #include <IE.au3> #include <File.au3> #include <Sound.au3> #include <Array.au3> Dim $FromDate Dim $Body Dim $TestRes Dim $UserName Dim $Password Dim $oForm Dim $oLogin Dim $oPassword Dim $oSubmit Dim $oSubmitPDF Dim $oIE Dim $aRecords Dim $aFinal Dim $res Dim $oSubmitBK Dim $oHistoryForm Dim $sHTML Dim $sSearchText Dim $sFlag ;TEST VALUE*** CASE ID ;TEST VALUE*** SSN ;Launch IE and hit the site $oIE = _IECreate ("https://pacer.login.uscourts.gov/cgi-bin/login.pl?court_id=00pcl") WinSetState("Public Access to Court Electronic Records","",@SW_MAXIMIZE) $sSearchText = "Chapter 13 Plan" ;Login to the system $UserName = "" $Password = "" $oForm = _IEFormGetCollection ($oIE, 0) $oLogin = _IEFormElementGetCollection ($oForm, 1) $oPassword = _IEFormElementGetCollection ($oForm, 2) $oSubmit = _IEFormElementGetCollection ($oForm, 4) _IEFormElementSetValue($oLogin, $UserName) _IEFormElementSetValue($oPassword, $password) _IEAction ($oSubmit, "click") _IELoadWait ($oIE) ;Navigate to the BK search _IELinkClickByText($oIE,"Bankruptcy") ;Build the data set Dim $aRecords _FileReadToArray("c:\AutoItSrc\DS\testDS.csv",$aRecords) For $x = 1 to $aRecords[0] ;Remove the white space $res = StringStripWS($aRecords[$x],7) $aFinal = StringSplit($res,",",2); this is split as $aFinal[0] is the SSN and $aFinal[1] is the Case# ;Create a folder to store the result set DirCreate("c:\result\" & $aFinal[0]& "_" & $aFinal[1]) ;Search for the case $oBkForm = _IEFormGetCollection ($oIE, 0) $oCaseID = _IEFormElementGetCollection ($oBKForm, 5) _IEFormElementSetValue($oCaseID, $aFinal[1]) $oSSN = _IEFormElementGetCollection ($oBKForm, 22) _IEFormElementSetValue($oSSN, $aFinal[0]) ;Submit the form $oSubmitBK = _IEFormElementGetCollection ($oBKForm, 24) _IEAction ($oSubmitBK, "click") _IELoadWait ($oIE) Sleep(1000) ConsoleWrite("SSN = " & $aFinal[0] & " | " & "CaseID = " & $aFinal[1] & " | " & "# Processed this run: " & $x & @CRLF) ; Check for No Cases $Body = _IEBodyReadText($oIE) ;msgbox(0,"Body Text",$Body) $TestRes = StringInStr($Body,"No Records Found") If $TestRes > 0 Then ;No Record Found, so save it and back it up for a new search Sleep(100) Send("!fa") Sleep(500) Send(" C:\result\" & $aFinal[0]& "_" & $aFinal[1] & "\" & $aFinal[0]& "_" & $aFinal[1] & "_Summary.htm") Sleep(200) Send("!s") Sleep(1000) _IEAction($oIE,"back") Sleep(1500) Else ;Record is OK ;Download the HTM file _IELinkClickByIndex($oIE, 5) _IELinkClickByText($oIE,"History / Documents") ; Need to accomdate different format of link... If @error Then _IELinkClickByText($oIE,"History/Documents") EndIf _IELoadWait($oIE,2000) ;Click to pull the record. $oHistoryForm = _IEFormGetCollection ($oIE, 0) $oSubmitPDF = _IEFormElementGetCollection($oHistoryForm,4) _IEAction ($oSubmitPDF, "click") _IELoadWait ($oIE) Sleep(1000) ;Find the all Chapter 13 in description and DL each PDF $sHTML = _IEDocReadHTML($oIE) $sFlag = "Set" $intCtr = 1 Do ;~ $res = StringSplit($sHTML,@CRLF) ;~ ;_ArrayDisplay ($res,"Result of HTML Parse") ;~ $iIndex = _ArraySearch($res, $sSearchText, 0, 0, 0, 1) ;~ $iIndex = $iIndex -17 ;~ msgbox (0,"res",$iIndex) ;~ If @error Then ;~ MsgBox(0, "Not Found", '"' & $sSearchText & '" was not found in the array.') ;~ Else ;~ MsgBox(0, "Found", $iIndex[171]) ;~ EndIf ;~ Exit $oLinks = _IELinkGetCollection ($oIE) $iNumLinks = @extended MsgBox(0, "Link Info", $iNumLinks & " links found") For $oLink In $oLinks MsgBox(0, "Link Info", $oLink.href) Next Until $res = 0 msgbox(0,"res","Done") Exit Sleep(1000) Send("!fa") Sleep(500) Send("C:\result\" & $aFinal[0]& "_" & $aFinal[1] & "\" & $aFinal[0]& "_" & $aFinal[1] & "_Summary.htm") sleep(1000) Send("!s") Sleep(2000) msgbox (0,"Histroy Page","Done") Exit ;Setup browser for new search, back 3 pgs. _IEAction($oIE,"back") Sleep(1500) _IEAction($oIE,"back") Sleep(1500) _IEAction($oIE,"back") Sleep(1500) EndIf Next msgbox(0,"Info","Run Complete") Exitsample.htm

Hi, I am very new to autoit, please forgive what I suspect is a simple questiopn... Given this code: $oIE = _IECreate ("http://www.example.com"); some example site with a form on it $oForm = _IEFormGetCollection ($oIE, 0) how can I loop through the collection so as to figure out what to use in _IEFormElementGetCollection so that I hit the correct input box? I am having trouble getting anything to fill in, I've started at zero and worked my way to 15 on the second parameter in _IEFormElementGetCollection. Thanks in advance. Ando