Getting data from IE

[David Johnston]

Here, at work, we have an intranet with pages that query alarms and
other types of data stored in SQL through ASP pages. What I have done
in the past is just create a web query and append the url with data in
certain cells to import the data I need. Now, it seems that something
has changed with the way our company is making our pages since now
when you click the submit button on the newer pages, it opens the
results in a new window and the variables are not attached to the url
as before. Hence, I cannot simply query the page by appending the url
with data in cells.

So far, I have inserted a webbrowser control and navigate it to the
page that I want to get the data from. I then fill in the variables
using VBA and then click the Submit button, also using VBA. At that
point, the new window opens with the resutls. That's as far as I can
figure out. I would assume that there would be a way to set the focus
to the new window and copy the innerhtml back to Excel using VBA... I
just don't know what that way is and I haven't been able to find
anything on the web to help out. So, I'm stuck. Any ideas?

Thanks in advance,
David

 

[Tim Williams]

Try the function below. That should allow you to get a reference to the new
window.
You can then work with the document contained in it.

Tim
'***********************************
'Return an IE window with matching location
Function GetIE(sAddress As String) As Object

Dim objShell As Object, objShellWindows As Object, o As Object
Dim retVal As Object, sURL As String


Set retVal = Nothing
Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows

'see if IE is already open
For Each o In objShellWindows
sURL = ""
On Error Resume Next
sURL = o.document.Location
On Error GoTo 0
If sURL <> "" Then
If sURL Like sAddress & "*" Then
Set retVal = o
Exit For
End If
End If
Next o

Set GetIE = retVal
End Function
'*************************************