Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

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


Open in new window


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

Jim.
Avatar of 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



<<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
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial