[Excel 2010] Import data from current web page

[wil0004]

Hi all,

so what I am looking for is to be able to run something like a webquery but for the currently open web page as I have written a code (below) to login into a particular website but when using the traditional querytables command it is unable to connect to the site because the site wont allow access without login... So ultimately I need the data to be pulled from the open IE window.

Sub IE_login()
    Dim ie As InternetExplorer
    Dim C
    Dim ULogin As Boolean, ieForm
    Dim MyPass As String, MyLogin As String
    Dim mypost As String


redo:
    MyLogin = Range("a1")
    MyPass = Range("a2")

    Set ie = New InternetExplorer
    ie.Visible = True
    ie.Navigate "https://www.cmcmarketsstockbroking.com.au/logon.aspx"

    'Loop until ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop

    'Look for password Form by finding test "Password"
    For Each ieForm In ie.Document.forms
        If InStr(ieForm.innertext, "Password") <> 0 Then
            ULogin = True
            'enter details
            ieForm(0).Value = MyLogin
            ieForm(3).Value = MyPass
            'login
            ieForm.submit
            
            GoTo datadl
            Exit For
            End If
            Next
            
datadl:


        ie.Navigate "https://www.cmcmarketsstockbroking.com.au/net/ui/Research/Research.aspx?asxcode=" & Range("a4") & "&view=historical"
        Do Until ie.ReadyState = READYSTATE_COMPLETE
        Loop
        



End Sub

Thanks for any help,

Ash.

[westconn1]

you need to wait for the login submission to complete before navigating to a page that requires login

without being able to login to the site, i can not test any code, also would need to know what data you require from the page
after logging in your web query may succeed, depending whether the login is limited to a single instance of a browser

[wil0004]

Thanks for the reply,


I have altered the code to access Vbforums (So you can test the code), login, navigate to reply to this post and then run a web query and as you will see from the data that is placed into excel the web query is blocked by the site requesting login details

This is the exact same problem I am having with the other site.

Here is the code (Its messy, far from sleek and does take some time to log in and grab the data [maybe 30 seconds all up??])

Things that need to be done before running this code:
1. Add Microsoft Internet controls and Microsoft HTML Object Library references

2. cUsername and cPassword will be your personal details

Sub Test()

    Const cURL = "http://www.vbforums.com/"
    Const cUsername = "XXXXXX"
    Const cPassword = "XXXXXX"
    
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    Dim qt As QueryTable
        
    Set IE = New InternetExplorer
    
    IE.Visible = True
    IE.Navigate cURL
    
    'Wait for initial page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Set doc = IE.Document
    
    'Get the only form on the page
    
    Set LoginForm = doc.forms(0)
    
    'Get the User Name textbox and populate it
 
    Set UserNameInputBox = LoginForm.elements("vb_login_username")
    UserNameInputBox.Value = cUsername
    
    'Get the password textbox and populate it

    Set PasswordInputBox = LoginForm.elements("vb_login_password")
    PasswordInputBox.Value = cPassword
    
    With IE
    .Document.forms(0).submit
    End With
            
    'Wait for the new page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    'Get the HTML document of the new page
    
    Set doc = IE.Document
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
     
    IE.Navigate "http://www.vbforums.com/newreply.php?do=newreply&noquote=1&p=3971107"
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Dim sht As Worksheet, rng As Range
Dim strName As String
strName = ActiveCell.Value

With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.vbforums.com/newreply.php?do=newreply&noquote=1&p=3971107", Destination:=Range("B10"))
.Name = "ASX:" & strName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2,3,4,5,6,7,8,9,10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Application.ScreenUpdating = True
Range("a1").Activate
    
    End Sub

Thanks for your time,

Ash.