Copy table from website to Excel using Web Browser Object

[wyt3dr4g0n]

Ok so I've been developing a spreadsheet for use in collecting helpful information at the call center that I work at. It's been using webqueries and has worked great up to this point, however the primary intranet site I'm pulling data from has change it's login method to a more secure one and I'm no longer able to use the queries as before thanks to this. I CAN however use a browser object and get it to login using the following code:

Dim oHTML_Element As IHTMLElement
Dim OCUrl, loginURL, uid, pword, logmsg As String
Dim Table, Location As HTMLTable

loginURL = "[URL unfurl="true"]http://intranetloginsitetopassthrough"[/URL]
OCUrl = "[URL unfurl="true"]http://siteimneedingtopulltablefrom"[/URL]
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.Navigate loginURL
oBrowser.Visible = False 'set to true for testing

uid = InputBox("Please enter your username", "Username")
pword = InputBoxDK("Please enter your password", "Password")
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.Document

HTMLDoc.all.UserId.Value = uid
HTMLDoc.all.passWord.Value = pword

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

For Each oHTML_Element In HTMLDoc.getElementsByTagName("successOK")
oHTML_Element.Click: Exit For
Next
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
oBrowser.Navigate OCUrl

End Sub

That part is working perfectly, logs into the site and browses to the page with the table I need. My question is, what code do I need to select a table on this page, copy it, and paste into a sheet in excel? Is this possible? Would it be easier to save the document and just load the htm page in excel? I've done this manually and I can work with the data fine that way as well but I'm not sure of the code to save the page. Either method would be acceptible to me at this point.

 

[wyt3dr4g0n]

OKAY! So I've discovered the awesomeness that is .ExecWB... seems to have done the trick and I can use it to perform either method. I decided to go with...

oBrowser.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
oBrowser.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
Sheet2.Paste Range("A2")

It copies the whole page and pastes it into the worksheet, enough for what my purpose requires. Thanks for the help anyway!

 

[Gruuuu]

If you wish to be more exacting, you will have to do some tricky maneuvering. You can use the GetElementsByTagName to select the table you're looking for, then check the InnerText or InnerHTML to verify its identity. After that you have a couple options, you could parse all the data programmatically and arrange it (difficult) or write all the table HTML to a new text file and save it as an .html and either use a web query on that, or copy and paste the table as you have done.

Probably not worth the time investment though