SOAP Calls via VBA

[Felix18807]

I am trying to make a SOAP Call to the reallysimplesystems API. I am unfamiliar with this process but I have spent time searching the internet and looking for ways to do this on various forums. The general view seems to be that the Microsoft SOAP toolkit is outdated and no longer supported (I'm using MSAccess 2010).

If you are familiar with this process could you have a look at my code below and see if I have made any silly mistakes?

Thanks In Advance

Dim URL As String
Dim envelope As String
Dim strXML As String

URL = "

https://webtrans.reallysimplesystems.com/api/api.v1.asmx"

strXML = "<?xml version=""1.0"" encoding=""utf-8""?>" & _
"<soap:Envelope xmlns:xsi=""

http://www.w3.org/2001/XMLSchema-instance""

xmlns:xsd=""

http://www.w3.org/2001/XMLSchema""

xmlns:soap=""

http://schemas.xmlsoap.org/soap/envelope/"">"

& _
"<soap:Body>" & _
"<GetAccountById xmlns=""ReallySimple"">" & _
"<CustomerId>XXXXX</CustomerId>" & _
"<APIPassword>XXXXX</APIPassword>" & _
"<AccountId>470612</AccountId>" & _
"</GetAccountById>" & _
"</GetAccounts>" & _
"</soap:Body>" & _
"</soap:Envelope>"

envelope = strXML

http.setTimeouts 30000, 30000, 30000, 30000

Call http.Open("POST", URL)

http.setRequestHeader "Host", "webtrans.reallysimplesystems.com"
http.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
' http.setRequestHeader "Content-Length", Len(strXML)
http.setRequestHeader "SOAPAction", "ReallySimple/GetAccountById"

http.Send envelope

txtXML.value = Replace(Replace(Replace(http.responseText, "<", vbCrLf), ">", ""), "/", "")

 

[kjv1611]

So, what errors are you receiving? What is it doing or not doing? Have you tried stepping through the code to see whether it's doing what you want or not?

We need more information about your situation, not just "Here's my code, how do I fix it?"

I've not messed with SOAP, per se, but even if I practically lived on working with the stuff, I'd not be able to help without know what I'm helping to fix.

 

[Felix18807]

Ok when I run this code I get the reponse status 400 "Bad Request"

The following is a sample SOAP 1.1 request and response from the website.

POST /api/api.v2.asmx HTTP/1.1
Host: webtrans.reallysimplesystems.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "ReallySimple/GetAccounts"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:soap="

http://schemas.xmlsoap.org/soap/envelope/">

<soap:Body>
<GetAccounts xmlns="ReallySimple">
<CustomerId>long</CustomerId>
<APIPassword>string</APIPassword>
<SearchField>string</SearchField>
<SearchValue>string</SearchValue>
</GetAccounts>
</soap:Body>
</soap:Envelope>

Sorry for my lack of specificity(?)

 

[Felix18807]

Right the following code works and I am able to save the XMLDOC without issue.

'''''''''''''''''''''''''''''''''''''''''''''''''''''

If IsNull(txtFieldname) Or IsNull(txtValueName) Then
MsgBox "Enter Data Into Field/Value Box"
Exit Sub
End If

txtXML.value = ""
txtStatus.value = ""

Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP
Dim XMLDOC As New DOMDocument

sURL = "

https://webtrans.reallysimplesystems.com/api/api.v2.asmx"

sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""

http://www.w3.org/2001/XMLSchema-instance""

xmlns:xsd=""

http://www.w3.org/2001/XMLSchema""

xmlns:soap=""

http://schemas.xmlsoap.org/soap/envelope/"">"

sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <GetAccounts xmlns=""ReallySimple"">"
sEnv = sEnv & " <CustomerId>MyID</CustomerId>"
sEnv = sEnv & " <APIPassword>MyPass</APIPassword>"
sEnv = sEnv & " <SearchField>" & txtFieldname & "</SearchField>"
sEnv = sEnv & " <SearchValue>" & Replace(txtValueName, "&", "&amp;") & "</SearchValue>"
sEnv = sEnv & " </GetAccounts>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"

With xmlhtp
.Open "POST", sURL, False
.setRequestHeader "Host", "webtrans.reallysimplesystems.com"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction", "ReallySimple/GetAccounts"

.Send sEnv

XMLDOC.LoadXML .responseText
XMLDOC


XMLDOC.Save CurrentProject.path & "\WebQueryResult.xml"

End With

txtStatus.value = xmlhtp.StatusText


MsgBox "DONE"

'''''''''''''''''''''''''''''''''''''''''''''''''''''

I have a problem when I want to import the new XML file. Because the element name "Account" is the same as the table name I can either replace this table or append to it.

What I want to do is save this data to a temporary table so I can remove any ascii coded symbols like "&#1050;"

Has anyone got any experience of this?

 

[kjv1611]

Haven't tried this myself, but I wonder if you could literally just create it as a temp table, then... I don't recall if there is anything specific you have to do, but you could just try adding a ~ to the table name when you import it.

Here's a previous discussion where the talked about diff methods of doing the same - you could see if one of those fits your current scenario:
thread705-1446576