Get Active IE Contents Into Worksheet

[Ken Puls]

Hi Guys,

I'm having a bit of an issue with something. I want to get the content from an IE window (a table) into an Excel worksheet. In the past, I've used the QueryTable method, (code below,) but they've recently upgraded it and it doesn't work any more.

The website takes a login and password, which I have, but I suspect that it is this that is giving the issue. The querytable method works by setting up a querytable to the URL, but if you paste the URL in a web browser, it comes back to the login page. At least... it does if you close IE.

Honestly, I don't really want to send a new request anyway. I want the results directly off the exisiting page. Does anyone have a way?

Here's the querytable code (which if memory serves, Tony Jollans helped me with ages ago.):
Function RetrieveQueryTable() As Boolean
'Requires reference to Microsoft Internet Controls
'Should work with any web table, provided it is not in a frame!
'Can change the coreURL to be assigned from a worksheet
Dim sws As SHDocVw.ShellWindows
Dim ieDoc As Object
Dim objWeb As QueryTable

'Incoporate error handling
On Error GoTo ExitRoutine

'Establish link to IE application
Set sws = New SHDocVw.ShellWindows

'Assign URL in IE window to a string
Set ieDoc = sws.Item(0).Document

'Retrieve the web table from the IE window
If Not ieDoc Is Nothing Then

'Clear the sheet and create the target range
With Worksheets("QueryTarget")
.Cells.Clear
Set objWeb = .QueryTables.Add( _
Connection:="URL;" & ieDoc.URL, _
Destination:=.Range("A1"))
End With

With objWeb
.WebSelectionType = xlSpecifiedTables
.WebTables = "6" ' Identify your HTML Table here
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Else
MsgBox "Sorry, I can't find an Internet Explorer document", vbOKOnly + vbCritical
End If

ExitRoutine:
If Err.Number <> 0 Then
RetrieveQueryTable = False
Else
RetrieveQueryTable = True
End If

On Error GoTo 0

Set objWeb = Nothing
Set ieDoc = Nothing
Set sws = Nothing
End Function

[lucas]

Not much different than what you have Ken. Matt is the man on this kind of problem.
Sub gethtmltable()
Dim objWeb As QueryTable

Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://www.vbaexpress.com/kb/default.php", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "2" ' Identify your HTML Table here
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

[Ken Puls]

Hmmm... where is he, anyway? :think:

[mvidas]

Here I am! Still need help?
Though I do prefer to stay away from both IE and querytables .... :) Hopefully I can take the querytable portion out of the process

[Ken Puls]

I do, indeed. :)

I'd love to see a non-query table angle at this. It's the only way I knowt to get info back from IE, so pretty much fails if you have a password protected site. :(

[mvidas]

Ok :)

A couple things to note before you go pulling an active IE page
-What if you have multiple IE pages open? I'll be the first to admit I've never used an "SHDocVw.ShellWindows" object, but it doesnt look like you're pulling from a specific one.
-Do you have only one IE window open? If not, it looks like it will be pulling from the first opened one.
-I believe windows explorer uses an IE object as well, if you are like me and always have 1 or 2 explorer windows open (at least when I'm at work), which rarely if ever close, those will always be found first

Why do you want to pull from an active window anyways? So you can use it on any table? It might be beneficial to loop through open ones and say "Found one with address '<address>' - Do you want to pull from this one?" Also, I dont know how many pages have 6 tables on them ... if you're hard coding the table number you might as well hard code the page too. Just a thought
I may be missing something, as I'm hungry and its about a minute until lunch. I'll check back after :)

Oh, and just for old times sake... :shifty:

[Ken Puls]

LOL!

Here's the deal of what's going on:
-Log in to site with valid credentials
-Do a search for specifics
-Get back a serach page with results
-Copy the data into Excel
-Manipulate and play with it

The problem with the query table is that it requests the information from the URL directly, and I can't get through that way.

If I have to have only one IE or explorer window open, no big deal. I can live with that... SHDocVw.ShellWindows... this is sounding familiar... Can you get the table from it though? Last time I looked at it for someone, we were parsing the HTML code right out of it I think...

[Norie]

Ken

Have you considered automating IE?

Here's an example:


Sub Test()

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "http://data.bls.gov/cgi-bin/srgate"
Do Until .ReadyState = 4: DoEvents: Loop
Set myTextField = .document.all.Item("series_id")
myTextField.Value = "ECU11121I"
ie.document.forms(0).submit
Do Until .ReadyState = 4: DoEvents: Loop
Do While .busy: DoEvents: Loop
Set doc = ie.document
GetAllTables doc
.Quit
End With
End Sub

Sub GetAllTables(d)
For Each e In d.all
If e.nodename = "TABLE" Then
Set t = e

tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("B" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
End If
Next e
End Sub

[mvidas]

SHDocVw.ShellWindows... this is sounding familiar...
I got it from your code above! :)

Anyways.... yes to avoid the query table, you would have to play with the html. As a start (in order to help with the table parsing further, I would need to know what it looks like):
Function RetrieveQueryTable() As Boolean
'Requires reference to Microsoft Internet Controls
'Should work with any web table, provided it is not in a frame!
'Can change the coreURL to be assigned from a worksheet
Dim SWs As SHDocVw.ShellWindows, vIE As SHDocVw.InternetExplorer

'Incoporate error handling
On Error GoTo ExitRoutine

'Establish link to IE application
Set SWs = New SHDocVw.ShellWindows

For Each vIE In SWs
If Left(vIE.LocationURL, 4) = "http" Then 'avoid explorer windows/etc this way
If MsgBox("IE Window found. The URL is:" & vbCrLf & vIE.LocationURL & vbCrLf & _
vbCrLf & "Do you want to see the html?", vbYesNo) = vbYes Then

'Show html in a msgbox
MsgBox vIE.Document.Body.innerHTML

'Or put it to a file
'dim vFF as long
'vff=freefile
'open "C:\thehtml.txt" for output as #vff
'print #vff,vie.document.body.innerhtml
'close #vff
End If
End If
Next

ExitRoutine:
If Err.Number <> 0 Then
RetrieveQueryTable = False
Else
RetrieveQueryTable = True
End If

On Error GoTo 0

Set SWs = Nothing
Set vIE = Nothing
End Function
Since you already have IE open and want whats on the visible page, there is no reason to not use the IE object. Since it seems like there is a specific type of table you want on the results (based on table #6), we should be able to find the pattern of the table in the html and parse it from there. if its a sensitive site then either email me the file, post it here then delete it later, or parse it yourself. I'd recommend regexp for it, but I recommend that for everything. I'd brush my teeth with it if i could

[mvidas]

Sorry, Norie, been a while since i refreshed the page :)

[Ken Puls]

I got it from your code above! :)

I've been hanging around Zack too long. :doh:

I'll take a look again tonight and see if I can make one of those work. Been too long since I played with it now. ;)

[StreetTrader]

Hi there!

thanks mvidas for the code, that one works for me. Actually, i've posted a similar inquiry to other forum (i've stated what i do manually and ask vba expert on other forum for the vba code). I'm lucky to find the code here in vbaexpress without even posting an inquiry.

Anyways, I would like to ask if its possible to revised the code below , instead of looking at all open brower or internet explorer, it will get the html code of the specific url?



For Each vIE In SWs
If Left(vIE.LocationURL, 4) = "http" Then 'avoid explorer windows/etc this way
If MsgBox("IE Window found. The URL is:" & vbCrLf & vIE.LocationURL & vbCrLf & _
vbCrLf & "Do you want to see the html?", vbYesNo) = vbYes Then

'Show html in a msgbox
MsgBox vIE.Document.Body.innerHTML

'Or put it to a file
dim vFF as long
vff=freefile
open "C:\thehtml.txt" for output as #vff
print #vff,vie.document.body.innerhtml
close #vff
End If
End If
Next


Also, i want vba to automatically input the text (the one in red font) or change the text of the input box then click go or press enter and get the html code for me and instead of saving it as open "C:\thehtml.txt", the filename should be the same as the value or text in the input box PLUS the time. I need to include the time cause i have to get the information every 3 mins or less depends upon the activity of the stock, then i will have a post analysis after the end of trading.

Below is a part (i think) that vba should control to change the text

<FORM name=search onsubmit="return ValidateSearch(this);" action=quotes.asp method=get>
<INPUT size=10 value=ABCDEFGHIJ name=code> <INPUT type=hidden value=SI name=type> </FORM>
</DIV>
<DIV id=golinks><B><A href="javascript:submitForm(document.search);">GO</A> |
</B><A href="javascript:SymbolGuide();">SYMBOL GUIDE</A> </DIV>
<SCRIPT language=javascript>
document.search.code.focus();
</SCRIPT>


Thanks in advance for your help..

by the way, i don't have any programming background.



I got it from your code above! :)

Anyways.... yes to avoid the query table, you would have to play with the html. As a start (in order to help with the table parsing further, I would need to know what it looks like):
Function RetrieveQueryTable() As Boolean
'Requires reference to Microsoft Internet Controls
'Should work with any web table, provided it is not in a frame!
'Can change the coreURL to be assigned from a worksheet
Dim SWs As SHDocVw.ShellWindows, vIE As SHDocVw.InternetExplorer

'Incoporate error handling
On Error GoTo ExitRoutine

'Establish link to IE application
Set SWs = New SHDocVw.ShellWindows

For Each vIE In SWs
If Left(vIE.LocationURL, 4) = "http" Then 'avoid explorer windows/etc this way
If MsgBox("IE Window found. The URL is:" & vbCrLf & vIE.LocationURL & vbCrLf & _
vbCrLf & "Do you want to see the html?", vbYesNo) = vbYes Then

'Show html in a msgbox
MsgBox vIE.Document.Body.innerHTML

'Or put it to a file
dim vFF as long
vff=freefile
open "C:\thehtml.txt" for output as #vff
print #vff,vie.document.body.innerhtml
close #vff
End If
End If
Next

ExitRoutine:
If Err.Number <> 0 Then
RetrieveQueryTable = False
Else
RetrieveQueryTable = True
End If

On Error GoTo 0

Set SWs = Nothing
Set vIE = Nothing
End Function
Since you already have IE open and want whats on the visible page, there is no reason to not use the IE object. Since it seems like there is a specific type of table you want on the results (based on table #6), we should be able to find the pattern of the table in the html and parse it from there. if its a sensitive site then either email me the file, post it here then delete it later, or parse it yourself. I'd recommend regexp for it, but I recommend that for everything. I'd brush my teeth with it if i could