Ealric Posted December 2, 2008 Share Posted December 2, 2008 Here's what I have so far (copied a bit of a script from another user and hacked it to get the jist of things..) expandcollapse popup#include <GuiConstants.au3> #include <IE.au3> #include <File.au3> #include <Excel.au3> #include <Array.au3> #include <GUIComboBox.au3> #include <GuiConstantsEx.au3> #include <Constants.au3> $Address = "C:\Football\" DirCreate($Address ) $oExcel = _ExcelBookNew(1) _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _ExcelSheetAddNew($oExcel, "Rushing Offense") _ExcelSheetAddNew($oExcel, "Passing Offense") _ExcelSheetAddNew($oExcel, "Total Offense") _ExcelSheetAddNew($oExcel, "Scoring Offense") _ExcelSheetAddNew($oExcel, "Rushing Defense") _ExcelSheetAddNew($oExcel, "Pass Defense") _ExcelSheetAddNew($oExcel, "Total Defense") _ExcelSheetAddNew($oExcel, "Scoring Defense") _ExcelSheetAddNew($oExcel, "Kickoff Returns") _ExcelSheetAddNew($oExcel, "Turnover Margin") _ExcelSheetAddNew($oExcel, "Fumbles Recovered") _ExcelSheetAddNew($oExcel, "Passes Intercepted") _ExcelSheetAddNew($oExcel, "Fumbles Lost") _ExcelSheetAddNew($oExcel, "Passes Had Intercepted") _ExcelSheetAddNew($oExcel, "Turnovers Gained") _ExcelSheetAddNew($oExcel, "Turnovers Lost") _ExcelSheetAddNew($oExcel, "Passing Efficiency") _ExcelSheetAddNew($oExcel, "Pass Efficiency Defense") _ExcelSheetAddNew($oExcel, "Fewest Penalties Per Game") _ExcelSheetAddNew($oExcel, "Fewest Yards Penalized Per Game") _ExcelSheetAddNew($oExcel, "Punt Return Yardage Defense") _ExcelSheetAddNew($oExcel, "Kickoff Return Yardage Defense") _ExcelSheetAddNew($oExcel, "Sacks") _ExcelSheetAddNew($oExcel, "Tackles for Loss") _ExcelSheetAddNew($oExcel, "Sacks Allowed") _ExcelSheetAddNew($oExcel, "Tackles for Loss Allowed") _ExcelSheetAddNew($oExcel, "3rd Down Conversion Percentage") _ExcelSheetAddNew($oExcel, "4th Down Conversion Percentage") _ExcelSheetAddNew($oExcel, "3rd Down Percentage Defense") _ExcelSheetAddNew($oExcel, "4th Down Percentage Defense") _ExcelSheetAddNew($oExcel, "Time of Possession") _ExcelSheetAddNew($oExcel, "Red Zone Offense") _ExcelSheetAddNew($oExcel, "Red Zone Defense") _ExcelSheetAddNew($oExcel, "First Downs Offense") _ExcelSheetAddNew($oExcel, "First Downs Defense") _ExcelSheetDelete($oExcel, "Sheet1", False) _ExcelSheetDelete($oExcel, "Sheet2", False) _ExcelSheetDelete($oExcel, "Sheet3", False) $oIE = _IECreate("about:blank",0,0) _IELoadWait ($oIE) $oIEs = _IENavigate($oIE, "http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamrush&site=org") $oTable = _IETableGetCollection ($oIEs) ; checked to see how many tables were on the page (one was located) ;$iNumTables = @extended ;MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page") $aTableData = _IETableWriteToArray ($oTable, True) WinActivate("[TITLE:NCAA Football Stats - Microsoft Excel]", "") Winwaitactive("[TITLE:NCAA Football Stats - Microsoft Excel]") _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) sleep(300) _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _IEQuit ($oIE) _ExcelBookClose($oExcel) The problem is that first of all, I can't even get the data to copy into any data sheet. What I'm trying to do is eventually populate each and every data sheet and I can use it as an offline book to print out and go over some statistics. I'm eventually going to manipulate it with another program I created that calculates standard deviation on all statistics to compile a true SoS... I'm using Vista - not sure if that's making a difference here. On the commented out section, I tested extended to see how many tables it would fine. It found 1 table which is correct... Maybe it's just the way I'm attempting to use this... Any help is greatly appreciated.. My Projects: [topic="89413"]GoogleHack Search[/topic], [topic="67095"]Swiss File Knife GUI[/topic], [topic="69072"]Mouse Location Pointer[/topic], [topic="86040"]Standard Deviation Calculator[/topic] Link to comment Share on other sites More sharing options...
Valuater Posted December 2, 2008 Share Posted December 2, 2008 (edited) This will handle the IE info into an array.... This was Tested on XP. Vista has "some" known problems. expandcollapse popup#include <GuiConstants.au3> #include <IE.au3> #include <File.au3> ; return array with stats $Stats = Get_Text("http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamrush&site=org") ; _ExcelWriteSheetFromArray()???? ; write a file with todays date Local $wFile = @YEAR & "-" & @MON & "-" & @MDAY & ".txt" _FileWriteFromArray($wFile, $Stats) ; for testing only Run("notepad.exe") WinWaitActive("") ControlSetText("", "", "Edit1", FileRead($wFile)) Exit Func Get_Text($URL, $Split = "|") Local $sloc = @TempDir & "\stest.txt" Local $oIE = _IECreate($URL, 0, 0) Local $body = _IEBodyReadHTML($oIE) $body = StringReplace($body, "</TR>", "") $body = StringReplace($body, "</TD>", "") $body = StringReplace($body, "<TR>", "") $body = StringReplace($body, "<TD>", "") $body = StringReplace($body, "</A>", "") $body = StringReplace($body, "<TD class=crailbg>", "") $body = StringReplace($body, "<TD class=crailbg2>", "") $body = StringReplace($body, '<A href="/football/exec/rankingSummary?year=2008&org=', "") FileDelete($sloc) FileWrite($sloc, $body) Local $aRecords, $aRet[120], $aCnt = 0, $aInfo If Not _FileReadToArray($sloc, $aRecords) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf For $x = 33 To $aRecords[0] Step 12 $aInfo = "" For $t = $x To ($x + 10) If $t = $x + 1 Then $aRecords[$t] = StringTrimLeft($aRecords[$t], StringInStr($aRecords[$t], ">")) $aInfo &= $aRecords[$t] & $Split Next $aCnt += 1 $aRet[$aCnt] = StringTrimRight($aInfo, 1) & @CRLF If $aCnt >= 119 Then $aRet[$aCnt] = StringTrimRight($aInfo, (StringInStr($aRecords[$t], ">") - 1)) Return $aRet EndIf Next EndFunc ;==>Get_Text 8) Edited December 2, 2008 by Valuater Link to comment Share on other sites More sharing options...
Ealric Posted December 2, 2008 Author Share Posted December 2, 2008 This will handle the IE info into an array.... Thanks Val! I actually have Vista so I ended up using.. expandcollapse popup#include <GuiConstants.au3> #include <IE.au3> #include <File.au3> #include <Excel.au3> #include <Array.au3> #include <GUIComboBox.au3> #include <GuiConstantsEx.au3> #include <Constants.au3> #RequireAdmin $Address = "C:\Football\" DirCreate($Address ) $oExcel = _ExcelBookNew(1) _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _ExcelSheetAddNew($oExcel, "Rushing Offense") _ExcelSheetAddNew($oExcel, "Passing Offense") _ExcelSheetAddNew($oExcel, "Total Offense") _ExcelSheetAddNew($oExcel, "Scoring Offense") _ExcelSheetAddNew($oExcel, "Rushing Defense") _ExcelSheetAddNew($oExcel, "Pass Defense") _ExcelSheetAddNew($oExcel, "Total Defense") _ExcelSheetAddNew($oExcel, "Scoring Defense") _ExcelSheetAddNew($oExcel, "Kickoff Returns") _ExcelSheetAddNew($oExcel, "Turnover Margin") _ExcelSheetAddNew($oExcel, "Fumbles Recovered") _ExcelSheetAddNew($oExcel, "Passes Intercepted") _ExcelSheetAddNew($oExcel, "Fumbles Lost") _ExcelSheetAddNew($oExcel, "Passes Had Intercepted") _ExcelSheetAddNew($oExcel, "Turnovers Gained") _ExcelSheetAddNew($oExcel, "Turnovers Lost") _ExcelSheetAddNew($oExcel, "Passing Efficiency") _ExcelSheetAddNew($oExcel, "Pass Efficiency Defense") _ExcelSheetAddNew($oExcel, "Fewest Penalties Per Game") _ExcelSheetAddNew($oExcel, "Fewest Yards Penalized Per Game") _ExcelSheetAddNew($oExcel, "Punt Return Yardage Defense") _ExcelSheetAddNew($oExcel, "Kickoff Return Yardage Defense") _ExcelSheetAddNew($oExcel, "Sacks") _ExcelSheetAddNew($oExcel, "Tackles for Loss") _ExcelSheetAddNew($oExcel, "Sacks Allowed") _ExcelSheetAddNew($oExcel, "Tackles for Loss Allowed") _ExcelSheetAddNew($oExcel, "3rd Down Conversion Percentage") _ExcelSheetAddNew($oExcel, "4th Down Conversion Percentage") _ExcelSheetAddNew($oExcel, "3rd Down Percentage Defense") _ExcelSheetAddNew($oExcel, "4th Down Percentage Defense") _ExcelSheetAddNew($oExcel, "Time of Possession") _ExcelSheetAddNew($oExcel, "Red Zone Offense") _ExcelSheetAddNew($oExcel, "Red Zone Defense") _ExcelSheetAddNew($oExcel, "First Downs Offense") _ExcelSheetAddNew($oExcel, "First Downs Defense") _ExcelSheetDelete($oExcel, "Sheet1", False) _ExcelSheetDelete($oExcel, "Sheet2", False) _ExcelSheetDelete($oExcel, "Sheet3", False) $oIE = _IECreate("about:blank") _IELoadWait ($oIE) _IENavigate($oIE, "http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamrush&site=org") $oTable = _IETableGetCollection ($oIE,3) $aTableData = _IETableWriteToArray ($oTable, True) WinActivate("[TITLE:Microsoft Excel]", "") Winwaitactive("[TITLE:Microsoft Excel]") _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) sleep(300) _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _IEQuit ($oIE) _ExcelBookClose($oExcel) I had been missing the admin authorization piece. I also was using the wrong index so when I spotted 5, I found the one I needed which was (3). Now, the only thing i have to figure out is how to open every link on the site and force the copy into the appropriate tabs. I'll probably have to Create a tab, Open the page, Copy the data, etc. per a rotating function.. hrm.. My Projects: [topic="89413"]GoogleHack Search[/topic], [topic="67095"]Swiss File Knife GUI[/topic], [topic="69072"]Mouse Location Pointer[/topic], [topic="86040"]Standard Deviation Calculator[/topic] Link to comment Share on other sites More sharing options...
Ealric Posted December 2, 2008 Author Share Posted December 2, 2008 Val, that's a real nice job you did there. I kind of like yours better. The one I posted is more for excel spreadsheets and for a nice look. But, yours is better for what i want to do with the end data and that's to parse particular data columns so that I can run some mathematical standard deviation formulas... Thanks so much mate! My Projects: [topic="89413"]GoogleHack Search[/topic], [topic="67095"]Swiss File Knife GUI[/topic], [topic="69072"]Mouse Location Pointer[/topic], [topic="86040"]Standard Deviation Calculator[/topic] Link to comment Share on other sites More sharing options...
Ealric Posted December 2, 2008 Author Share Posted December 2, 2008 I finished the "Excel" version of the program. It works perfectly on Vista. Can someone test this on XP and let me know if it works or if something is wrong with it? expandcollapse popup#include <GuiConstants.au3> #include <IE.au3> #include <File.au3> #include <Excel.au3> #include <Array.au3> #include <GUIComboBox.au3> #include <GuiConstantsEx.au3> #include <Constants.au3> #RequireAdmin Global $url[37], $tab[37] Global $Address = "C:\Football\" Global $oExcel,$oIE,$oTable,$aTableData,$i ;Offense $url[0]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtotoff&site=org" ; total offense $url[1]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamrush&site=org" ; rushing offense $url[2]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampass&site=org" ; passing offense $url[3]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamscoroff&site=org" ; scoring offense $url[4]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtop&site=org" ; time of possession $url[5]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamredzone&site=org" ; red zone offense $url[6]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamfumlost&site=org" ; fumbles lost $url[7]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampasshadint&site=org" ; passes had intercepted $url[8]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamsacksallowed&site=org" ; sacks allowed $url[9]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtackleslossoffense&site=org" ; tackles for loss allowed $url[10]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teampasseff&site=org" ; passing efficiency $url[11]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team3down&site=org" ; 3rd down conversion percentage $url[12]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team1down&site=org" ; first downs offense $url[13]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team4down&site=org" ; 4th down conversion percentage ;Defense $url[14]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtotdef&site=org" ; total defense $url[15]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamrushdef&site=org" ; rushing defense $url[16]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampassdef&site=org" ; pass defense $url[17]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamscordef&site=org" ; scoring defense $url[18]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamredzonedefense&site=org" ; red zone defense $url[19]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamfumblerecov&site=org" ; fumbles recovered $url[20]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampassint&site=org" ; passes intercepted $url[21]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamsacks&site=org" ; sacks $url[22]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtacklesloss&site=org" ; tackles for loss $url[23]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teamdefpasseff&site=org" ; pass efficiency defense $url[24]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team3downdef&site=org" ; 3rd down percentage defense $url[25]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team1downdefense&site=org" ; first downs defense $url[26]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team4downdef&site=org" ; 4th down percentage defense ;Special Teams $url[27]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamkickret&site=org" ; kickoff returns $url[28]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampuntret&site=org" ; punt returns $url[29]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamnetpunt&site=org" ; net punting $url[30]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teampuntretdefense&site=org" ; punt return yardage defense $url[31]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamkoreturndef&site=org" ; kickoff return yardage defense ;Other $url[32]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamturnovermrgn&site=org" ; turnover margin $url[33]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamturnovergain&site=org" ; turnovers gained $url[34]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamturnoverlost&site=org" ; turnovers lost $url[35]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teampenalties&site=org" ; fewest penalties per game $url[36]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teamyardspenalized&site=org" ; fewest yards penalized per game ;Offense $tab[0]="Total Offense" $tab[1]="Rushing Offense" $tab[2]="Passing Offense" $tab[3]="Scoring Offense" $tab[4]="Time of Possession" $tab[5]="Red Zone Offense" $tab[6]="Fumbles Lost" $tab[7]="Passes Had Intercepted" $tab[8]="Sacks Allowed" $tab[9]="Tackles For Loss Allowed" $tab[10]="Passing Efficiency" $tab[11]="3rd Down Conversion Percentage" $tab[12]="First Downs Offense" $tab[13]="4th Down Conversion Percentage" ;Defense $tab[14]="Total Defense" $tab[15]="Rushing Defense" $tab[16]="Pass Defense" $tab[17]="Scoring Defense" $tab[18]="Red Zone Defense" $tab[19]="Fumbles Recovered" $tab[20]="Passes Intercepted" $tab[21]="Sacks" $tab[22]="Tackles For Loss" $tab[23]="Pass Efficiency Defense" $tab[24]="3rd Down Percentage Defense" $tab[25]="First Downs Defense" $tab[26]="4th Down Percentage Defense" ;Special Teams $tab[27]="Kickoff Returns" $tab[28]="Punt Returns" $tab[29]="Net Punting" $tab[30]="Punt Return Yardage Defense" $tab[31]="Kickoff Return Yardage Defense" ;Other $tab[32]="Turnover Margin" $tab[33]="Turnovers Gained" $tab[34]="Turnovers Lost" $tab[35]="Fewest Penalties Per Game" $tab[36]="Fewest Yards Penalized Per Game" DirCreate($Address ) $oExcel = _ExcelBookNew(1) _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _ExcelSheetAddNew($oExcel, $tab[0]) _ExcelSheetDelete($oExcel, "Sheet1", False) _ExcelSheetDelete($oExcel, "Sheet2", False) _ExcelSheetDelete($oExcel, "Sheet3", False) $oIE = _IECreate("about:blank") _IELoadWait ($oIE) _IENavigate($oIE, $url[0]) $oTable = _IETableGetCollection ($oIE,3) $aTableData = _IETableWriteToArray ($oTable, True) WinActivate("[TITLE:Microsoft Excel]", "") Winwaitactive("[TITLE:Microsoft Excel]") _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) sleep(300) For $i = 1 to 36 Step 1 _ExcelSheetAddNew($oExcel, $tab[$i]) _IENavigate($oIE, $url[$i]) $oTable = _IETableGetCollection ($oIE,3) $aTableData = _IETableWriteToArray ($oTable, True) WinActivate("[TITLE:Microsoft Excel]", "") Winwaitactive("[TITLE:Microsoft Excel]") _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) sleep(300) Next _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _IEQuit ($oIE) _ExcelBookClose($oExcel) It will parse every URL for 37 football categories and automatically create an excel spreadsheet, completing each and every tab. It took me around 90 seconds or so to completely finish, maybe close to 2 minutes. Thanks.. My Projects: [topic="89413"]GoogleHack Search[/topic], [topic="67095"]Swiss File Knife GUI[/topic], [topic="69072"]Mouse Location Pointer[/topic], [topic="86040"]Standard Deviation Calculator[/topic] Link to comment Share on other sites More sharing options...
JayEmmet Posted January 2, 2009 Share Posted January 2, 2009 I finished the "Excel" version of the program. It works perfectly on Vista. Can someone test this on XP and let me know if it works or if something is wrong with it? expandcollapse popup#include <GuiConstants.au3> #include <IE.au3> #include <File.au3> #include <Excel.au3> #include <Array.au3> #include <GUIComboBox.au3> #include <GuiConstantsEx.au3> #include <Constants.au3> #RequireAdmin Global $url[37], $tab[37] Global $Address = "C:\Football\" Global $oExcel,$oIE,$oTable,$aTableData,$i ;Offense $url[0]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtotoff&site=org" ; total offense $url[1]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamrush&site=org" ; rushing offense $url[2]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampass&site=org" ; passing offense $url[3]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamscoroff&site=org" ; scoring offense $url[4]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtop&site=org" ; time of possession $url[5]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamredzone&site=org" ; red zone offense $url[6]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamfumlost&site=org" ; fumbles lost $url[7]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampasshadint&site=org" ; passes had intercepted $url[8]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamsacksallowed&site=org" ; sacks allowed $url[9]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtackleslossoffense&site=org" ; tackles for loss allowed $url[10]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teampasseff&site=org" ; passing efficiency $url[11]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team3down&site=org" ; 3rd down conversion percentage $url[12]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team1down&site=org" ; first downs offense $url[13]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team4down&site=org" ; 4th down conversion percentage ;Defense $url[14]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtotdef&site=org" ; total defense $url[15]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamrushdef&site=org" ; rushing defense $url[16]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampassdef&site=org" ; pass defense $url[17]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamscordef&site=org" ; scoring defense $url[18]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamredzonedefense&site=org" ; red zone defense $url[19]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamfumblerecov&site=org" ; fumbles recovered $url[20]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampassint&site=org" ; passes intercepted $url[21]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamsacks&site=org" ; sacks $url[22]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamtacklesloss&site=org" ; tackles for loss $url[23]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teamdefpasseff&site=org" ; pass efficiency defense $url[24]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team3downdef&site=org" ; 3rd down percentage defense $url[25]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team1downdefense&site=org" ; first downs defense $url[26]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_team4downdef&site=org" ; 4th down percentage defense ;Special Teams $url[27]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamkickret&site=org" ; kickoff returns $url[28]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teampuntret&site=org" ; punt returns $url[29]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamnetpunt&site=org" ; net punting $url[30]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teampuntretdefense&site=org" ; punt return yardage defense $url[31]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamkoreturndef&site=org" ; kickoff return yardage defense ;Other $url[32]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamturnovermrgn&site=org" ; turnover margin $url[33]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamturnovergain&site=org" ; turnovers gained $url[34]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&rpt=IA_teamturnoverlost&site=org" ; turnovers lost $url[35]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teampenalties&site=org" ; fewest penalties per game $url[36]="http://web1.ncaa.org/mfb/natlRank.jsp?year=2008&div=IA&rpt=IA_teamyardspenalized&site=org" ; fewest yards penalized per game ;Offense $tab[0]="Total Offense" $tab[1]="Rushing Offense" $tab[2]="Passing Offense" $tab[3]="Scoring Offense" $tab[4]="Time of Possession" $tab[5]="Red Zone Offense" $tab[6]="Fumbles Lost" $tab[7]="Passes Had Intercepted" $tab[8]="Sacks Allowed" $tab[9]="Tackles For Loss Allowed" $tab[10]="Passing Efficiency" $tab[11]="3rd Down Conversion Percentage" $tab[12]="First Downs Offense" $tab[13]="4th Down Conversion Percentage" ;Defense $tab[14]="Total Defense" $tab[15]="Rushing Defense" $tab[16]="Pass Defense" $tab[17]="Scoring Defense" $tab[18]="Red Zone Defense" $tab[19]="Fumbles Recovered" $tab[20]="Passes Intercepted" $tab[21]="Sacks" $tab[22]="Tackles For Loss" $tab[23]="Pass Efficiency Defense" $tab[24]="3rd Down Percentage Defense" $tab[25]="First Downs Defense" $tab[26]="4th Down Percentage Defense" ;Special Teams $tab[27]="Kickoff Returns" $tab[28]="Punt Returns" $tab[29]="Net Punting" $tab[30]="Punt Return Yardage Defense" $tab[31]="Kickoff Return Yardage Defense" ;Other $tab[32]="Turnover Margin" $tab[33]="Turnovers Gained" $tab[34]="Turnovers Lost" $tab[35]="Fewest Penalties Per Game" $tab[36]="Fewest Yards Penalized Per Game" DirCreate($Address ) $oExcel = _ExcelBookNew(1) _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _ExcelSheetAddNew($oExcel, $tab[0]) _ExcelSheetDelete($oExcel, "Sheet1", False) _ExcelSheetDelete($oExcel, "Sheet2", False) _ExcelSheetDelete($oExcel, "Sheet3", False) $oIE = _IECreate("about:blank") _IELoadWait ($oIE) _IENavigate($oIE, $url[0]) $oTable = _IETableGetCollection ($oIE,3) $aTableData = _IETableWriteToArray ($oTable, True) WinActivate("[TITLE:Microsoft Excel]", "") Winwaitactive("[TITLE:Microsoft Excel]") _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) sleep(300) For $i = 1 to 36 Step 1 _ExcelSheetAddNew($oExcel, $tab[$i]) _IENavigate($oIE, $url[$i]) $oTable = _IETableGetCollection ($oIE,3) $aTableData = _IETableWriteToArray ($oTable, True) WinActivate("[TITLE:Microsoft Excel]", "") Winwaitactive("[TITLE:Microsoft Excel]") _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) sleep(300) Next _ExcelBookSaveAs($oExcel, $Address & "NCAA Football Stats", "xls", 0, 1) _IEQuit ($oIE) _ExcelBookClose($oExcel) It will parse every URL for 37 football categories and automatically create an excel spreadsheet, completing each and every tab. It took me around 90 seconds or so to completely finish, maybe close to 2 minutes. Thanks..I see that no one has replied to your question about this working on XP. Well I tested it on my XP and it works perfectly, Good job! 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