0

I'm getting the above error when trying to get random numbers from random.org. The error is occurring on the line "Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.4.0")". I don't have a lot of experience with connecting to websites from vba so I'm not sure what I am doing wrong.

Sub pick_random()
Dim x As Long
Dim randomInt As Integer

x = Worksheets(ActiveSheet.Name).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
randomInt = GetRndNos(1, 1, x)
randomStudentFirst = ThisWorkbook.Sheets(ActiveSheet.Name).Cells(randomInt, 1).Value
randomStudentLast = ThisWorkbook.Sheets(ActiveSheet.Name).Cells(randomInt, 2).Value
randomStudent = randomStudentFirst + " " + randomStudentLast

MsgBox "Selected: " & randomStudent
End Sub


Function GetRndNos(NUM As Long, MIN As Long, MAX As Long) As Variant
Dim objXMLHTTP As Object
Dim strURL As String
Dim strResp
Dim I As Long
strURL = "http://www.random.org/integers/"
strURL = strURL & "?num=" & NUM & "&min=" & MIN & "&max=" & MAX & "&col=1&base=10&format=plain&rnd=plain"
Debug.Print strURL
Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.4.0")

With objXMLHTTP
    .Open "GET", strURL, False
    .send

    strResp = .responseText

    GetRndNos = Split(strResp, Chr(10))

End With

Set objXMLHTTP = Nothing
End Function

Any help is appreciated, thank you!

grademacher
  • 67
  • 1
  • 3
  • 9
  • 2
    Why not just instantiate `MSXML2.ServerXMLHTTP`? – omegastripes Feb 05 '18 at 17:28
  • In this blog post there is code to read from registry the current version for a COM class. http://exceldevelopmentplatform.blogspot.com/2018/02/vba-progid-what-is-current-version.html – S Meaden Feb 06 '18 at 01:09

0 Answers0