vba - EXCEL: Automatically populating cells from websites requiring a login and where the information is not directly stored in tables -
i trying write vba code in excel file fetch data company's intranet site (which requires login).
i doing part of company project , need excel auto fetch these fields (numbers) portal.
i did research , found relevant code helps automate login process, each field need fill i.e cells under column c row 1, need first fetch unique web link column row 1, on , forth.
sub gettable() dim ieapp internetexplorer dim iedoc object dim ietable object dim clip dataobject 'create new instance of ie set ieapp = new internetexplorer 'you don’t need this, it’s debugging ieapp.visible = true 'assume we’re not logged in , go directly login page ieapp.navigate "http://severe-frost-552.heroku.com/login" while ieapp.busy: doevents: loop until ieapp.readystate = readystate_complete: doevents: loop set iedoc = ieapp.document 'fill in login form – view source browser control names iedoc.forms(0) .login.value = "dailydose" .password.value = "password" .submit end while ieapp.busy: doevents: loop until ieapp.readystate = readystate_complete: doevents: loop 'now we’re in, go page want ieapp.navigate "http://severe-frost-552.heroku.com/" while ieapp.busy: doevents: loop until ieapp.readystate = readystate_complete: doevents: loop 'get table based on table’s id set iedoc = ieapp.document set ietable = iedoc.all.item("sampletable") 'copy tables html clipboard , paste teh sheet if not ietable nothing set clip = new dataobject clip.settext "<html>" & ietable.outerhtml & "</html>" clip.putinclipboard sheet1.select sheet1.range("a1").select sheet1.pastespecial "unicode text" end if 'close 'er ieapp.quit set ieapp = nothing end sub the problem above code can fetch data defined tables on website, data trying fetch not defined under div id , populated text after appropriate spacing. eg. also, keeps returning error client terminated connection or ie didnt respond.
<div class="menucontent"><table width="100%"><tr><td width="50%"><h2 style="border-color: #2175bb;">necessary persons</h2></td><td><h2 style="border-color: #2175bb;">estimated time complete</h2></td></tr><tr><td> **2** </td><td> **180 min** </td></tr> the bold text need fetch site excel. don't care formatting. appreciated!
normally not recommend 1 shall not parse html regex. in special case think best shot.
set reference microsoft vbscript regular expression 5.5, replace "txt" part word macro , should fine:
dim rex regexp dim matches matchcollection dim m match dim txt string set rex = new regexp rex .pattern = "(estimated time complete)(.+?)(\<td\>)(.+?)(\<\/td\>\<td\>)(.+?)(\<\/td\>)" .multiline = false .global = true end txt = activedocument.content set matches = rex.execute(txt) each m in matches msgbox "necessary persons: " & m.submatches(3) msgbox "etc: " & m.submatches(5) next m will trick you?
Comments
Post a Comment