Get data from web and paste into Excel

[Robin121]

How do I go about to log on to a webpage (username/password; I am a member) go to a certain page on the site, fill in the form on the page with a number specified by me, copy the resulting page and finally paste it into Excel 2007?

...then I'd like to capture this with procedure in a macro and call it by pushing a VBA-button, being able to refresh the data...

Anyone suggestions?
Thanks!

 

[Ruddles]

We'd probably need to see the page post-login to provide code for this but I guess you're not going to hand over your username and password!

As an example, here's a script which just logs in to Yahoo:-

Option Explicit
Option Compare Text
 
Const MyUserName As String = "[B][COLOR=red]ruddles[/COLOR][/B]"
Const MyPassword As String = "[COLOR=red][B]secret[/B][/COLOR]"
Const READYSTATE_COMPLETE As Integer = 4
 
Dim objIE As Object
 
Public Sub LoginToYahoo()
  
  Set objIE = CreateObject("InternetExplorer.Application")
  
  With objIE
    .Visible = True
    .Silent = True
    .Navigate ("[URL]https://login.yahoo.com/config/login[/URL]")
    Do Until .ReadyState = READYSTATE_COMPLETE
      DoEvents
    Loop
    Application.Wait Now() + TimeValue("00:00:02")
    If InStr(objIE.document.body.innerhtml, "Not " & MyUserName & "?") = 0 Then
[B][COLOR=magenta]      .document.all.login.Value = MyUserName
[/COLOR][/B]    End If
[B][COLOR=magenta]    .document.all.passwd.Value = MyPassword
    .document.forms(0).submit
[/COLOR][/B]    Do Until .ReadyState = READYSTATE_COMPLETE
      DoEvents
    Loop
    Application.Wait Now() + TimeValue("00:00:02")
  End With
 
[COLOR=green]  ' objIE.Quit
  ' Set objIE = Nothing
[/COLOR]  
End Sub

Unless someone comes up with a more complete solution, perhaps this will get you started. To test it as it stands - to satisfy yourself that it's actually possible - insert your Yahoo username and password in place of the bits in red and run the procedure.

The bits in pink manipulate the username and password fields and click the Sign In button. When you modify the code to work with your Web site, you'll need to look at the page HTML to find out what the username and password fields are called - they're login and passwd in the Yahoo example but they will probably be something different on your Web site - and also what sort of button you've got so that you can use the correct method to click it: it's .submit in the Yahoo example.

 

[Robin121]

Ruddles,

Thanks for your response.
To make things easier I hope: the site doesn't need a username/password, since it's generally accessible and there's no private info posted.
Just view: http://www.knltb.nl/cms/showpage.aspx, and choose 'Speelsterkte/Spelersprofiel' from the dropdown menu at 'Snelmenu' .
There you provide a 'Bondsnummer' (e.g. 23985631) and from the next page I'd like to extract several posted data and store it into an exisiting Excel-file. This I'd like to repeat for several 'Bondsnummers' in a row. Does that help?

Let me know if you need more info.