Get a response from the server (convert excel code to calc)

[Kreol2013]

Greetings!
I have a code of Excel ask for help in converting it to librecalc macro
The code checks the server response to url
I would be very grateful for the help

Code: Select all   Expand viewCollapse view

Function GetURLstatus(ByVal URL$) As Long
   On Error Resume Next: URL$ = Replace(URL$, "\", "/")
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
    xmlhttp.Open "GET", URL, "False"
    xmlhttp.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
    xmlhttp.send
    If Val(xmlhttp.Status) = "200" Then
        GetURLstatus = "1"
    Else
        GetURLstatus = "0"
    End If
    Set xmlhttp = Nothing
End Function


[rudolfo]

OpenOffice Basic can use ActiveX COM objects with its builtin Statement CreateObject.
The core functionality in your function xmlhttp.Open and xmlhttp.send should work as it is in OpenOffice.
SET is VBA specific, you better remove it in OpenOffice. Set is silently ignored by OO Basic, but Nothing will probably let it fail. Remove this last line with "set nothing".
And it seems you got the types wrong. Your function says it returns a long, but it actually returns a string: "0" or "1".
As xmlhttp.Status is the http status code of the request I would rather guess that it is a numeric value and not a string. Try testing: xmlhttp.Status = 200 instead of using Val.

Or in short: Debug your code.

[Kreol2013]

Thanks, but for some reason at this writing all the time Status "1"

Code: Select all   Expand viewCollapse view

Function GetURLstatus(ByVal URL$) As String
   On Error Resume Next: URL$ = Replace(URL$, "\", "/")
   Dim oHttp As Object
   Set oHttp = CreateObject("MSXML2.XMLHTTP")
    oHttp.Open "GET", url, False
    oHttp.Send
    If oHttp.Status = 200 Then
        GetURLstatus = "1"
    Else
        GetURLstatus = "0"
    End If
    Set oHttp = Nothing
End Function

[rudolfo]

Is there any reason, why you have changed the name of the ActiveX object from "Microsoft.XmlHttp" to "MSXML2.XMLHTTP"? If you have problems and try to find a solution it is always a good idea to keep the particular piece of code as simple as possible. So make sure you have read the documentation for this ActiveX object on Microsoft MSDN.

You are using the line On Error Resume Next which tells the Basic interpreter to silently ignore all errors that happen during the execution of a statement/line. If you use this directive you need to have a good reason for it. Normally that you don't depend on this information, because you can evaluate return codes that give you the same information (or nearly the same information).

This is not the case here: The two statements that are likely to cause errors oHttp.Open and oHttp.Send do not return anything. If you have read the documentation you will know that the Send starts the actual http request and does all the network I/O. If a firewall blocks the requests, if your computer looses connectivity, if the URL is illegal ... Send will throw an exception. If the remote server was never reached the http request is not completed and hence there isn't an http status code. oHttp.status can have any random value depending on how the initialization of the oHttp object was done. In any case nothing you can rely on. What you need is:

Code: Select all   Expand viewCollapse view

Function GetURLstatus(URL) As Integer
   Dim oHttp As Object
   oHttp = CreateObject("MSXML2.XMLHTTP")

   On Error Goto HttpError

   oHttp.Open "GET", url, False
   oHttp.Send

   If oHttp.Status = 200 Then
       GetURLstatus = 1
   Else
       GetURLstatus = 0
   End If

HttpError:
   GetURLstatus = -1

End Function

And one final remark: I hope you are aware that this question and problem has nothing to do with OpenOffice. It is a pure Basic Language and ActiveX related question.

[Kreol2013]

Using your code now, all references to the status of -1.
I understand that this is not an issue open office.
I'm trying to understand how to run a macro.
Thanks for your help

[rudolfo]

If you see the -1 as return code the XMLHttp object has thrown an exception. But this is an error on the ActiveX side and you won't get much information in OpenOffice Basic about this exception of the ActiveX object.
But you need this error details, otherwise you don't know what you are supposed to fix or change. The only logical step that you have left is to execute the same methods of the ActiveX object in a different environment where you have the chance to get some further information. The Windows Scripting Host and the Powershell can serve for this purpose.
Visual Basic Script differs only in some details from the Basic interpreter of OpenOffice. Thus create a file testHttp.vbs:

Code: Select all   Expand viewCollapse view

Dim oHttp
Dim sUrl

Set oHttp = CreateObject("Microsoft.XmlHttp")

WScript.Echo getStatusOfUrl( oHttp, "http://www.postelnoe.vn.ua/index.php/percal/view/category/virtuemart_category_id/208")

WScript.Echo getStatusOfUrl( oHttp, "http://www.postelnoe.vn.ua/index.php/percal/view/productdetails/virtuemart_product_id/697/virtuemart_category_id/208")

Function getStatusOfUrl( http, sUrl)

  On Error Resume Next

  http.open "GET", sUrl, FALSE
  http.send ""

  If Err.Number <> 0 Then
    WScript.Echo Err.Source & " (" & CStr(Err.Number) & ") " & Err.Description & ": Failure to retrieve " & sUrl
    Err.Clear
    getStatusOfUrl = -1
  Else
    getStatusOfUrl = http.status
  End If

End Function


VBScript has a significant limitation compared to its big brothers Visual Basic and Visual Basic for Applications: You can't specify the type of variables and On Error Goto SomeJumpMark is not supported. If you want to avoid that an error aborts your script you can only use On Error Resume Next. As you know from my last post this means that you have to check the return codes of any statement and make sure that they have successfully done what they are supposed to do. The good thing is that VBScript has an global error object Err where all well behaved ActiveX objects leave their failure conditions if they have run into a runtime error. The above code inspects this error object for details.

It might be interesting for you to learn to know that my tests to the www.postelnoe.vn.ua URLs failed in the first run with -1, but returned a successful 200 when I executed the script a second time. Ah right, to execute this VBScript you need to open a command prompt and type:
cscript testHttp.vbs

[wouterm]

And one final remark: I hope you are aware that this question and problem has nothing to do with OpenOffice. It is a pure Basic Language and ActiveX related question.

Well, maybe my question does have something to do with OpenOffice. I've got this simple function:

Code: Select all   Expand viewCollapse view

Function GetHTML(URL As String) As String
    Dim HTML As String
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .Send
        GetHTML = .ResponseText
    End With
End Function

Works like a charm under VBA/Excel, but gives mixed results in Calc. Works fine when I test it with "http://www.google.com", but fails when I test it with "http://ww.bol.com"(the one I want). Looks like it has something to do with redirects(the obvious diference between them), but I really do not see why.

[Villeroy]

OpenOffice comes Python and Java script as additional macro languages.

[rudolfo]

Looks like it has something to do with redirects(the obvious diference between them), but I really do not see why.

Why should there be a difference in redirects between MS Office VBA and OpenOffice StarBasic if you use the same ActiveX object?

Read the MSDN documentation about the XMLHttp Object (that I have referenced in one of my previous posts) and you will learn that the ActiveX object does only what it is told to do. It executes exactly one (that is only a single one) http request. HTTP is a well documented protocol, you don't need to guess about "Redirects" (see http://www.w3.org/Protocols/rfc2616/rfc ... ml#sec10.3)

If you expect that it is related to the remote end (different answers on the same kind of GET request) you either have to follow Villeroys advice to use a toolkit and programming language that can give you more details about the http request (like python) or you have to use a network packet capturing tool like tcpdump or wireshark to capture the traffic between your User-Agent client (aka browser) and the remote Web-Server.

And even the Microsoft ActiveX object "Microsoft.XmlHttp" (better use this generic COM identifier string instead of messing around with version numbers as in "MSXML2.XMLHTTP") returns a status code. You just have to evaluate it. It's in .status. This is the member variable to test if you have a 3xx redirect code or not.

[wouterm]

Why should there be a difference in redirects between MS Office VBA and OpenOffice StarBasic if you use the same ActiveX object?

Exactly. There cannot be. So the problem must ly with OOBasic.

If you expect that it is related to the remote end (different answers on the same kind of GET request)

I don't. But that is the noticable differnce between a request that returns a responsetext and one that doesn't

It executes exactly one (that is only a single one) http request.

No. XmlHttp returns a clean 200 .status, and the .responsetext, of the final page. Not of the one I request, but of the redirect. Tried and tested. But under oobasic the responsetext is empty.

[karolus]

@Woutern
Nobody force you to use Basic.
Villeroy has given a hint

Karolus

[rudolfo]

Why should there be a difference in redirects between MS Office VBA and OpenOffice StarBasic if you use the same ActiveX object?

Exactly. There cannot be. So the problem must ly with OOBasic.

Well, with this kind of thinking you won't get a result from debugging. The core point in debugging is to open your mind and try to remove as much prejudice and assumptions as possible. Expect that anything can happen. If you make conclusions "the problem must ly with OOBasic" without verifying your assumption you will be stuck.

ActiveX modules don't really care from which language they are called. You have tried it in VBA and OoBasic. Give it another try with VBScript and run your script code with cscript.exe or wscript.exe (the Windows Scripting Host interpreters) or use python and Marc Hammond's excellent pywin32 package. Only a few modifications are needed to let the code run with the WSH scripting host and VBScript. Most of the examples in the net will be for WSH anyway.

It executes exactly one (that is only a single one) http request.

No. XmlHttp returns a clean 200 .status, and the .responsetext, of the final page. Not of the one I request, but of the redirect. Tried and tested. But under oobasic the responsetext is empty.

Most webservers reply with a short message (as HTML content) if they redirect the browser to a different URL. An empty page is mostly an indicator for a failure on the webserver. Maybe you have differences in encoding of parameters because the languages of MS Office and OpenOffice are different and the settings of OpenOffice are unknown to the webserver and the server silently fails?

[wouterm]

Have been trying it in VBS now. There the ActiveX module does what is is supposed to do. But here I have run into another problem, getting VLOOKUP to work in Calc from VBS.

[Villeroy]

Have been trying it in VBS now. There the ActiveX module does what is is supposed to do. But here I have run into another problem, getting VLOOKUP to work in Calc from VBS.

This is the second topic you've hijacked for the one and only reason that you are unable to program anything.

[rudolfo]

Have been trying it in VBS now. There the ActiveX module does what is is supposed to do. But here I have run into another problem, getting VLOOKUP to work in Calc from VBS.

If I run the test in VBScript I get the opposite result as you: google.com returns a failure (probably because it tries to attempt a redirect to https. yellow-box is a server in my internal network and the other one www.bol.com is surely something you are more aware off than me.

Code: Select all   Expand viewCollapse view

sUrl = "http://www.google.com/"

Set http = CreateObject("Microsoft.XmlHttp")

WScript.Echo getStatusOfUrl( http, sUrl)
WScript.Echo getStatusOfUrl( http, "http://yellow-box/")
WScript.Echo getStatusOfUrl( http, "http://www.bol.com/")

Function getStatusOfUrl( http, sUrl)

   On Error Resume Next
   'Load Webpage where address is URL
   http.open "GET", sUrl, FALSE
   http.send ""

   If Err.Number <> 0 Then
       WScript.Echo Err.Source & " (" & CStr(Err.Number) & ") " & Err.Description & ": Failure to retrieve " & sUrl
       Err.Clear
       getStatusOfUrl = -1
   Else
       getStatusOfUrl = http.status
   End If

   'Assign webpage contents as a string to variable called Webpage
   sWebPage = http.responseText

End Function


This are the results:

Code: Select all   Expand viewCollapse view

msxml3.dll (-2147024891) Zugriff verweigert: Failure to retrieve http://www.google.com/
-1
200
200


It seems like the http Response Code 302 (redirect) is converted into an exception by the ActiveX object. Hm, this is a really individualistic interpretation of a 3xx http status code. Surely not what the W3C consortium has intented with this status codes. Due to this strange interpretation of the http codes by the ActiveX/COM object it is essential to work with a language that supports exceptions by concept (Javascript, Python, Java) and not by work-around (Visual Basic, OOo Basic).

The Err.Number test in my above code contradicts the concept of Exceptions as it reads again some kind of return code from a function or method (although stored in a global variable/object).
The crucial idea with exception is: If something is really exceptional, don't massage it until it fits into a return code variable, but "return" it through a completely different channel: throw an exception (object). It is an "exception" (something I haven't planned for), so why should it match with my concept/structure of return values!?

Conclusion: Don't use Basic if you have to deal with network I/O. If you use Python you will have enough tracing features to look under the hood of the network communications. Basic just leaves your alone here: Right, "Zugriff verweigert" is German, but even in a english OS version a "Access denied" wouldn't be helpful at that stage. Of course with some masochistic tendencies you might stick to Basic, but in that case be prepared that a network sniffer (tcpdump or wireshark) has to do the dirty work for you to see what you would have seen directly with the help of some tracing options directly in Python.

[wouterm]

Conclusion: Don't use Basic if you have to deal with network I/O.

Unfortunately I am forced to work with an excel spreadsheet, so a macro seemed the most logical step. Writing it in vba was a piece of cake, the trouble started when I tried to port this macro to oobasic.
In Vbscript it works fine too.

So if the same code runs fine in excel, runs fine in vbs, I am afraid it must be some bug in OO.

[rudolfo]

Unfortunately I am forced to work with an excel spreadsheet ...

If you are forced to work with Excel, I don't understand why you bother this forum with ActiveX questions?
And I have my doubts that your judgement about OpenOffice and Bugs is based on solid logic. As I have mentioned before, I see the same kind of issues and errors if I am working in Visual Basic Script completely without OpenOffice.
There are several reasons why an ActiveX object is not working as it is supposed to do. You state in your signature that you are working with OpenOffice 4.1 and Windows 7, but you leave out the important point if your OS is 64bit or if it is a 32 bit OS. OpenOffice is a 32bit application, so it will always run in the 32bit subsystem. But VisualBasic Script can run as 64bit or as 32bit process. Mine was running as a 32bit process and showed errors. So maybe the problem is not OpenOffice, but the 32bit Microsoft http.xml ActiveX object. Of course this error will only show in a 32 bit environment. VBScript and MS Office might just be not aware of this problem in the 32bit version because they use the 64bit version.

But the simple fact that the error appears only in OpenOffice doesn't mean that it is an error of OpenOffice. Fortunately OpenOffice has different (and advanced) macro languages like Python. If the failures are happening also with the Python language we could start to state that is is a problem of OpenOffice. Python is worth any hour that you invest in it. It is useful anywhere, not only inf the context of Openoffice.

[wouterm]

If you are forced to work with Excel, I don't understand why you bother this forum with ActiveX questions?

Because I have to read a webpage to put data in the excelsheet.

OpenOffice has different (and advanced) macro languages

I do not need an advanced macro language, what I do is very simple. Read a webpage, process the content, put the result in a spreadsheet.

If I use CreateObject("InternetExplorer.Application") to read the page I get exactly the same (32 bit) object in OO and vbs. Once again, in OO I can not read the conent of the webpage. Just run this script in OO and vbs.

Code: Select all   Expand viewCollapse view

sub testie()
    dim URL
    dim webbrowser
    dim htmlstring
   URL = "http://www.google.com"
   Set Webbrowser = CreateObject("InternetExplorer.Application")
   Webbrowser.Visible = True
   Webbrowser.navigate2 URL, 2 + 4 + 8 + 16384, "_self"
   Do While Webbrowser.busy
          ''whatever
   Loop
   htmlstring = Webbrowser.Document.body.innerHTML

   MsgBox (htmlstring)
   
   URL = "http://www.bol.com"
   Webbrowser.navigate2 URL, 2 + 4 + 8 + 16384, "_self"
   Do While Webbrowser.busy
          ''whatever
   Loop
   htmlstring = Webbrowser.Document.body.innerHTML

   MsgBox (htmlstring)
end sub


[karolus]

I do not need an advanced macro language, what I do is very simple. Read a webpage, process the content, put the result in a spreadsheet.

I'm pretty sure - with advanced language the whole thing would be easier to solve - but you will stuck with crumpy VBS

Karolus

[rudolfo]

You are right, what you plan to do is easy to describe: read a page, process, save it in a spreadsheet.
But any programmer that has worked with network communication will tell you that "read a webpage" is more difficult then processing an algorithm to calculate fibonacci numbers.
Reading a web page, includes the steps to open a socket connection to the webserver (optionally not a direct connection but one through a proxy), send some request data to the webserver and wait for it's response. This part can get complicated: The content on the web page might be serveral kBytes, so it has to travel over the network in several packets. Because the CPU has better things to do than waiting on network packets it switches to other processes and has to switch back when there is an indication that data has arrived. This requires events, slots and/or asynchronous I/O reading to get this done. Some parts of this can be encapsulated by the ActiveX object and won't reach the end user. But this removes the control over the process from the user (at least partly).

Instead of plumbing some external part (XmlHttp object) into a limited language (VB(S), Basic) it is better to use a language that has native spport for Network I/O and asynchronous file reading (Python)