- Microsoft Access
- VBA
- REST
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
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
<<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:
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
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.
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?
´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?
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.
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.
<<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.
That was an example, not the actual code you will need.
Jim.
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?
It is progressing. Now I am getting on the instruction " objRequest.Open "GET", strPostURL, False" a type mismatch err
any ideas?
sure!
This is ther actual function invoked from a button
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
This is ther actual function invoked from a button
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.png]()
And 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
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.
And 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.
»bp
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.
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?
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?
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
»bp
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial