0

My goal is to check the internet connecton in VBA but when i try to call a url with request.open I get the error message "Argument is not optional".

Sadly internet research has no yielded any results.

This is my code: 

On Error GoTo NoConnectionErrorHandling
    Dim Request As MSXML2.XMLHTTP60
    Request.Open "http://www.google.com"
    Request.send
    MsgBox Request.Status

It hangs itself in the third line of the shown code. I hope someone can help me as i have very very little experience in VBA yet.

MansNotHot
  • 263
  • 1
  • 3
  • 19

1 Answers1

2

You have to specify the type of the request. It can be "GET", "POST" or something else.

See here for the open Method: https://msdn.microsoft.com/en-us/library/ms757849(v=vs.85).aspx

These are the parameters:

  • bstrMethod The HTTP method used to open the connection, such as GET, POST, PUT, or PROPFIND. For XMLHTTP, this parameter is not case-sensitive. The verbs TRACE and TRACK are not allowed when IXMLHTTPRequest is hosted in the browser. What is the difference between POST and GET?

  • bstrUrl The requested URL. This can be either an absolute URL, such as "http://Myserver/Mypath/Myfile.asp", or a relative URL, such as "../MyPath/MyFile.asp".

  • varAsync[optional]

  • bstrUser[optional]

  • bstrPassword[optional]

As you see, the method needs at least two parameters (the other 3 are optional) thus 1 is not enough.

You need to declare Request with the keyword New. Thus, something like the following piece works:

Public Sub TestMe()    
    Dim Request As New MSXML2.XMLHTTP60
    Request.Open "GET", "http://www.bbc.com"
    Request.send
    MsgBox Request.Status    
End Sub

Whenever you are using libraries outside the standard VBA libraries it is a good idea to do one of the following 2:

  • Provide information for the library:

enter image description here

  • Use late binding:
Dim Request As Object
Set Request = CreateObject("Msxml2.ServerXMLHTTP.6.0")
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • I have tried this and i dont get an error message anymore, but strangely it fails and goes into the ErrorHandler altough there is a perfect internet connection – MansNotHot Feb 05 '18 at 10:30
  • 1
    @MansNotHot - see the edit. `Google` is a site, that does not allow being cralwed by anybody that easily. `BBC.COM` has no such resctriction. Furthermore, you should declare `Request` with the keyword `New`. – Vityata Feb 05 '18 at 10:35