Show XML data into an existing table

[inddon]

Hello There,

I have a XML output received from another application stoerd in a string variable. I would like to show this data in the same workbook and in an exiting table.

I am looking for the VBA code to successfully bring about the above requirement.

I have attached a sample workbook (Sample Workbook for XML.xlsm), for your reference:
-XML sample data (Note: in reality the structure of the XML data is not fixed. It can change both in columns and rows)
-Table

Look ing forward to hearing from you.


Thank you & regards,
Don

<ROWSET>
   <ROW>
      <INVOICE_NUMBER>10145033156</INVOICE_NUMBER>
      <INVOICE_ID>30</INVOICE_ID>
    </ROW>
    <ROW>
      <INVOICE_NUMBER>10145033158</INVOICE_NUMBER>
      <INVOICE_ID>31</INVOICE_ID>
    </ROW>
</ROWSET>

 

[Marc L]

Hello, you can try the VBA method Workbook.XmlImportXml …​

​

 

[inddon]

Hello, you can try the VBA method Workbook.XmlImportXml …​

​

Thak you for Marc for the hint.

I found some code on the web. Not sure how to go about it.

Workbook.XmlImportXml (Data, ImportMap, Overwrite, Destination)
Workbook.XmlImportXml (<stringData>, <ImportMap ?>, Overwrite, <Range>)

What would be the ImportMap, as my structure of XML (columns <xmlttags> and rows will vary everytime it gets the xmldata into the variable)?

Could you please demonstrate the VBA code in my sample workbook?

Thanks
Don

 

[p45cal]

From that one cell it's pretty straightforward to convert to a proper table with Power Query:



Let's have sight of the xml file before you try to load it in Excel.

 

[inddon]

From that one cell it's pretty straightforward to convert to a proper table with Power Query:

View attachment 84482

Let's have sight of the xml file before you try to load it in Excel.

Thank you p45cal. Good idea to know about Power Query.

In VBA, the XML data is received from another application and stored in a VBA variable. From this VBA variable it needs to be displayed in a given Table. The structure of the XML received can vary (rows, columns).

How can I do this Power Query using VBA?

 

[Marc L]

What would be the ImportMap

Try with Nothing …​

​

 

[p45cal]

and stored in a VBA variable

Try:

Sub test()
bbb = "<ROWSET>   <ROW>      <INVOICE_NUMBER>10145033156</INVOICE_NUMBER>      <INVOICE_ID>30</INVOICE_ID>    </ROW>    <ROW>      <INVOICE_NUMBER>10145033158</INVOICE_NUMBER>      <INVOICE_ID>31</INVOICE_ID>    </ROW></ROWSET>"
ThisWorkbook.XmlImportXml bbb, Nothing, True, Range("A1")
End Sub

This will bring up an dialogue box each time and add an xmlmap (not very clever to keep adding new xmlmaps), then it will put the data at cell A1 of the active sheet (overwrites).

the XML data is received from another application

Better, how does the xml data get into the variable?
Power Query can fetch it directly for itself and no vba is required.

 

[Marc L]

According to the attachment a VBA demonstration for starters :​

Sub Demo1()
         Application.ScreenUpdating = False         
         If [H8].ListObject Is Nothing Then [H8].CurrentRegion.Clear Else [H8].ListObject.Delete
         ThisWorkbook.XmlImportXml [B8], Nothing, True, [H8]
    With [H8].ListObject
        .ListColumns(1).Range.Insert
        .Range(1) = "Sr."
        .DataBodyRange.Columns(1) = Evaluate("ROW(1:" & .ListRows.Count & ")")
        .Range.Columns.AutoFit
    End With
         Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[inddon]

Try with Nothing …​

​

Thank you, it works

 

[inddon]

Try:

Sub test()
bbb = "<ROWSET>   <ROW>      <INVOICE_NUMBER>10145033156</INVOICE_NUMBER>      <INVOICE_ID>30</INVOICE_ID>    </ROW>    <ROW>      <INVOICE_NUMBER>10145033158</INVOICE_NUMBER>      <INVOICE_ID>31</INVOICE_ID>    </ROW></ROWSET>"
ThisWorkbook.XmlImportXml bbb, Nothing, True, Range("A1")
End Sub

This will bring up an dialogue box each time and add an xmlmap (not very clever to keep adding new xmlmaps), then it will put the data at cell A1 of the active sheet (overwrites).



Better, how does the xml data get into the variable?
Power Query can fetch it directly for itself and no vba is required.

Thank you @p45cal. This works.

This will bring up an dialogue box each time and add an xmlmap

It did not bring any dialogue box. I wouldn't prefer this, as there would be many XML transformation to the various Tables

Better, how does the xml data get into the variable?
Power Query can fetch it directly for itself and no vba is required.

We have an excel VBA workbook which gets connected to a non-excel application.
We request data from this non-excel application only via it's API. The returned output is in XML format returned to the excel workbook, stored in a variable. From this variable, it needs to be displayed in a Table.

I am not quite familiar with Power Query, would be interested when VBA can connect to non-excel application and Power Query can get data via API's and display this in the worksheet.

 

[inddon]

According to the attachment a VBA demonstration for starters :​

Sub Demo1()
         Application.ScreenUpdating = False       
         If [H8].ListObject Is Nothing Then [H8].CurrentRegion.Clear Else [H8].ListObject.Delete
         ThisWorkbook.XmlImportXml [B8], Nothing, True, [H8]
    With [H8].ListObject
        .ListColumns(1).Range.Insert
        .Range(1) = "Sr."
        .DataBodyRange.Columns(1) = Evaluate("ROW(1:" & .ListRows.Count & ")")
        .Range.Columns.AutoFit
    End With
         Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Wonderful @Marc L
Thank you

 

[p45cal]

which gets connected to a non-excel application

What is the application?
Power Query can connect to loads of stuff…

 

[inddon]

What is the application?
Power Query can connect to loads of stuff…

It is a inhouse developed application hosted in Amazon Cloud.

Can you provide some info. regarding Power Query connecting to other stuff?
Can we write VBA code for Power Query?

Thanks