excel - Pull data from Website into VBA -
this might fall under dumb question newbie. don't know start in vba. tried few different approaches on web trying pull data site i'm trying , of them failed miserably. can me (more or less show me) how pull data website?
https://rotogrinders.com/projected-stats/nfl?site=fanduel
it wouldn't let me data->import. here have far. keep getting stuck on line t = 0 (table.length - 1).
sub test1() dim appie object set appie = createobject("internetexplorer.application") dim table object dim t integer dim r integer dim c integer appie .navigate "https://rotogrinders.com/projected-stats/nfl?site=fanduel" .visible = true end while appie.busy doevents loop set table = appie.document.getelementbyid("proj-stats") t = 0 (table.length - 1) r = 0 (table(t).rows.length - 1) c = 0 (table(t).rows(r).cells.length - 1) thisworkbook.worksheets(1).cells(r + 1, c + 1) = table(t).rows(r).cells(c).innertext next c next r next t appie.quit set appie = nothing end sub
you close, , there several ways data. chose extract row elements (html <td>
) , step through simple loop. since there 6 columns i'm using 2 variables (r
& c
row , column) offset data format correctly.
set table = appie.document.getelementsbytagname("td") r = 0 c = 0 each itm in table worksheets(1).range("a1").offset(r, c).value = itm.innertext c = c + 1 if c mod 6 = 0 r = r + 1 c = 0 end if next itm
example result:
one last note, browser didn't finish loading before script went on... cheated using break point before loop, waited until loaded, hit f5 continue execution of code ensure alway run.
Comments
Post a Comment