How to send cell value as SOAP request in Excel?

Copper Contributor

Hi folks, hope someone can guide me please. I have a spreadsheet with 900+ rows of data which is concatenated to a point where I have a fully-formed SOAP request in column W for each row. Is there a way that I can either send all of these SOAP requests in one go (which would be great for the initial upload), or create a button for each row to send them individually (which might be useful if the data gets modified and the individual row needs to be sent again)?

For extra good data, I'd also like it to capture the SOAP response in column X so that I have a record of success/failure of each SOAP request, but it's not a showstopper if that doesn't happen.

 

I've seen posts that hint at the kind of thing I need but as my VB knowledge is next to zero, I can't translate them into what I need, which is essentially "for each row, take cell W, send it as a SOAP request, place response in cell X". Thank you!

4 Replies

@TheChad Can you explain what you are trying to achieve?  A quick Google search for "send SOAP request with Excel VBA" gives a few results, but the code is always complex. 

@Ingeborg Hawighorst Hi, thank you for your reply! My apologies if my original request wasn't very clear. What I have is a spreadsheet where each row has information about an object, and I need to send that data as a SOAP request through an API.

 

Each row is concatenated so that the complete SOAP request is in Column W. All I need to do is to have a VBA solution for sending that SOAP request, and if possible (but not essential), recording the SOAP response in Column X.

 

Ideally I would like to send all the SOAP requests in the spreadsheet in one VBA script, but I will also settle for having a way to send each SOAP request individually if that's the way it has to be done.

 

Thank you!

@TheChad 

Do you need help with sending the SOAP request or with looping through the values in the sheet? If you have almost no knowledge of VBA, then that is not the easiest project to begin with. Have you had a look at the code that you can find with the search I mentioned above? It definitely won't be any easier than these code examples.

@Ingeborg Hawighorst I need help with both, to be honest. I have looked at the code you suggested, and I'm still confused - you're right that this probably isn't the easiest place to start with VBA!

 

Would you be able to advise on a 'relatively simple' script that I could use to send an individual SOAP request please? It only needs to take the value of a cell in Column W, which is the fully-formed SOAP request, and just send that. Ideally it would be great if I can capture the response too, but I'm ok with not doing that if it's going to prove overly-complicated.

 

Thank you!