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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
If you can give the URL of the XML file, and tell us which values to be extracted, we can find a solution.
 

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
So, which datas do you need to be extracted?
 
Last edited:

Cook13s

New Member
Joined
Dec 7, 2017
Messages
12

ADVERTISEMENT

<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

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

Rules Violation
Joined
Oct 26, 2002
Messages
1,075

ADVERTISEMENT

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
 

Cook13s

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

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
:)

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:

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,525
Members
414,245
Latest member
Major Aly

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
Top