Hi all, just wondering is there a way i can use my current web query to import url's as an address, currently i import it as full html then i have a function that turns the hyperlink in to plain text address, here is the codes i use for my queries.
The bottom query imports data nice and fast, but as plain text, if i try to use it on pagers where i want to use the urls on the page it just imports data that is displayed not the url address.
The bottom query imports data nice and fast, but as plain text, if i try to use it on pagers where i want to use the urls on the page it just imports data that is displayed not the url address.
Rich (BB code):
Public Function ExecuteWebRequest(url As String) As String
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "GET", url, False
oXHTTP.send
ExecuteWebRequest = oXHTTP.responseText
Set oXHTTP = Nothing
End Function
Rich (BB code):
Rich (BB code):
Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function
Rich (BB code):
Dim objWeb As QueryTable
Set objWeb = ActiveSheet.QueryTables.Add(Connection:="URL;https://tatts.com/racing/" _
& Format(Sheets("Selections").Range("D3").Value, "DD/MM/YYYY") & "/RaceDay", _
Destination:=Range("$A$1"))
With objWeb
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "2"
.SaveData = True
.Refresh BackgroundQuery:=False
End With
Set objWeb = Nothing
Rich (BB code):
formhtml = ExecuteWebRequest(ThisWorkbook.Sheets("Meetings").Range("V1").Value)
outputtext (formhtml)
Set temp_qt = ThisWorkbook.Sheets("Race").QueryTables.Add(Connection:= _
"URL;" & ThisWorkbook.Path & "\temp.txt" _
, Destination:=ThisWorkbook.Sheets("Race").Range("$A$1"))
With temp_qt
.RefreshStyle = xlOverwriteCells
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "13,17"
.SaveData = True
.Refresh BackgroundQuery:=False
End With
Set temp_qt = Nothing
Kill ThisWorkbook.Path & "\temp.txt"