Extract data from few XML file into Excel.
Closed
GizmoIE
Posts
14
Registration date
Thursday May 30, 2019
Status
Member
Last seen
June 13, 2019
-
May 30, 2019 at 06:32 AM
Blocked Profile - Jun 14, 2019 at 09:16 AM
Blocked Profile - Jun 14, 2019 at 09:16 AM
Related:
- Extract data from xml file to excel
- Transfer data from one excel worksheet to another automatically - Guide
- Notepad++ xml plugin - Guide
- How to open .msi file - Guide
- Excel date format dd.mm.yyyy - Guide
- Vba code to search data in excel - Guide
3 responses
Running the code that you already have, without counting the rows, places the xml entries correctly on the sheet, one by one. I removed the last line of .databinding.refresh and it does not produce the last line being duplicated.
Give it a go!
Give it a go!
GizmoIE
Posts
14
Registration date
Thursday May 30, 2019
Status
Member
Last seen
June 13, 2019
Jun 13, 2019 at 09:33 AM
Jun 13, 2019 at 09:33 AM
Yeah you right looks like it should work now, will check once back home :) thanks alot.
Just to clarify on my example; I used the following XML file and schema to map my sheet. I made three copies of the same file, naming them ....1.xml, ...2.xml, ....3.xml.
I only altered one line in your posted code; databindings.refresh.
With this code:
So, if your code, still does not produce what was shown in the above image of my sheet, then you need to look at the source of your data, as the schema that was published does not look to be complete, specifically there is no close to the ROOT (</root>)
I only altered one line in your posted code; databindings.refresh.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Root>
<EmployeeInfo>
<Name>Jane Winston</Name>
<Date>2001-01-01</Date>
<EMPCODE>0001</EMPCODE>
</EmployeeInfo>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Airfare</Description>
<Amount>500.34</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Hotel</Description>
<Amount>200</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Taxi Fare</Description>
<Amount>100.00</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Long Distance Phone Charges</Description>
<Amount>57.89</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Food</Description>
<Amount>82.19</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-02</Date>
<Description>Food</Description>
<Amount>17.89</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-02</Date>
<Description>Personal Items</Description>
<Amount>32.54</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-03</Date>
<Description>Taxi Fare</Description>
<Amount>75.00</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-03</Date>
<Description>Food</Description>
<Amount>36.45</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-03</Date>
<Description>New Suit</Description>
<Amount>750.00</Amount>
</ExpenseItem>
</Root>
With this code:
Sub Import_Xml()
Dim arrFiles, bAppend As Boolean, XMLFilePath
arrFiles = Application.GetOpenFilename("XML files,*.xml", , "Upload New xml", , True)
If Not IsArray(arrFiles) Then MsgBox "No file selected. Process will be canceled.": Exit Sub
bAppend = MsgBox("Add to existing LIST (YES). Create new list (NO)", vbYesNo) = vbYes
With ActiveWorkbook.XmlMaps("Root_Map")
For Each XMLFilePath In arrFiles
MsgBox ("Sheet: " & XMLFilePath)
.Import XMLFilePath, bAppend
bAppend = False
Next
'.DataBinding.Refresh
End With
End Sub
So, if your code, still does not produce what was shown in the above image of my sheet, then you need to look at the source of your data, as the schema that was published does not look to be complete, specifically there is no close to the ROOT (</root>)
Jun 6, 2019 at 03:15 AM
Updated on Jun 6, 2019 at 06:27 AM
Why are you entering data into a cell? It is supposed to be imported by thw connection.
Jun 7, 2019 at 04:58 AM
Jun 7, 2019 at 06:06 AM
Jun 7, 2019 at 06:34 AM