# Soap Query and dump in excel



## cornepiek (Feb 25, 2019)

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 (Feb 25, 2019)

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()
> 
> 
> 
> ...


----------



## Kyle123 (Feb 25, 2019)

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 (Feb 25, 2019)

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 (Feb 25, 2019)

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


----------



## cornepiek (Feb 25, 2019)

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

How do i make this work in excel VBA?


#!/usr/bin/perl*use* 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 request*my* $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_id*my* $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 accounts*my* $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 (Feb 25, 2019)

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 (Mar 26, 2021)

cornepiek said:


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




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"


----------

