asked on Use MS Access VBA to create http post request
I am trying to send http requests to the API provided by HelloSign.com........To test what I was doing, I used Git BASH, and the following command completed correctly (I have changed the key and some of the user / document information, but the syntax is exactly as I sent it):
curl -u "9Ca9C0972a4e1ae439Cee8e5d
I opened up a VBA module, and set a reference to the Microsoft XML 6.0 library. Then I tried the following:
Public Function TestAPI()
Dim StrAPI As String
Dim request As MSXML2.ServerXMLHTTP60
StrAPI = " -u '9Ca9C0972a4e1ae439Cee8e5d
Set request = New ServerXMLHTTP60
request.Open "Post", StrAPI, False
request.send
Debug.Print request.StatusText
End Function
This code created an error at : request.Open "Post", StrAPI, False
The error was:
runtime error '-2147012890 (80072ee6)
System error: -2147012890
The only change I made to the string that worked in Curl was to change the double quotes to single quotes inside the string.
Thanks in advance as this is a stumbling block in a new process we are trying to develop.
curl -u "9Ca9C0972a4e1ae439Cee8e5d
I opened up a VBA module, and set a reference to the Microsoft XML 6.0 library. Then I tried the following:
Public Function TestAPI()
Dim StrAPI As String
Dim request As MSXML2.ServerXMLHTTP60
StrAPI = " -u '9Ca9C0972a4e1ae439Cee8e5d
Set request = New ServerXMLHTTP60
request.Open "Post", StrAPI, False
request.send
Debug.Print request.StatusText
End Function
This code created an error at : request.Open "Post", StrAPI, False
The error was:
runtime error '-2147012890 (80072ee6)
System error: -2147012890
The only change I made to the string that worked in Curl was to change the double quotes to single quotes inside the string.
Thanks in advance as this is a stumbling block in a new process we are trying to develop.
Microsoft AccessXMLVBAGit
Last Comment
Rick Rudolph
You can't POST a URL containing spaces and Curl syntax:
curl.1 the man page
So, resolve what the Curl command examples do, and then adjust the URL and parameters accordingly (if possible).
curl.1 the man page
So, resolve what the Curl command examples do, and then adjust the URL and parameters accordingly (if possible).
ASKER
I ran the code suggested by John, and receive the following message in the Message Box:
{"error":{"error_msg":"Inv
I did a debug.print of strAPI, and this is the result:
https://9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a:@api.hellosign.com/v3/signature_request/send' -F 'title=rjr.docx' -F 'subject=Ricks Test Contract' -F 'message=Please sign this contract' -F 'signers[0][email_address]
I am obviously lost here,
I did change the strAPI line to eliminate the apostrophe in my name......so I changed Rick's to Rick
The url makes no sense to me with the https:// followed by the api key.....:@api......
The apostrophe after the word send is also confusing to me
{"error":{"error_msg":"Inv
I did a debug.print of strAPI, and this is the result:
https://9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a:@api.hellosign.com/v3/signature_request/send' -F 'title=rjr.docx' -F 'subject=Ricks Test Contract' -F 'message=Please sign this contract' -F 'signers[0][email_address]
I am obviously lost here,
I did change the strAPI line to eliminate the apostrophe in my name......so I changed Rick's to Rick
The url makes no sense to me with the https:// followed by the api key.....:@api......
The apostrophe after the word send is also confusing to me
Do you follow Gustav's advice?
Cause the -F parameter means that you need to post form variables as it seems as multipart/form-data in opposite to application/x-www-form-url
To get a better insight what you need to do: Install Fiddler to visualize the entire http traffic from curl as well as your tries.
Cause the -F parameter means that you need to post form variables as it seems as multipart/form-data in opposite to application/x-www-form-url
To get a better insight what you need to do: Install Fiddler to visualize the entire http traffic from curl as well as your tries.
ASKER
I did not truly understand Gustav's advice, so the short answer is no.
I will install Fiddler and see what I can learn from that.
Clearly my biggest problem is a total lack of knowledge in what is going on behind the scenes. Is there any information from Fiddler that might give us information as to what is failing with the MS Access approach that John suggested?
Thank you,
Rick
I will install Fiddler and see what I can learn from that.
Clearly my biggest problem is a total lack of knowledge in what is going on behind the scenes. Is there any information from Fiddler that might give us information as to what is failing with the MS Access approach that John suggested?
Thank you,
Rick
The -u parameter means normally HTTP basic authentication. This is done by setting the appropriate header
The -F parameters means that you need to add the data as parameter for the Send call. And according to the curl description it is a multipart schema instead of url encoding.
But you may test url encoding as:
objHttp.SetRequestHeader "Authorization", "Basic " + Base64Encode(authUser + ":" + authPass)
The -F parameters means that you need to add the data as parameter for the Send call. And according to the curl description it is a multipart schema instead of url encoding.
But you may test url encoding as:
Option Compare Database
Option Explicit
Public Function Test()
Const URL As String = "https://api.hellosign.com/v3/signature_request/send"
Dim WebClient As MSXML2.XMLHTTP60
Dim Password As String
Dim UrlEncodedData As String
Dim Username As String
Password = "..."
UrlEncodedData = UrlEncode("title=rjr.docx&subject=Rick's Test Document&message=Please sign this contract&signers[0][email_address]=rrudolph@test.com&signers[0][name]=Rick Rudolph&file[0]=@c:/docpath/rjr.docx&test_mode=1")
Username = "9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a"
Set WebClient = New MSXML2.XMLHTTP60
WebClient.Open "POST", URL, False, "", ""
WebClient.SetRequestHeader "Content-Type", "application/x-www-form-UrlEncoded"
WebClient.SetRequestHeader "Authorization", "Basic " + EncodeBase64(Username + ":" + Password)
WebClient.Send UrlEncodedData
Debug.Print WebClient.Status, WebClient.StatusText
Set WebClient = Nothing
End Function
Public Function EncodeBase64(ByVal CData As String) As String
Dim Document As MSXML2.DOMDocument
Dim Element As MSXML2.IXMLDOMElement
Dim Data() As Byte
Set Document = New MSXML2.DOMDocument
Set Element = Document.createElement("b64")
Data = StrConv(CData, vbFromUnicode)
Element.dataType = "bin.base64"
Element.nodeTypedValue = Data
EncodeBase64 = Element.Text
Set Element = Nothing
Set Document = Nothing
End Function
Public Function UrlEncode( _
ByVal StringVal As String, _
Optional SpaceAsPlus As Boolean = False _
) As String
Dim bytes() As Byte, b As Byte, i As Integer, space As String
If SpaceAsPlus Then space = "+" Else space = "%20"
If Len(StringVal) > 0 Then
With New ADODB.Stream
.Mode = adModeReadWrite
.Type = adTypeText
.Charset = "UTF-8"
.Open
.WriteText StringVal
.Position = 0
.Type = adTypeBinary
.Position = 3 ' skip BOM
bytes = .Read
End With
ReDim result(UBound(bytes)) As String
For i = UBound(bytes) To 0 Step -1
b = bytes(i)
Select Case b
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Chr(b)
Case 32
result(i) = space
Case 0 To 15
result(i) = "%0" & Hex(b)
Case Else
result(i) = "%" & Hex(b)
End Select
Next i
UrlEncode = Join(result, "")
End If
End Function
ASKER
ste5an..........I think we are making progress. I did make some very minor changes to your encodebase64 function. i changed the msxml2.domdocument to msmxl2.domdocument60
Ran the code, and received a 400 bad request message in debug.print.
The code, did bring up a box labeled Windows Security
MSAcess.exe
The server api.hellosign.com is asking for our user name and password
I pasted the key into the user name, left the password blank, and I believe that may have generated the bad request
subsequent running of the code do not bring up the box, but still have the 400 Bad Request message
Ran the code, and received a 400 bad request message in debug.print.
The code, did bring up a box labeled Windows Security
MSAcess.exe
The server api.hellosign.com is asking for our user name and password
I pasted the key into the user name, left the password blank, and I believe that may have generated the bad request
subsequent running of the code do not bring up the box, but still have the 400 Bad Request message
ASKER
I omitted that their was a message in the security box "That server also reports. "API".
I think I need to delete the windows security username / password, but don't know where it is stored. I don't see it in the credentials manager.
API_Security_Box.JPG
I think I need to delete the windows security username / password, but don't know where it is stored. I don't see it in the credentials manager.
API_Security_Box.JPG
It's necessary that you use Fiddler in parallel to compare what curl has sent and what you send. This makes getting things correct much simpler.
Did you use the correct credentials in my sample?
Did you use the correct credentials in my sample?
ASKER
Yes, I changed the credentials back to the correct ones. Did you happen to look at the jpg i posted?
That dialog should only pop open, when there is either no credentials in the code for basic authentication. Test the authentication alone:
Public Function Test()
Const URL As String = "https://api.hellosign.com/v3/signature_request/send"
Dim WebClient As MSXML2.XMLHTTP60
Dim Password As String
Dim Username As String
Password = "..."
Username = "9Ca9C0972a4e1ae439Cee8e5d54CCa881031ba247e085807a847aa86Ca080d6a"
Set WebClient = New MSXML2.XMLHTTP60
WebClient.Open "POST", URL, False, "", ""
WebClient.SetRequestHeader "Authorization", "Basic " + EncodeBase64(Username + ":" + Password)
WebClient.SetRequestHeader "Content-Type", "application/x-www-form-UrlEncoded"
WebClient.Send
Debug.Print WebClient.Status, WebClient.StatusText
Set WebClient = Nothing
End Function
ASKER
This brings up the Windows Security box.........also, do not understand "Not that you accidentally have still a colon in there"
the only colon I see is the colon in your code. if you mean did I accidentally include the colon in my paste, the answer is no.
However, this did give some interesting results. I received a message 402 Payment Required.............this tells me that we are being successful in communicating with the api.
In the original string, the very end of the string........test_mode=1 turns the request into a free request. Now when I authenticate only, it wants me to pay and I don't have a pay account. So the rest of my problem is now in the string, not the authentication. I will look closer at that and see if I see any clues.
the only colon I see is the colon in your code. if you mean did I accidentally include the colon in my paste, the answer is no.
However, this did give some interesting results. I received a message 402 Payment Required.............this tells me that we are being successful in communicating with the api.
In the original string, the very end of the string........test_mode=1 turns the request into a free request. Now when I authenticate only, it wants me to pay and I don't have a pay account. So the rest of my problem is now in the string, not the authentication. I will look closer at that and see if I see any clues.
FYI, besides fiddler, I've found Postman to be a very nice tool to debug with when trying to wade through this stuff.
Jim.
Jim.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Open in new window