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>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top