Fordraiders
asked on
making a web service call and retrieving data to a access table
does anyone have an example of making a web service call say to an SAP system or whatever and bring the data back into a Access table ?
Using vba code ?
Thanks
fordraiders
Using vba code ?
Thanks
fordraiders
ASKER
jim,
Thanks for the reply
Yes, option 2
I was successful using this :
Dim reader As New XMLHTTP60 '
reader.Open "GET", "https://xxxxxx1.dev-sap.cccxxx.com:55555/sap/commerce/ecompa_v2?sap-client=220", False, "cat", "mouse"
'
reader.setRequestHeader "Accept", "application/json"
reader.Send
Do Until reader.ReadyState = 4
DoEvents
Loop '
If reader.STATUS = 200 Then
MsgBox (reader.responseText)
Else
MsgBox "Unable to get data."
End If
Now, my issue is the service call I'm making needs a payload request. in this format ?
<QRPPRequest>
<AccountNumber>0999999</AccountNumber>
<QuoteNumber>049777777</QuoteNumber>
<Items>
<ProductId>4R55555</ProductId>
<ProductId>666TTACC6</ProductId>
</Items>
</QRPPRequest>
Integrating this into my above code or Yours ?
fordraiders
Thanks for the reply
Yes, option 2
I was successful using this :
Dim reader As New XMLHTTP60 '
reader.Open "GET", "https://xxxxxx1.dev-sap.cccxxx.com:55555/sap/commerce/ecompa_v2?sap-client=220", False, "cat", "mouse"
'
reader.setRequestHeader "Accept", "application/json"
reader.Send
Do Until reader.ReadyState = 4
DoEvents
Loop '
If reader.STATUS = 200 Then
MsgBox (reader.responseText)
Else
MsgBox "Unable to get data."
End If
Now, my issue is the service call I'm making needs a payload request. in this format ?
<QRPPRequest>
<AccountNumber>0999999</AccountNumber>
<QuoteNumber>049777777</QuoteNumber>
<Items>
<ProductId>4R55555</ProductId>
<ProductId>666TTACC6</ProductId>
</Items>
</QRPPRequest>
Integrating this into my above code or Yours ?
fordraiders
<<Now, my issue is the service call I'm making needs a payload request. in this format ? >>
So straight XML. Most API web calls take either XML or JSON (just another way of formatting a string of info).
Most API calls take those as part of the body:
reader.body =
Depending on the contents, it may need to be Base64 encoded. Again, this is where the lib comes in handy.
Jim.
So straight XML. Most API web calls take either XML or JSON (just another way of formatting a string of info).
Most API calls take those as part of the body:
reader.body =
Depending on the contents, it may need to be Base64 encoded. Again, this is where the lib comes in handy.
Jim.
ASKER
jim, I guess what I'm trying to say then is my payload would/could be a range of data on my excel workbook.
SAY: just as an example:
SHEET1
A1 B1 C1
Quotenumber AccountNumber Productid
123 80987 1QAW2
123 80987 3E4R5
123 80987 34RE4
FOR XML how do i get the code to read this data to send in my request
Most all the examples are single line requests?
fordraiders
SAY: just as an example:
SHEET1
A1 B1 C1
Quotenumber AccountNumber Productid
123 80987 1QAW2
123 80987 3E4R5
123 80987 34RE4
FOR XML how do i get the code to read this data to send in my request
Most all the examples are single line requests?
fordraiders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://github.com/VBA-tools/VBA-Web
Which you can use to make web service calls. There's a bit of a learning curve to use it, but it handles JSON and a lot of others things as well if you need it.
The other thing you can do is reference the Windows HTTP lib and make the call yourself directly. i.e.:
Open in new window
The call you need to make of course depends on what your are interfacing to.
Jim.