Connecting a data source to Excel via API

[hellfire45]

So somebody at my company has told me they would like a macro enabled Excel workbook that can connect to a certain Fortune 100 Electronics company's database system via API and extract customer requested information. A customer would be able to enter a quote # or some other UID and it will connect to their database system and extract data pertaining to product orders.

An API has been created to do this and I have API documentation for XML integration.


Has anybody ever done anything like this?
Any advice? Online resources? Any info needed from the manual that might help?

I'm not really sure how to get started.


Some info below from the XML Integration Manual:

Basic Coding Overview

This section will not cover the programmatic creation of the submit XML document, there are many utilities available in a variety of languages that facilitate this processing. Nor will this section cover the accessing and navigation through the response XML document, again, many utilities and practices are available.
Once the submit document is created the following steps should be taken by your automated process:

1. If necessary, convert the XML document to a String representation
2. Establish an HTTP connection to company's XML service
3. Set the HTTP's header property "Content-Type" to a value of "text/xml"
4. Set the HTTP's header property "Content-Length" to the exact length of your submit XML document's string representation
5. Post the XML document, as a string, through the HTTP connection from step 2 above
6. Obtain a response stream from the HTTP connection
7. Receive the response XML document as a string

[B]VB.Net Example[/B]

The following VB.Net code snippet was written in version 1.1[INDENT][INDENT][COLOR=#008000]' xmlDocAsStr is the submit XML document as a String[/COLOR] 
Dim byteArray(xmlDocAsStr.Length - 1) As Byte     
Dim objUTF8Encoding As  New UTF8Encoding
byteArray = objUTF8Encoding.GetBytes(xmlDocAsStr)
 [COLOR=#008000]' urlStr is companyURL as a String[/COLOR]
Dim con As CType(WebRequest.Create(urlStr), HttpWebRequest)   

con.Method = "POST"
con.ContentType = "text/xml"
con.ContentLength = byteArray.Length

Dim strmRequest As con.GetRequestStream()

strmRequest.Write(byteArray, 0, byteArray.Length)    [COLOR=#008000]' send the submit xml document[/COLOR]
strmRequest.Close()

Dim srResponse as New StreamReader(con.GetResponse().GetResponseStream(), Encoding.ASCII)
Dim sb as  srResponse.ReadToEnd() [COLOR=#008000] ' receive the response xml document[/COLOR]
[COLOR=#008000]' the response XML document is now in sb as a String[/COLOR]

[/INDENT]
[/INDENT]
srResponse.Close()  [COLOR=#008000]' ALWAYS close your connection ![/COLOR]

 

[John_w]

The code at https://www.mrexcel.com/forum/excel...e-json-body-send-post5086118.html#post5086118 shows how to send a request to a web service API using XMLhttp.

Within the XMLhttp object, XMLhttp.responseXML is a DOMDocument60 or DOMDocument object, and XMLhttp.responseXML.XML is the XML response as a string.

 

[hellfire45]

The code at https://www.mrexcel.com/forum/excel...e-json-body-send-post5086118.html#post5086118 shows how to send a request to a web service API using XMLhttp.

Within the XMLhttp object, XMLhttp.responseXML is a DOMDocument60 or DOMDocument object, and XMLhttp.responseXML.XML is the XML response as a string.

Thanks John. I see you responded on the other post as well. This is an encouraging start. I think I still need more guidance. This VBA is over my head and is a type that I haven't needed at all up to this point.

For example, I'm not really sure the unique pieces are of this API I'm trying to utilize that I need to plug into that VBA code.

Do you think it would be possible for you to provide anymore guidance for me to get started with this? I'll provide any information I can.

 

[John_w]

It's difficult to help without seeing the API documentation. The parts you need from that example are the With XMLhttp block, so your code would be something like this:

    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim httpReq As XMLHTTP60
        Dim XMLdoc As DOMDocument60
        Set httpReq = New XMLHTTP60
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Dim httpReq As XMLhttp
        Dim XMLdoc As DOMDocument
        Set httpReq = New XMLhttp
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

    With httpReq
        .Open "POST", "https://your_Url/", False
        .setRequestHeader "Content-Type", "text/xml"
        .send (your_xml_string)
        Set XMLdoc = .responseXML
    End With
    
    Debug.Print XMLdoc.xml  'the response as an XML string

The above uses early binding, so you must set a reference to MS XML v6.0 via Tools -> References in the VBA editor.

 

[hellfire45]

It's difficult to help without seeing the API documentation. The parts you need from that example are the With XMLhttp block, so your code would be something like this:

    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
        Dim httpReq As XMLHTTP60
        Dim XMLdoc As DOMDocument60
        Set httpReq = New XMLHTTP60
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
        Dim httpReq As XMLhttp
        Dim XMLdoc As DOMDocument
        Set httpReq = New XMLhttp
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

    With httpReq
        .Open "POST", "https://your_Url/", False
        .setRequestHeader "Content-Type", "text/xml"
        .send (your_xml_string)
        Set XMLdoc = .responseXML
    End With
    
    Debug.Print XMLdoc.xml  'the response as an XML string

The above uses early binding, so you must set a reference to MS XML v6.0 via Tools -> References in the VBA editor.

Thanks for doing what you can John. I'm going to keep this thread in mind as a resource as I go forward. Unfortunately, I think it's probably a risky move to post the actual API documentation since it seems illegal to share it?