Recording Web Navigation with VBA

[kingguga]

I am trying to update information from a webite on a daily basis that I need a username and password for and that I need select a specific date range for. Both of these require that I manually input things in order to get the output I am looking for. Unfortuantly, I don't think excel is capable of recording internet VBA. Is there another way around this? Web queries only work in non-password sites and when I don't have to mannually adjust date ranges. Thanks!

 

[Ruddles]

It's quite possible to write a VBA program to get through a login screen - for example the following code logs you in to Yahoo!:-

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

Note the way that the username and password are entered into the boxes and theway the Sign In button is pressed.

If you can post the HTML of the login screen, I might be able to give you some suggestions.

 

[kingguga]

Your code works excellently. I was able to login automatically by making the adjustments to where you put "username" and "password".

The other issue I have is that sometimes data does not get loaded in very well via the web query (some data doesn't have the green box to check mark next to it). Is there anyway just to highlight this data or have the VBA just to a control A on the webpage to capture it?

Finally, sometimes I have other options that I need to adjust like selecting certain boxes and manipulating dropdowns. An example is located at the following link: http://www.exchangerate.com/past_ra...rom=&date_to=12-09-2010&action=Generate+Chart

When you go here you will see boxes that you can choose a date range for. How can those be adjusted? Thanks for your insightfulness!

 

[Ruddles]

Glad your navigation problem is sorted!

Re the Web query: sorry, that's outside my area of knowledge. Hopefully there's someone else watching who can help or, as I guess this is now a different question, you could start a new thread with "Web query" in the title and that might attract more responses.

Sorry I couldn't be more helpful.