PDA

View Full Version : web query



Martinw
12-13-2012, 03:07 PM
I am trying to update my webquery (xml feed is unreliable for reasons unknown) to MSXML2.XMLHTTP

I have come across the following code:-

Formhtml = ExecuteWebRequest("https://www.ozbet.com.au/UISecure/ToteUI/RaceTote.aspx?fixtureId=" & Sheets("ID").Range("a1").Value & _
"&fixtureDate=" & Sheets("ID").Range("a2").Value & "&contestNumber=" & Sheets("ID").Range("a3").Value) ' runs the first function.

I know the above works.

I have tried to modify as below:-

Dim WA As String
Sub queryhtml(wks As Worksheet)
WA = wks.Range("AF1").Value
Formhtml = ExecuteWebRequest(WA)

It will not run the Formhtml function; just skips it and exits the sub

Below is the function it calls


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

Any help would be greatly appreciated as I am completely out of my depth here.

Ta,
Martin

Jan Karel Pieterse
12-13-2012, 10:05 PM
Is the function ignored entirely, or what happens *exactly*?

Martinw
12-13-2012, 10:42 PM
It gets the url string, creates http connections and imports the webpage.

Martinw
12-13-2012, 10:46 PM
It gets the url string, creates http connections and imports the webpage.
It is then followed by another function

outputtext (Formhtml)

It gets the contents held in the file created by the second function and Creates a temp txt file to hold the contents of the webpage.

The code is as below

Public Function outputtext(text As String)
Dim MyFile As String, fnum As String
MyFile = ThisWorkbook.Path & "\temp.txt"
fnum = FreeFile()
Open MyFile For Output As fnum
Print #fnum, text
Close #fnum
End Function

Ta,
Martin

Jan Karel Pieterse
12-14-2012, 06:08 AM
I'm sorry for not being clearer. I was referring to your remark:

"It will not run the Formhtml function; just skips it and exits the sub"

Do you mean you F8 through the code, get to that statement and then the next F8 just jumps over the statement?

Martinw
12-14-2012, 06:18 AM
yes

Martinw
12-14-2012, 06:49 AM
Just had another try and it gets to oXHTTP.send in the Public Function ExecuteWebRequest and then exits - does not go onto ExecuteWebRequest = oXHTTP.responseText

Martinw
12-14-2012, 03:37 PM
Have been trying different things such as

Dim WA As String
WA = wks.Range("AF1").Value 'this has the url for each worksheet in it

and then in

Public Function ExecuteWebRequest(url As String) As String
Dim oXHTTP As Object
' Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
Set oXHTTP = New MSXML2.XMLHTTP
oXHTTP.Open "GET", WA, False 'note WA has replaced url
oXHTTP.send
ExecuteWebRequest = oXHTTP.responseText
Set oXHTTP = Nothing
End Function

I have replaced url with WA in oXHTTP.Open "GET", WA, False but nothing happens. Trouble is I do not understand, or know, much about MSXML2.XMLHTTP. Nor, it seems, does MS help.

When I F8 through the code it goes straight from oXHTTP.send to then next sub. Even putting a stop at the bottom of Sub queryhtml does not stop it.

Any help would be greatly apprciated as I am truly lost as usual.

Ta,
Martin

Jan Karel Pieterse
12-17-2012, 01:15 AM
This means an error occurs when you try to issue the Send command. This may be related to how the particular webservice wants to communicate.
Without an example of your URLs this is impossible to troubleshoot I'm afraid.

Martinw
12-17-2012, 01:29 AM
Hi Jan,
Thanks for the reply.
You are right there was a fault in my url. Fixed that and then found the page is too slow in replying. So I am going to have to go to xml - which is not very reliable. But that is the host's problem - not updating the data consistently. And I am getting the data for free so cannot really complain.
I have another site I can access but it has a security key on it. I can have the key but writing the code is beyond me at this stage. So I will continue using web query from my current source. It works fine but do not know how long it will be available.
Thanks again
Martin

Jan Karel Pieterse
12-17-2012, 06:32 AM
OK. You're welcome of course.