We help IT Professionals succeed at work.

web browser for rest interface

Hi,

I am using ms/access and need to invoke a REST interface. I have designed a form and, within, I  did put a web browser control and tested its source with a test string. It looks perfect.

Now I need to automate the function which means that at a click of a button I need to construct a string and this string will be used as the control source of the browser. How do I do it?

thanks in advance
Comment
Watch Question

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
<<Now I need to automate the function which means that at a click of a button I need to construct a string and this string will be used as the control source of the browser. How do I do it? >>

 You don't<g>

 Using a web browser control is not required and actually a bad approach.   You call a web service directly in VBA code.   Does the web site your using use JSON either in getting requests or in response?   If so, you'll probably want to use this:

https://github.com/VBA-tools/VBA-Web 

Which is a complete code library for talking to web sites.   If no JSON, then you can take a simpler approach like this:

Public Function GetGeoCodeForAddressFromMapQuest(strAddress As String, ByRef strLat As String, ByRef strLon As String) As Boolean

    Const RoutineName = "GeoCodeAddressFromMapQuest"
    Const Version = "1.0.0.0"

    Dim strPostURL As String
    Dim arrResponse
    Dim strGranularity As String
    Dim strStreetConf As String
    Dim strPostalConf As String

    Dim strTransactionKey As String

    On Error GoTo Error_Handler

    ' Default to false in case anything goes wrong.
    GetGeoCodeForAddressFromMapQuest = False
    
    If CheckForInternetConnection() = True Then
        strTransactionKey = GetAppConfigValue("xxx", "MapQuestAPITransactionKey", GetCurrentCompany(), ".")

        ' Set the correct URL
        strPostURL = "http://www.mapquestapi.com/geocoding/v1/address?key=" & strTransactionKey & "&inFormat=kvp&outFormat=csv&delimiter=|&location=" & strAddress

        ' We use xmlHTTP to submit the input values and record the response
        Dim objRequest As New MSXML2.XMLHTTP
        objRequest.Open "GET", strPostURL, False
        objRequest.Send

        ' The response string is broken into an array using the specified delimiting character
        arrResponse = Split(objRequest.responseText, "|", -1)

        ' Check the qualitity code returned.
        strGranularity = Left$(arrResponse(26), 1)
        strStreetConf = Mid$(arrResponse(26), 3, 1)
        strPostalConf = Mid$(arrResponse(26), 5, 1)
        
        If InStr(1, "LIBP", strGranularity) > 0 And InStr(1, "AB", strStreetConf) > 0 And InStr(1, "AB", strPostalConf) > 0 Then
            strLat = arrResponse(21)
            strLon = arrResponse(22)
            GetGeoCodeForAddressFromMapQuest = True
        End If
    End If

Exit_Handler:
    On Error Resume Next

    Set objRequest = Nothing

    Exit Function

Error_Handler:
    UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
    Resume Exit_Handler

End Function
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Dang new editor....could not add more text.

The above example shows an example of talking to Mapquest to get a geocode for an address.   You set a reference to the XLM lib, and it has the functions you can call to work with a REST interface.

Jim.
Hi Jim!

´Thanks a lot for the reply.

The site is the nexmo site and I am using the line:

=https://rest.nexmo.com/sms/json?api_key=WWWW&api_secret=XXXX&from=YYYY&to=+XXXX&text=blablablabla"
   

(just replace the keys for confidentiality

How to I know about jason?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Looking at the developer docs:

https://developer.nexmo.com/concepts/overview 

 you'll probably want to use the lib on Github.   But I have never worked with a JWT (Jason Web Token), which needs to be used for authentication and I don't think the lib handles that.

Jim. 
Ok

I'll try first your direct approach. It it works, then I'll be happy as a clam
'got a 1st problem:

Dim objRequest As New MSXML2.XMLHTTP

Screenshot-2020-03-04-at-16.20.50.png
can you help?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
You'll need to include a reference, as shown below.
sshot-163.png
»bp
mmmh.. not yet

'got the same error

Screenshot-2020-03-04-at-16.37.00.png


I have no V4 but have a v3

shall I also adfd that one?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<I'll try first your direct approach. It it works, then I'll be happy as a clam >>

That was an example, not the actual code you will need.

Jim.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
You don't want version 6, use version 3 then.


»bp
Ok. I am on V3 and cleaned the example code.

It is progressing. Now I am getting  on the instruction " objRequest.Open "GET", strPostURL, False" a type mismatch err

Screenshot-2020-03-04-at-18.02.50.png
any ideas?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Can you post the full code you are testing please.


»bp
sure!
Option Compare Database

Public Function sendSMS(strTarget As String, strText As String) As Boolean

    Const RoutineName = "sendSMS"
    

    Dim strPostURL As String
    strPostURL = "=https://rest.nexmo.com/sms/json?api_key=ttt&api_secret=uuu&from=vvvto=xxx&text=estamos fritos e cozidos e comidos"

    Dim arrResponse

    On Error GoTo Error_Handler

    ' Default to false in case anything goes wrong.
    sendSMS = False
    ' We use xmlHTTP to submit the input values and record the response
    Dim objRequest As New MSXML2.XMLHTTP
    objRequest.Open "GET", strPostURL, False
    objRequest.Send
    ' The response string is broken into an array using the specified delimiting character
    arrResponse = Split(objRequest.responseText, "|", -1)
    sendSMS = True

Exit_Handler:
    On Error Resume Next
    Set objRequest = Nothing
    Exit Function

Error_Handler:
      MsgBox Err.Description, _
        "ERROR " & Err.Number

    Resume Exit_Handler

End Function

Open in new window



This is ther actual function invoked from a button
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
The error is actually occuring on the Send, not the Open.

It looks like you have a stray equal sign in:

strPostURL = "=https://rest.nexmo.com/sms/json?api_key=ttt&api_secret=uuu&from=vvvto=xxx&text=estamos fritos e cozidos e comidos"

removing that removed the error for me.

When I am debugging things like this I typically drop back to the default error handling of VBA, so that I see as much info as possible right at the spot it occurs.  So I commented out this line:

On Error GoTo Error_Handler

for testing and when I got the error it was a bit more obvious the exact line that was executing, even without breakpoints.  SO when I ran the code with the error in the URL string, it popped up this message box.
sshot-165.pngAnd then when I clicked Debug it took me right to the line of code and stopped there as if it was a breakpoint.  This give us the opportunity to inspect values, etc.
sshot-166.png
»bp
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
and FYI, you need a POST operation, not a GET (I'm assuming your are trying to send):

https://developer.nexmo.com/messaging/sms/overview 

Jim.
Yes, definitely.

I am willing to send a message and get a result to be stored in a text field in the DB. I just pasted the sample code you have provided in the beginning but without knowing anything about it. Cleaned a bit for by specific case. But I do not know anything about the POST and GET. Can you please point me to a doc on it?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
It's not perfect but this seems like one of the better overviews I could find, I'm sure you will know more after reading than before.  Hope it helps.



»bp
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Also, to Jim's point, are you trying to fetch information with this interaction, or send information?

And since the API is secure, if there is JSON returned that needs to be processed, you will need to share a sample of that here if we are to get more specific about how to process it.  If it is very simple you may be able to handle it with just string manipulations, pulling out the needed parts.  But if it is a bit more complex in nature you will need a JSON library to assist with that, like VBA-tools/VBA-JSON: JSON conversion and parsing for VBA


»bp
Hi,
I am sorry, I had to stall.

Here is what I need:
1. send a message (as per the sample code)
2. store the result in a text field (NEXMOS sends back a simple text easy to parse

Nothing else...

kindest regards
joao
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Okay, so I think you should have enough to do that at this point.  If not post specific errors / questions.


»bp
Ok. I agree. Im am closing que question with a big thanks!
Thanks for the help!