Soap Query and dump in excel

cornepiek

New Member
Joined
Jun 22, 2010
Messages
48
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>
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

cornepiek

New Member
Joined
Jun 22, 2010
Messages
48
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
 

cornepiek

New Member
Joined
Jun 22, 2010
Messages
48
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,708

ADVERTISEMENT

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

cornepiek

New Member
Joined
Jun 22, 2010
Messages
48
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;}
 
Last edited:

cornepiek

New Member
Joined
Jun 22, 2010
Messages
48
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,056
Members
409,799
Latest member
camronmartin

This Week's Hot Topics

Top