Help with Importing XML or JSON online

Cook13s

New Member
Joined
Dec 7, 2017
Messages
12
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
If you can give the URL of the XML file, and tell us which values to be extracted, we can find a solution.
 
Upvote 0
<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.
 
Upvote 0
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
 
Upvote 0
Try the code below code and if its OK for you, then change the Ranges mentioned in the code to suit your needs.

Code:
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
 
Upvote 0
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
 
Upvote 0
:)

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;

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

with, something like this;

Code:
MyFile = "http://www.MySite.com/MyXMLfile.xml"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top