Help with Importing XML or JSON online

[Cook13s]

It seems I can't find a straight answer anywhere, so I hope someone here is able to answer my question, or at least guide me in the right direction. I'd like to know if there's a way to import specific data from an online source, such as a json or xml file, have it automatically populate the excel worksheet with that specific data and ignore anything else not requested.

So for example, we use a ticket system that is 3rd party but does not have a way of exporting data to an excel worksheet. I discovered however that they do have an xml/json exterior that looks something like this.

JSON: https://imgur.com/6mJPEnv (removed sensitive data)

I was able to import the data and it imports everything as a connection. If I add it as a table, it only imports the top tree, which is a lot more work than just manual copy/paste. I would like to know if we can grab this data as a connection using the Get Data from Web function and then automatically populate cells in a work sheet that looks like this. Only adding the text data into the worksheet below. Mind you, the data vary from ticket to ticket but the functions seen on the left column of the JSON/Excel does not change.

Worksheet: https://imgur.com/oZ7F0zj

Thanks in advance.
I don't think I have a great understanding of VBA in Excel but I have messed with scripting before so if I need to get dirty with code, I can.

 

[Haluk]

If you can give the URL of the XML file, and tell us which values to be extracted, we can find a solution.

 

[Cook13s]

I can't provide the URL, it requires an internal account but I can provide a test xml/json file that is saved from the source.

Dropbox download: https://www.dropbox.com/sh/d3hxi506tico8d9/AACsjaHZga6tlKwZ-Xc_dg5Sa?dl=0

 

[Haluk]

So, which datas do you need to be extracted?

 

[Cook13s]

<requester-name>
<responder-name>

Under <Custom Field>
<customer_id_number_336006>
<sales_order_number_336006>
<invoice_number_336006>
<model_336006>
<version_336006>
<depot_confirmed_shipping_address_336006>
<warranty_336006>
<billabletest_336006>
<depot_shipping_type_336006>

Basically, the info should go in here like this: https://imgur.com/ZlOl4fl Don't mind the two inputs in the merged cells for System Type, I can fix that later.

 

[Cook13s]

Oh oops, I forgot this uses BBCode.

requester-name
responder-name

Under Custom Field

customer_id_number_336006
sales_order_number_336006
invoice_number_336006
model_336006
version_336006
depot_confirmed_shipping_address_336006
warranty_336006
billabletest_336006
depot_shipping_type_336006

 

[Haluk]

Try the code below code and if its OK for you, then change the Ranges mentioned in the code to suit your needs.

Sub Get_XML_Data()
    'Haluk
    '08/12/2017
    '
    Dim xDoc As Object
    Set xDoc = CreateObject("MSXML2.DOMDocument")
    xDoc.async = False
    xDoc.validateOnParse = False
    
    MyFile = Application.GetOpenFilename
    If MyFile = "False" Then Exit Sub
    
    xDoc.Load MyFile
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/requester-name")
    Key1 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/responder-name")
    Key2 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/customer_id_number_336006")
    Key3 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/invoice_number_336006")
    Key4 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/model_336006")
    Key5 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/depot_confirmed_shipping_address_336006")
    Key6 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/warranty_336006")
    Key7 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/billabletest_336006")
    Key8 = MyKey.Text
    
    Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/depot_shipping_type_336006")
    Key9 = MyKey.Text
    
    Range("A1") = "Requester Name:"
    Range("B1") = Key1
    Range("A2") = "Responder Name:"
    Range("B2") = Key2
    Range("A3") = "customer_id_number_336006:"
    Range("B3") = Key3
    Range("A4") = "invoice_number_336006:"
    Range("B4") = Key4
    Range("A5") = "model_336006:"
    Range("B5") = Key5
    Range("A6") = "depot_confirmed_shipping_address_336006:"
    Range("B6") = Key6
    Range("A7") = "warranty_336006:"
    Range("B7") = Key7
    Range("A8") = "billabletest_336006:"
    Range("B8") = Key8
    Range("A9") = "depot_shipping_type_336006:"
    Range("B9") = Key9
    Range("A:B").Columns.AutoFit
    
    Set MyKey = Nothing
    Set xDoc = Nothing
End Sub

 

[Cook13s]

Thank you very much! This did it! It's loading info into their respective cells now.

Now the only question I have left is: How do I get it use the data in connection/query loaded from the Get Data from Web option?

https://imgur.com/Lg2KkF6

 

[Haluk]

A different technique is used in my post and it has nothing to do with the method you are referring to.

So, if the related XML table is somewhere on the net, change the below lines;

    MyFile = Application.GetOpenFilename
    If MyFile = "False" Then Exit Sub

with, something like this;

MyFile = "http://www.MySite.com/MyXMLfile.xml"

 

[Cook13s]

Unfortunately, that did not work.

https://imgur.com/UggdF1M