Compilation Error with small script

I'm trying to write a cls in Excel 2007, here's the code:

Dim JiraService As MSXML2.XMLHTTP60
Set JiraService = New MSXML2.XMLHTTP60

JiraService.Open "GET", "URL", False
JiraService.SetRequestHeader "Content-Type", "application/json"
JiraService.SetRequestHeader "Accept", "application/json"
JiraService.Send

sRestResponse = JiraService.ResponseText

MsgBox sRestResponse

I'm getting a compilation error, indicating that Set appears to be invalid.

It basically says that the external procedure is not valid.

Can anyone help me out?

Answers


You are missing the appropriate reference I guess. Click on Tools~~>References in VBA

Add a project references to;

  • Microsoft WinHTTP Services, version 5.1
  • Microsoft XML, v6.0

Also in this line

JiraService.Open "GET", "URL", False

If URL is the variable which holds the actual URL then "URL" will be considered as a string and not a variable since it is between quotes. I guess you are trying this?

JiraService.Open "GET", URL, False

FOLLOWUP (From Comments)

The problem is that you have pasted the above code in a class module. You are suppose to paste the code in a module and call it in a sub. See this example

Sub Sample()
    Dim JiraService As MSXML2.XMLHTTP60
    Set JiraService = New MSXML2.XMLHTTP60

    JiraService.Open "GET", URL, False
    JiraService.SetRequestHeader "Content-Type", "application/json"
    JiraService.SetRequestHeader "Accept", "application/json"
    JiraService.Send

    sRestResponse = JiraService.ResponseText

    MsgBox sRestResponse
End Sub

Screenshot


Need Your Help

git impossible to push after add

git ubuntu github

Ok, I've a trouble that I'm not able to fix since some week.