search

VBA-tools / VBA-Web

VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the web
http://vba-tools.github.io/VBA-Web/
MIT License
2.01k stars 494 forks source link

Strange "Object doesn't support this property or method" error #384

Closed dcolley closed 5 years ago

dcolley commented 5 years ago

I'm on:

I had to edit the WebHelpers file to point to absolute path - I found this hint here 

Private Declare PtrSafe Function web_popen Lib "/usr/lib/libc.dylib" Alias "popen" (ByVal web_Command As String, ByVal web_Mode As String) As LongPtr
Private Declare PtrSafe Function web_pclose Lib "/usr/lib/libc.dylib" Alias "pclose" (ByVal web_File As LongPtr) As LongPtr
Private Declare PtrSafe Function web_fread Lib "/usr/lib/libc.dylib" Alias "fread" (ByVal web_OutStr As String, ByVal web_Size As LongPtr, ByVal web_Items As LongPtr, ByVal web_Stream As LongPtr) As LongPtr
Private Declare PtrSafe Function web_feof Lib "/usr/lib/libc.dylib" Alias "feof" (ByVal web_File As LongPtr) As LongPtr

I created my own implementation of I

Implements IWebAuthenticator
Option Explicit

' --------------------------------------------- '
' Constants and Private Variables
' --------------------------------------------- '

Private Const web_HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
Private Const web_HTTPREQUEST_SETCREDENTIALS_FOR_PROXY = 1

' --------------------------------------------- '
' Properties
' --------------------------------------------- '

Public username As String
Public password As String

' ============================================= '
' Public Methods
' ============================================= '

''
' Setup
'
' @param {String} Username
' @param {String} Password
''
Public Sub Setup(username As String, password As String)
    Me.username = username
    Me.password = password
End Sub

Private Sub Class_Initialize()

End Sub

Private Sub Class_Terminate()
    Debug.Print "[DEBUG] HttpBasicAuthenticator.Class_Terminate()"
    'Me.username = Nothing
    On Error GoTo errorHandler
    'Me = Nothing
    'Unload Me
    Exit Sub
errorHandler:
    MsgBox Err.Number + ", " + Err.Description

End Sub

''
' Hook for taking action before a request is executed
'
' @param {WebClient} Client The client that is about to execute the request
' @param in|out {WebRequest} Request The request about to be executed
''
Private Sub IWebAuthenticator_BeforeExecute(ByVal Client As WebClient, ByRef Request As WebRequest)
    Request.SetHeader "Authorization", "Basic " & WebHelpers.Base64Encode(Me.username & ":" & Me.password)
End Sub

''
' Hook for taking action after request has been executed
'
' @param {WebClient} Client The client that executed request
' @param {WebRequest} Request The request that was just executed
' @param in|out {WebResponse} Response to request
''
Private Sub IWebAuthenticator_AfterExecute(ByVal Client As WebClient, ByVal Request As WebRequest, ByRef Response As WebResponse)
    ' e.g. Handle 401 Unauthorized or other issues
End Sub

''
' Hook for updating http before send
'
' @param {WebClient} Client
' @param {WebRequest} Request
' @param in|out {WinHttpRequest} Http
''
Private Sub IWebAuthenticator_PrepareHttp(ByVal Client As WebClient, ByVal Request As WebRequest, ByRef Http As Object)
    Http.SetCredentials Me.username, Me.password, web_HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
End Sub

''
' Hook for updating cURL before send
'
' @param {WebClient} Client
' @param {WebRequest} Request
' @param in|out {String} Curl
''
Private Sub IWebAuthenticator_PrepareCurl(ByVal Client As WebClient, ByVal Request As WebRequest, ByRef Curl As String)
    ' e.g. Add flags to cURL
    Curl = Curl & " --basic --user " & WebHelpers.PrepareTextForShell(Me.username) & ":" & WebHelpers.PrepareTextForShell(Me.password)
End Sub

And I added debug statements to all the standard Functions/Subs

Here is my test code:

Function GetJiraField(issueRef As String, fieldRef As String) As String

    Dim myAuth As New MyHttpBasicAuthenticator
    Dim cl As New WebClient
    Dim req As New WebRequest
    Dim res As New WebResponse
    Dim username As String, password As String

    username = "My.User@my-company.com"
    password = "Secret01"

    cl.BaseUrl = "https://mycompany.atlassian.net"
    Dim resource As String

    myAuth.Setup username, password
    Set cl.Authenticator = myAuth

    req.resource = "/rest/api/3/field"
    req.Method = WebMethod.HttpGet
    'req.Format = WebFormat.Json
    'req.SetHeader "Accept", "application/json"
    req.SetHeader "Content-Type", "application/json"

    'WebHelpers.EnableLogging = True

    res = cl.Execute(req)

    GetJiraField = "not implemented"

End Function

Sub RunTest()

    Dim res As String
    res = GetJiraField("", "")
    Debug.Print res

End Sub

During debug, the response contains the values I expect, BUT I get a strange error:

Object doesn't support this property or method

Here is the output from the debug screen:

[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] ExtractHeaders()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] Class_Initialize()
[DEBUG] ExtractCookies()

[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] WebClient.Class_Terminate()
[DEBUG] WebRequest.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] Dictionary.Class_Terminate()
[DEBUG] WebResponse.Class_Terminate()
[DEBUG] HttpBasicAuthenticator.Class_Terminate()

I edited this Sub to read:

Private Sub Class_Terminate()
    Debug.Print "[DEBUG] HttpBasicAuthenticator.Class_Terminate()"
    'Me.username = Nothing
    On Error GoTo errorHandler
    'Me = Nothing
    'Unload Me
    Exit Sub   '<==== THIS IS WHERE THE ERROR OCCURS
errorHandler:
    MsgBox Err.Number + ", " + Err.Description

End Sub

There is no option to debug when I get the message.

If you got this far, then kudos to you! Any help would be appreciated...

zgrose commented 5 years ago

In GetJiraField try Set res = cl.Execute(req) instead of res = cl.Execute(req) because the return type is a WebResponse object.

dcolley commented 5 years ago

@zgrose oh, such a rookie mistake... Thanks! [hangs head in shame]