Navigate querytable with username & password 1

[osx99]

The code below works fine, which opens a webpage enters my username and password and navigates to the homepage

Function FillInternetForm()
  Dim IE As Object
  Set IE = CreateObject("InternetExplorer.Application")
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
  IE.Navigate "[URL unfurl="true"]https://reg.racingpost.co.uk/cde/login_iframe.sd"[/URL]
'go to web page listed inside quotes
  IE.Visible = True
  While IE.Busy
    DoEvents  'wait until IE is done loading page.
  Wend

  IE.Document.all("in_un").Value = "enter username"
  IE.Document.all("in_pw").Value = "enter password"
  IE.Document.all("submit").Click
  
    While IE.Busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
IE.Navigate "[URL unfurl="true"]http://www.racingpost.co.uk/news/home.sd"[/URL]
End Function

The rest of my code then attempts to extract data using querytables but it doesn't recognise that I have logged in from within the querytable.

I physically have to choose >Import External Date / Edit query and re-type my username and password into the Edit Web Query window

My code then works fine

My question is - how can I automate the signing in from within the webquery?

either in

Function GetSource(sURL As String) As String

Dim oXHTTP As Object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "GET", sURL, False
oXHTTP.send
GetSource = oXHTTP.responseText
Set oXHTTP = Nothing

End Function


Function getHTML()

Dim s As String
Dim arr
Dim i As Long
Dim sht As Worksheet

s = GetSource("[URL unfurl="true"]http://www.racingpost.co.uk/horses/a_days_racing.sd?r_date="[/URL] & Format(Now, "yyyy-m-d"))

arr = Split(s, vbLf)
Set sht = ThisWorkbook.Sheets("Sheet1")
Range("A1").Select
Range("A:Z").Delete
For i = LBound(arr) To UBound(arr)
sht.Range("A1").Offset(i, 0).Value = arr(i)
Next i

End Function

or in

Function getRaceLinks(webaddressgetracelinks As String)
Dim s As String
Dim arr
Dim i As Long
Dim sht As Worksheet

s = GetSource(webaddressgetracelinks)

arr = Split(s, vbLf)
Set sht = ThisWorkbook.Sheets("Sheet1")
Range("A1").Select
Range("A:Z").Delete
For i = LBound(arr) To UBound(arr)
sht.Range("A1").Offset(i, 0).Value = arr(i)
Next i
Range("c1").Select

Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & webaddressgetracelinks, Destination:=ActiveCell)
With qt
.FieldNames = True
.RowNumbers = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SaveData = True
.AdjustColumnWidth = False
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
'.WebTables = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With
'QT.Refresh BackgroundQuery:=False


Range("c1").Activate
'Next x
    
End Function

 

[sandoztek]

Hi ox99,
It's really funny that I posted a similar problem while also using the same RacingPost URL !
( I want to retrieve the Betting Forecast for all the races of the day)
My problem was that I could not login with

http://www.racingpost.co.uk/news/home.sd

(in_un & in_pw could not be found there, I don't understand why)
By chance I found

https://reg.racingpost.co.uk/cde/login_iframe.sd,

with which the login now works.
Unfortunately afterwards, no data are retrieved, the login has to be done manually.
It looks like one page is ignoring the login from the other one ...

 

[sandoztek]

Hi,
Have a look at my thread
Password & Login with WebQuery,
You'll understand why your( and mine) code doesn't work.

 

[osx99]

sandoztek (Programmer)

Soory for late reply... you're a star, this link is PERFECT!

Thank you.