CODEWITHSUNDEEP
vbaconnection
MansNotHot
MansNotHot

Reputation: 293

Why do i get "Argument is not optional" error on simple url call?

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.

Upvotes: 0

Views: 87

Answers (1)

Vityata
Vityata

Reputation: 43585

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")

Upvotes: 2

Related Questions