Run a SOAP statement using a VBA macro to import data

[nirbhaysharma]

Hi All,

I want to implement a macro, that can run a SOAP statement (present in the file itself), and send the values present in the excel file to a particular system.
Let me break it down for you:

1) there is an excel file with data on something...say Sales Calls made by a Sales Rep
2) there is a pre-tested SOAP statement that will be used to load the data inside the excel using a webservice and a username password authentication, and one by one run the soap statement for all the records.

I am new to SOAP but managed to learn it enough to get a SOAP request running for a record manually in SOAP UI. Now it needs to be automated using excel VBA macros. Any help would be welcome. Even a basic structure or a different example of a SOAP running thru a macro dynamically picking values from excel cells, it would be great as i can extend it.

Plz have a look at the sample.

This will be the settings sheet in the excel file:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]###USERNAME###[/TD]
[TD]sample_user[/TD]
[/TR]
[TR]
[TD]###PASSWORD###[/TD]
[TD]sample_password[/TD]
[/TR]
[TR]
[TD]SOAP STATEMENT[/TD]
[TD]soap_statement[/TD]
[/TR]
</tbody>[/TABLE]

This will be the data file:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]###ACCOUNTNAME###[/TD]
[TD]###CONTACTNAME###[/TD]
[TD]###SUBJECT###[/TD]
[TD]###ALIAS###[/TD]
[TD]###TYPE###[/TD]
[/TR]
[TR]
[TD]Account1[/TD]
[TD]Contact1[/TD]
[TD]Subject1[/TD]
[TD]Alias1[/TD]
[TD]Type1[/TD]
[/TR]
[TR]
[TD]Account2[/TD]
[TD]Contact2[/TD]
[TD]Subject2[/TD]
[TD]Alias2[/TD]
[TD]Type2[/TD]
[/TR]
[TR]
[TD]Account3[/TD]
[TD]Contact3[/TD]
[TD]Subject3[/TD]
[TD]Alias3[/TD]
[TD]Type3[/TD]
[/TR]
[TR]
[TD]Account4[/TD]
[TD]Contact4[/TD]
[TD]Subject4[/TD]
[TD]Alias4[/TD]
[TD]Type4[/TD]
[/TR]
[TR]
[TD]Account5[/TD]
[TD]Contact5[/TD]
[TD]Subject5[/TD]
[TD]Alias5[/TD]
[TD]Type5[/TD]
[/TR]
</tbody>[/TABLE]

What the macro should do is pick up the data from each row, put it in the soap statement and then run the statement to insert the data into the crm system.

Due to some reason i am unable to paste the SOAP statement here. hence i am giving a link to its snapshot. still, any ideas on how to paste it here?

snapshot of soap statement:

thanks in advance

 

[BartOon]

Hello,

So nothing about during this six next months ?
To many post over the web without any answer only thread started but not closed with a solution.

UP !

 

[BartOon]

Ok, it's a dead point, but I was able to figure it out, yiiiiipiieeee, to help (I hope) go to a french thread, SOAP Webservice authentifié par VBA (Excel) use google translate