I am doing a simple xml webservice macro call to determine the users security clearance....which returns a GetRestWebServiceValue=true or GetRestWebServiceValue=false response. This call is being done at the thisworkbook level using a public sub workbook_open() macro. I can step through the macro and see all works as it should, but the true or false is not getting passed to the standard module Public GetRestWebServiceValue As String.
As soon as the workbook_open() hits a exit sub or end sub the value is lost.
Any suggestions would be greatly appreciated.
code in a standard module
As soon as the workbook_open() hits a exit sub or end sub the value is lost.
Any suggestions would be greatly appreciated.
Code:
Public Sub Workbook_Open()
Dim url As String
User = Environ("username")
url = "http://ws.yahoo.com/ws/secured/authorization/" & User
On Error GoTo Err_SuperTrap
GetRestWebServiceValue = ""
Dim xmlhttp
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
Call xmlhttp.Open("GET", url, False)
Call xmlhttp.send
Dim xmldoc As DOMDocument
Set xmldoc = CreateObject("Microsoft.XMLDOM")
xmldoc.async = False
xmldoc.LoadXML (xmlhttp.responseText)
GetRestWebServiceValue = xmldoc.Text
MsgBox GetRestWebServiceValue
Exit_SuperTrap:
Exit Sub
Err_SuperTrap:
If Err.Number <> -2146697211 Then
MsgBox Err.Description
End If
GetRestWebServiceValue = "Intranet Server Down"
End Sub
code in a standard module
Code:
Public GetRestWebServiceValue As String
Dim adoCN As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim ReturnField As String
Dim strSQL As String