connecting and logging into a web page

[Chrissirhc]

Hi,


How do you automatically connect, log in to a website, navigate and then download a specific table into a range in a spreadsheet?

Thanks,

Chris

 

[SkipVought]

Hi,

Copy the URL.

In Excel, Data/Get External Data/New Web Query...

Paste in the URL or Browse.

If you have Excel 2003, you can see and select a specific table. Otherwise, you'll have to experiment with various table choices (1, 2, 3...)

Skip,

_[red][/red]

 

[Chrissirhc]

Ok thanks. I wanted to log in using a user name and password... However, I have a further question at the bottom of this post.

Here is the code I found / amended (just in case anyone needs something similar in future)

Sub LogInToSite()
Dim ie As InternetExplorer
Dim myTextField As Variant
Dim IDispatch As Variant
Dim doc As Variant
Set ie = New InternetExplorer
'Set ie = CreateObject("InternetExplorer.Application")

Sheets("results").Activate

With ie
.Visible = False
.Navigate "

http://www.thesite.co.uk/member/index.aspx"

Do Until .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Set myTextField = .Document.all.Item("tbUserName")
If myTextField Is Nothing = False Then ' We assume we are the login page.
myTextField.Value = "chris"
Set myTextField = .Document.all.Item("tbPassword")
myTextField.Value = "xxxxxxx"

ie.Document.forms(0).btnlogin.Click
End If

'ie.Document.forms(0).submit

'Document.forms(0).btnlogin.Click
Do Until .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Do While .Busy: DoEvents: Loop
Set doc = ie.Document
'Exit Sub
GetATable doc, "b3", 18, "PortfolioHighlights"

'Dirty way to get to the main details. Renavigate. Maybe change to cliking on the link instead.
.Navigate2 "

http://www.thesite.co.uk/member/stuff.aspx?lang=EN&stuff_id=343253&tab=0"

Do Until .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Set doc = ie.Document

GetATable doc, "B7", 16, "StuffDetails"

' GetAllTables doc

End With

ie.Quit
End Sub

Sub GetATable(d As Variant, theStartCell As String, tableNo As Integer, rangeName As String)
Dim t, e, c, r, I As Variant
Dim tabno, nextrow As Integer
Dim count As Integer
Dim rng, startCell As Range

Set startCell = Range(theStartCell)

count = 0
tabno = 0
nextrow = 0
For Each e In d.all
count = count + 1
Debug.Print e.nodename
If e.nodename = "TABLE" Then

' Stop
Set t = e

tabno = tabno + 1

If tabno = tableNo Then

Set rng = startCell
' rng.Offset(0, -1) = "Table " & tabno
rng.Select
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(0, 1)
I = I + 1
Next c

Set rng = rng.Offset(1, -I)
I = 0
Next r
End If
End If
Next e

'Stop

'resize range
Set rng = startCell
rng.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set rng = Selection
Names.Add rangeName, rng


End Sub

The problem is that the second .navigate I use, the .readystate is already READYSTATE_COMPLETE, which means it doesn't wait for the page to load. Unfortunately .readystate is readonly, so I can't write to it. How do I get around this?

Thanks in advance,

Chris

 

[Chrissirhc]

I think the answer to my own question is to use

Do While .Busy: DoEvents: Loop

Strange I don't see .busy in MSDN... so I'm not sure I like it...

 

[SkipVought]

I have never created an IE object. I might just learn something from this post.

Please be sure to post any solutions you might come up with.

Skip,

_[red][/red]

 

[PHV]

Strange I don't see .busy in MSDN

http://msdn.microsoft.com/workshop/browser/webbrowser/reference/properties/busy.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

Chrissirhc,
In my experience it's best to do both, like in the original call:

Do Until .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Do While .Busy: DoEvents: Loop

WebBrowser Object Busy Property

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)