making a web service call and retrieving data to a access table

There's this lib:

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.:

                  ' Capture the CC

                  ' Set the correct URL
110               strPostURL = "https://secure.authorize.net/gateway/transact.dll"

120               strPostSting = ""
130               strPostSting = strPostSting & "x_login=" & UrlEncode(strAPILogin) & "&"
140               strPostSting = strPostSting & "x_tran_key=" & UrlEncode(strTransactionKey) & "&"
                  'For debugging.
                  'strPostSting = strPostSting & "x_test_request=" & URLEncode("TRUE") & "&"
150               strPostSting = strPostSting & "x_version=" & UrlEncode("3.1") & "&"
160               strPostSting = strPostSting & "x_delim_data=" & UrlEncode("TRUE") & "&"
170               strPostSting = strPostSting & "x_delim_char=" & UrlEncode("|") & "&"
180               strPostSting = strPostSting & "x_relay_response=" & UrlEncode("FALSE") & "&"
190               strPostSting = strPostSting & "x_email_customer=" & UrlEncode("FALSE") & "&"

200               strPostSting = strPostSting & "x_type=" & UrlEncode("PRIOR_AUTH_CAPTURE") & "&"
210               strPostSting = strPostSting & "x_trans_id=" & UrlEncode(rs!CCTransactionID) & "&"

                  ' Additional fields can be added here as outlined in the AIM integration
                  ' guide at: http://developer.authorize.net
220               strPostSting = left(strPostSting, Len(strPostSting) - 1)

                  ' We use xmlHTTP to submit the input values and record the response
                  Dim objRequest As New MSXML2.XMLHTTP
230               objRequest.Open "POST", strPostURL, False
240               objRequest.Send strPostSting
250               strPostResponse = objRequest.ResponseText
                  'Debug.Print strPostResponse
260               Set objRequest = Nothing

                  ' the response string is broken into an array using the specified delimiting character
270               arrResponse = Split(strPostResponse, "|", -1)

280               If arrResponse(0) = 1 Then
                      ' Amount was captured.
                      ' Update order import tracking table
290                   strCommand = "UPDATE tblOrdImportTracking SET CCCapturedAt = '" & Now() & " ' WHERE JobNumber = " & !JobNumber & " AND ExportersOrderNumber = '" & !ExportersOrderNumber & "'"
300                   cnn.Execute strCommand, lngRecordsAffected, adCmdText
310                   If lngRecordsAffected <> 1 Then Stop

Select allOpen in new window

 The call you need to make of course depends on what your are interfacing to.

Jim.

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

<<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.

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