Soap Query and dump in excel

[cornepiek]

Hi All

Sorry if this has been requested, i went through the previous posts, but i cant find the answer im looking for

Can anyone please help me with the following:

I want to make a soap database query using VBA, and then dump the report in excel readable sheet

This is my soap scrip form SoapUI:

wsdl Portal : https://mydatabase.com/wsdl/CustomerAdminService.wsdl

<soapenv:envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:cus="http://mydatabase.com/Porta/SOAP/Customer">
<soapenv:header>
<auth_info xsi:type="soap:AuthInfoStructure" xmlns:soap="http://schemas.portaone.com/soap">
<login xsi:type="xsd:string">username</login>
<token xsi:type="xsd:string">token_here</token>
</auth_info>
</soapenv:header>
<soapenv:body>
<cus:get_customer_list soapenv:encodingstyle="http://schemas.xmlsoap.org/soap/encoding/">
<getcustomerlistrequest xsi:type="soap:GetCustomerListRequest" xmlns:soap="http://schemas.portaone.com/soap">
</getcustomerlistrequest>
</cus:get_customer_list>
</soapenv:body>
</soapenv:envelope>
<soapenv:envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:cus="http://portabillings.skyconnect.co.za/Porta/SOAP/Customer"></soapenv:envelope>

 

[cornepiek]

ok, i was able to put together this code......

However, the code returns the soap parameters as listed in the .wsdl, and not the list of data.

What am i doing wrong?

Sub OldSOAPServiceEarlyBinding()






Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP60
Dim XMLDOC As New DOMDocument60

sURL = "https://myportal.com/wsdl/CustomerAdminService.wsdl"

sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soapenv:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:cus=""http://myportal.com/Porta/SOAP/Customer"">" & _
" <soapenv:Header>" & _
" <auth_info xsi:type=""soap:AuthInfoStructure"" xmlns:soap=""http://schemas.portaone.com/soap"">" & _
" <login xsi:type=""xsd:string"">mylogin</login>" & _
" <token xsi:type=""xsd:string"">mytoken</token>" & _
" </auth_info>" & _
" </soapenv:Header>" & _
" <soapenv:Body>" & _
" <cus:get_customer_list soapenv:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/"">" & _
" <GetCustomerListRequest xsi:type=""soap:GetCustomerListRequest"" xmlns:soap=""http://schemas.portaone.com/soap"">" & _
" </GetCustomerListRequest>" & _
" </cus:get_customer_list>" & _
" </soapenv:Body>" & _
"</soapenv:Envelope>"

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

.Send sEnv

XMLDOC.LoadXML .responseText


XMLDOC.Save "C:\mydir\xml.xml"

End With



'MsgBox "DONE"
End Sub

 

[Kyle123]

SOAP is designed to be used with auomated tooling, Excel doesn't provide this. You'd be better off consuming the JSON service: https://www.portaone.com/docs/pb/mr70/PortaBilling_API_MR70.html

 

[cornepiek]

Do you maybe have some advice in converting the SOAP call to JSON?

i know very little about JSON and i really need to complete this project

 

[Kyle123]

You don’t convert anything. Did you read the link I posted?

 

[cornepiek]

i gues ill need to use something similar to this code:

How do i make this work in excel VBA?


#!/usr/bin/perluse warnings;use strict;use Data::Dumper;use LWP::UserAgent;use HTTP::Request::Common qw{ POST };useJSON;use HTTP::Request::Common;use Getopt::Long;# If the server certificate is not trusted (e.g. it was not issued by atrusted certificate authority), then ignore it.$ENV{PERL_LWP_SSL_VERIFY_HOSTNAME}=0;# Define your User Agent# This is the object acting as a browser that# makes requests and receives responses.my $ua = LWP::UserAgent->new();#=================================================================#======================== LOGGING IN =============================#=================================================================# Forming the POST request for authentication.# It must include user’s login and password.my $request = POST( 'https://demo.your_domain.com:443/rest/Session/login/', [ params =>'{"login":"demoroot", "password":"u7h79o1$"}', ]);# Sending the authentication requestmy $response = $ua->request($request);# Getting the authentication information (exiting if unauthorized)my $auth_info = parse_and_print_response($response);exit(1) if ( ! $auth_info );# Receiving session_idmy $session_id = $auth_info->{session_id};#=================================================================#================ GETTING THE LIST OF CUSTOMERS ===================#=================================================================# To get the list of accounts forming the POST request to be sent# to the following URL:# https:///rest///## The request must include:# auth_info – mandatory authentication information# (using ‘session_id’ which was received in the previous request);## params – a set of method parameters in JSON format.$request = POST( 'https://demo.your_domain.com:443/rest/Customer/get_customer_list/',[ auth_info => sprintf(' { "session_id": "%s" } ', $session_id), params => ' { "limit":"10", "offset":"2"} ' ]);# Sending the request$response = $ua->request($request);# Receiving the list of accountsmy $customer_list = parse_and_print_response($response);exit(1) if ( ! $account_list );#=================================================================#======================== LOGGING OUT ============================#=================================================================# Sending the logout request$request = POST( 'https://demo.your_domain.com:443/rest/Session/logout/', [ params => sprintf(' { "session_id": "%s" } ', $session_id), ]);# sending request$response = $ua->request($request);parse_and_print_response($response);#=================================================================#======================= HELP SUBROUTING =========================#=================================================================sub parse_and_print_response { my $response = shift;# Parse and print data received in the method response:# in case of success the response will contain data formatted as JSON# in its body,# in case of fail the response will contain the '500 Internal Server# Error' and error information formatted as JSON in its body. my $content_data; if ( $response->is_success() ) { my $content_json = $response->decoded_content(); print( STDERR ( Dumper($content_json) ) ); # convert from JSON to Perl data structure $content_data = from_json($content_json); print( STDERR ( Dumper($content_data) ) ); } else { print( STDERR ( Dumper( $response->status_line(), $response->decoded_content() ) ) ); } return $content_data;}

 

[cornepiek]

Hi Kyle, I am quite familiar with that document (although an older version)

however, i am mediocre at best with VBA, and although i can read and troubleshoot JSON, i cannot change it to suit my needs (yet)
Hence the reason i am asking the smart people for advice on how to incorporate the JSON script into my VBA script i already have fro processing the information.

What i have been doing so far was manually running the SOAP query, pulling the XML result, converting it to CSV, and processing it further in excel with my VBA script.
The plan is to automate the first part of this process as well, so i can handoff this task to a user with less access and/or skill, to simply run the VBA macro on button click.....

Please advise

 

[DarkValley]

Hi All

Sorry if this has been requested, i went through the previous posts, but i cant find the answer im looking for

Can anyone please help me with the following:

I want to make a soap database query using VBA, and then dump the report in excel readable sheet

This is my soap scrip form SoapUI:

wsdl Portal : https://mydatabase.com/wsdl/CustomerAdminService.wsdl

<soapenv:envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:cus="MyDatabase.com domain name is for sale. Inquire now.">
<soapenv:header>
<auth_info xsi:type="soap:AuthInfoStructure" xmlns:soap="http://schemas.portaone.com/soap">
<login xsi:type="xsd:string">username</login>
<token xsi:type="xsd:string">token_here</token>
</auth_info>
</soapenv:header>
<soapenv:body>
<cus:get_customer_list soapenv:encodingstyle="http://schemas.xmlsoap.org/soap/encoding/">
<getcustomerlistrequest xsi:type="soap:GetCustomerListRequest" xmlns:soap="http://schemas.portaone.com/soap">
</getcustomerlistrequest>
</cus:get_customer_list>
</soapenv:body>
</soapenv:envelope>
<soapenv:envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:cus="http://portabillings.skyconnect.co.za/Porta/SOAP/Customer"></soapenv:envelope>

You must use the "Definition URL" which can be found in properties within the program SOAPUI.

Use this URL for your sURL in your code. The part to take from "Definition URL" from beginning until => " .svc "

Should look like this example:

sURL = "https://myportal.com/wsdl/CustomerAdminService.svc"