Importing multiple XML files into Excel

Topic

New Reply

Hello,

I want to import all the XML files into Excel for reports. I was doing this one XML file at a time but this is of course silly.
Anyway a Excel VB Macro code for importing multiple XML files to a Excel sheet tab (XMLDATA) and copying the imported data in sequence into the Excel columns. All XML Files format are same. Is there a simple code for this? Browse to select the folder and import all XML files.

Thanks in advance.

Reply | Quote

Replies

zmagic,

This workbook will prompt you to navigate to a folder with XML files. It will load only XML files from the folder and sub folders to a sheet named “XMLData”. It will create a header with the file name and copy the file contents to each column.

HTH
Maud

Reply | Quote

HI Mate,

My scenario is , i want to import 100+ xml file in my excel 2010 via developer/import. To see all the data from individual xml sheet in 1 single lines.

any help could be great.

Thanks

James

Reply | Quote

Hi Maudibe,

I tried by your macro sheet but can I import all xml data by data field? (Example: column A contain date for all date, column B contain Age, column C contain Job. If any age data field found blank in xml then column B cell will blank and put Job data in column C. Please give me a solution.

Thanks in advance.

zmagic,

This workbook will prompt you to navigate to a folder with XML files. It will load only XML files from the folder and sub folders to a sheet named “XMLData”. It will create a header with the file name and copy the file contents to each column.

HTH
Maud

Reply | Quote

Hi Maudible,

The data from the XML files are imported out of sequence into the Excel columns. Is there a simple fix to the code for this. Secondly it only imports the first file and other xml files are not imported.

When i import the file manually it order is perfect but whereas with this code its in single line order, un-readable to create a report.

Reply | Quote

It reads and writes sequentially from the file to Excel. I don’t see how it is possible that the lines are out of order. As far as the order of the files in the columns, they are read alphabetically from the main folder and then from each subfolder. Here is a screenshot of the results produced’

35209-xmlFile

whereas with this code its in single line order, un-readable to create a report.

Not sure what you mean here. Each line from the XML file is copied to the next line in the Excel file. This is the format I generate in my work and create reports from them with no issues.

Reply | Quote

zmagic,

Are you looking for the file not to have a linefeed placed after the line is read? Output would look like this:

35232-XML-Format

Reply | Quote

zmagic,

Are you looking for the file not to have a linefeed placed after the line is read? Output would look like this:

35232-XML-Format

Hi Maudibe,
I open the file with option of ‘As an XML Table’. This option set xml file in order of column and rows automatically.

Reply | Quote

zmagic,

Sorry, I misunderstood. I thought you wanted to read the xml file line by line onto a spreadsheet which the initial code does. You want the file imported not to view the XML coding but to display the file in human readable form. I now understand your intent.

By not having a sample file, this is a bit difficult to determine the schema. However, I took a stab at it and this works well with XML files I have. Give it a go.

Reply | Quote

zmagic,
By not having a sample file, this is a bit difficult to determine the schema. However, I took a stab at it and this works well with XML files I have. Give it a go.

Thanks Maud, You have done it, Maud 1 help more while importing multiple xml it imports the Header Row for each file, Please delete that header rows. Not required

Reply | Quote

zmagic,

Sorry, I misunderstood. I thought you wanted to read the xml file line by line onto a spreadsheet which the initial code does. You want the file imported not to view the XML coding but to display the file in human readable form. I now understand your intent.

By not having a sample file, this is a bit difficult to determine the schema. However, I took a stab at it and this works well with XML files I have. Give it a go.

Hi Maudibe – I have a similar situation where I need to extract xml tags from some drawing files. There is no “map” supplied. If I open them individually I get a lot of lines and rows – I am new to xml tags so not sure if this is normal!
I can however trim the xml tags required right down to approx. 10 tags.

You mention that you can extract particular tags out in the VBA – how – any clue would be appreciated.

Thanks in advance.
Andy

Reply | Quote

Hi maudibe,

I am using the “XML Files_Revised” macro . Can file names of xml get printed in another column in records of that particular xml.

Thanks in advance
Sam

Reply | Quote

Hi Maudibe

Love the script file, never used VB before but it worked a treat, I was wondering, If I may is it possible to get the script to delete the xml files once read?
Any help would be very welcome.

Cheers

Richard

Reply | Quote

Please delete that header rows. Not required

zmagic,

For me to do that, I will need several samples of your XML files to determine how the header section is coded. Is that possible?

For my work, I read XML files into Excel in the fashion of the first macro I wrote for you, line-by-line coding. From that point, I modify them to suit our needs to create customized reports. I prefer doing the modifications in Excel as opposed to Notepad or an XML editor because of the VBA routines I can write to automate the process. I was guessing you were doing something along the same lines. Sorry for the confusion.

Maud

Reply | Quote

Hi

That’s a great job you did. It’s almost what I need.
I would have 2 more functionalities to ask
– use a XML DTD tu put data to the right row (because some XML files don’t use all the tags)
– Have only one header row.
– when a XML file is read, copy it to a subfolder called for example “Archives”

Could I send you files (.xsd and several samples) ?

Thanks

Reply | Quote

Sure! Let’s take a look.

Maud

Reply | Quote

Great

The differents samples with differents formats are in the TEST folder.

As you can see in the screenshot, datas are not always in the good row. You’ll notice that when there is only 1 data in a file (ie 2.xml and 5.xml), the header is not shown.

If you see row O, you’ll also notice that when ns1:Sts=PDNG, there is not ns1:Cd. I tried to find a XML file who have all the rows (so that I can import at first) but

36015-Microsoft-Excel-XML-Files_Revised-v1.1-test-DTD.xlsm

Here is what it woull looks like

36014-Goal

Reply | Quote

Guys,

Need Sample xml files to help you. An excel spreadsheet with how you would like it to be displayed would also be vey helpful.

Maud

Reply | Quote

Hi,

The above script is awesome, very impressive(i am not a techie)! I used it and it worked nearly perfectly.

The problem i encountered was that one single xml tag contains nearly 500,000 characters and it maybe even bigger than that. The tag was exported to excel but only the first 32759 entries were imported. To export that single tag would have taken 14 excel cells to export. Is there a way which the tag can be split down into columns each containing the first 32 k of the characters, then the next column containing the next 32k and so on to the full output has been completed.?!?

i have an example file if needs be.

Reply | Quote

hi,

I used the fle “XML Files_Revised.xlsm” but reflected the following error. “error run time -2147217376 (80041020) dtd not allowed..how can I fix it?

Reply | Quote

Many thanks for these fantastic macros, Maudible.

I am using the “XML Files_Revised” macro and was wondering if it would be possible to auto-generate “labels” for each of the columns of data that are imported from all of the xml files. I will provide the following example, which I hope will clarify my request:

The xml file contains a field and value of:

The value 36584 nicely populates the respective row in a column, however could the “Price” be extracted from label to populate a header row in the worksheet?

Hoping that makes sense.

Reply | Quote

I am using the script, but having a problem.
I am importing xml files that comes from different adobe forms
most of the forms works perfect; but one of the forms puts a line with the “headers” first, and then repeats the data 3 times, over 3 lines. Like this

“Name”,”date”,”country”
Mike,12/07,UK
Mike,12/07,UK
Mike,12/07,UK
“Name”,”date”,”country”
George,07/04,FR
George,07/04,FR
George,07/04,FR

On the other xml files there are no header displayed, wich is fine

Reply | Quote

Hi,
Is it possible to import XML files with different headers? When the script finds a new column, it adds the column and the data for that column.

Reply | Quote

Hi,
Is it possible to import XML files with different headers? When the script finds a new column, it adds the column and the data for that column.

Yes, but when it does, it imports the file 3 times (1 line for the headers, and the 3 repeted lines with the data.

I tried to list only file of the same format (from the same form) and the 3 repeat is only comming when importing data from 1 specific form, the other forms behave normally.
I guess that it must be something in the forms (all created with Adobe life cycle)

Reply | Quote

Post #14: Need Sample xml files to help you. An excel spreadsheet with how you would like it to be displayed would also be vey helpful.

Please include sample XML files and a sample spreadsheet of the desired final view.

Maud

Reply | Quote

Please include sample XML files and a sample spreadsheet of the desired final view.

Maud

Hello Maidibe,
Fisrt of all i would like to thank you for your efforts and time spend so far on this.
I have created a zipped file that includes xml sample and excel file.
Is it possible while importing the xml files according to the OrderNumber field values to insert in different sheets?
Kind regards,42114-xml
Spyros.

Reply | Quote

Dear Maud

Please see the attachment and please do something for me

Reply | Quote

Rahim,

I looked at your XML files. All 3 have different mappings that complicate things a bit. That is why the data was not lining up. (see Image)

42127-Rahim1

What makes it more difficult is to devise code that is generic enough to use for any mapping. I have completed that part and the image above is a snapshot of the output of the code I have so far. It will be used to generate the header and extract the data from the nodes. I am now working on the code to move the mapping of the nodes for each file so they are aligned. I do not believe that will be difficult. What you have posted as the final view is achievable but I will need a little more time.

Spyros,

When the code for Rahim is completed, I will apply it to your request. Please be patient

Maud

Reply | Quote

Rahim,

This was a challenge! Because your xml files have different mappings, I had to create a common map that all 3 files would map to. The attached file will import all your xml files from a folder and its subfolders then list them in a table under the correct header.

Note: This mapping is specific to Rahim’s .xml files but can be adapted for other .xml files

HTH,
Maud

Reply | Quote

Rahim,

This was a challenge! Because your xml files have different mappings, I had to create a common map that all 3 files would map to. The attached file will import all your xml files from a folder and its subfolders then list them in a table under the correct header.

Note: This mapping is specific to Rahim’s .xml files but can be adapted for other .xml files

HTH,
Maud

Dear Maudibe,

Sorry for late response. At first a lot of thanks for your kind support and your time. I tried to use this mapping but I faced problem with this. That’s why I’m attaching herewith the expected column head which will be import from different xml file as per column. Could you please look the column head attachment. Please help me on this.

Thanks in advance.

Reply | Quote

Dear Maudibe,

Sorry for late response. At first a lot of thanks for your kind support and your time. I tried to use this mapping but I faced problem with this. That’s why I’m attaching herewith the expected column head which will be import from different xml file as per column. Could you please look the column head attachment. Please help me on this.

Thanks in advance.

I forgot to add the xml database where all column head exist.

Reply | Quote

Spyros,

This worksheet will import your XML files according to the Order Number into sheets with the same name. If the sheet does not exist, it will create it. You did not specify which sheets to import them into so it was an assumption that the sheet has the same name as the order number.

HTH,
Maud

Reply | Quote

Can file names of xml get printed in another column in records of that particular xml.

Hi Sam,

The following file will place the name of the XML file in column A

Reply | Quote

I would have 2 more functionalities to ask
– use a XML DTD tu put data to the right row (because some XML files don’t use all the tags)
– Have only one header row.
– when a XML file is read, copy it to a subfolder called for example “Archives”

Could I send you files (.xsd and several samples) ?

Kalimero,

Sending the .xsd would be great as it would save me from having to write a schema file and the mapping of the tags. Having the samples would also be a plus.

I have a similar situation where I need to extract xml tags from some drawing files.

Andy,

Could you send me some sample .xml files ?

Thanks,
Maud

Reply | Quote

Rahim,

I forgot to mention that you have to set a reference to Microsoft XML

42208-Rahim

Reply | Quote

Rahim,

The above database you provided has 352 column fields as compared to the 68 columns in your first example. This would absorb more time than I have willing to spare to rebuild the mapping to that size. Maybe some else would like to pick up the ball on this one.

Maud

Reply | Quote

Let’s start by uploading some samples via zip file

Maud

Reply | Quote

Yes, that can be done. working on it

Reply | Quote

dz,

Here is the solution I came up with. Place all your XML files in a folder. Click the “Get Data” button. The code will search your specified folder and sequentially load only XML files. Only the data from the 3 nodes you indicated will be extracted and placed in columns A-C respectively. You must reference the Microsoft Scripting Runtime Library in your project for the code to successfully run.

HTH,
Maud

42864-dz1

Code:

Public row As Long
Sub ListFiles()
[COLOR=”#008000″]’LISTFILES AND LISTMYFILES MODIFIED FROM
‘http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder
‘CODE TO EXTRACT AND MAP DATA BY MAUDIBE[/COLOR]
[COLOR=”#008000″]’——————————————————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim ShellApplication As Object
    Application.ScreenUpdating = False
[COLOR=”#008000″]’——————————————————————–
‘GET SOURCE FOLDER[/COLOR]
    Set ShellApplication = CreateObject(“Shell.Application”).BrowseForFolder(0, “Please choose a folder”, 0, OpenAt)
    If ShellApplication Is Nothing Then
        Exit Sub
    Else: Path = ShellApplication.self.Path
    End If
    Set ShellApplication = Nothing
[COLOR=”#008000″]’——————————————————————–
‘ADD HEADER[/COLOR]
    [a3] = “dc:identifier”
    [b3] = “year”
    [c3] = “stpiece_per”
    row = 4
[COLOR=”#008000″]’——————————————————————–
‘CALL ROUTINE TO CYCLE THROUGH FOLDER[/COLOR]
    Call ListMyFiles(Path, True)
End Sub



Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Application.ScreenUpdating = False
[COLOR=”#008000″]’——————————————————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Set MyObject = New Scripting.FileSystemObject
    Set MySource = MyObject.GetFolder(mySourcePath)
[COLOR=”#008000″]’——————————————————————–
‘FIND XML FILES ONLY IN SCOURCE FOLDER[/COLOR]
    For Each myfile In MySource.Files
        If Right(myfile.Name, 3) = “XML” Or Right(myfile.Name, 3) = “xml” Then
[COLOR=”#008000″]’————————————————————-
‘IMPORT XML NODES[/COLOR]
           Set xmlDoc = CreateObject(“Microsoft.XMLDOM”)
           xmlDoc.SetProperty “SelectionLanguage”, “XPath”
           xmlDoc.Async = False
           xmlDoc.Load (mySourcePath & “” & myfile.Name)
           Set nodeXML1 = xmlDoc.getElementsByTagName(“dc:identifier”)
           Set nodeXML2 = xmlDoc.getElementsByTagName(“year”)
           Set nodeXML3 = xmlDoc.getElementsByTagName(“stpiece_per”)
           Cells(row, 1) = nodeXML1(0).Text
           Cells(row, 2) = nodeXML2(0).Text
           Cells(row, 3) = nodeXML3(0).Text
           row = row + 1
       End If
    Next
[COLOR=”#008000″]’————————————————————-
‘FIND XML FILES ONLY IN SOURCE SUBFOLDERS[/COLOR]
    If IncludeSubfolders Then
       For Each MySubFolder In MySource.SubFolders
          Call ListMyFiles(MySubFolder.Path, True)
       Next
    End If
[COLOR=”#008000″]’————————————————————-
‘CLEANUP[/COLOR]
    Set MyObject = Nothing
    Set MySource = Nothing
    Set xmlDoc = Nothing
    Set nodeXML1 = Nothing
    Set nodeXML2 = Nothing
    Set nodeXML3 = Nothing
Application.ScreenUpdating = True
End Sub



Public Sub reset()
    Columns(“A:C”).Select
    Selection.ClearContents
    Range(“A1”).Select
End Sub

Reply | Quote

Will you be creating new tags with values or just writing new values to existing tags?

Reply | Quote

Hi, I have loads of files like this (sample below) and I am trying to find a way of grabbing the ‘HeadLine’, ‘Author’ and ‘body’ tags values into a single Excel doc listing (a file per line). They are in a complex file hierarchy structure but I could move to a single file with time.

Any help would be massively appreciated!

Mike

<!– –>

–

–

–

–

Stephis buzzing atworld finalbid

JAPAN v ENGLAND

–

SKIPPER Steph Houghton has urged England’s women to go one better than their male counterparts did 25 years ago at Italia 90.

Tonight’s semi-final with Japan in Edmonton is the country’s biggest World Cup clash since Gazza and co lost on penalties in the last four to Germany in Turin.

Houghton is thrilled the Lionesses have got this far and is calling for yet another big effort as they bid to reach Sunday’s final in Vancouver.

She said: “We should be really proud of being the first England women’s team to reach a World Cup semi-final. It’s the biggest game in the history of our women’s game.

“Everyone is buzzing but we deserve this moment. We have worked so hard to get in this position. We gave everything against Canada, we knew it was a massive opportunity to make history.

“Now, potentially, we have two massive games left, and hopefully that ends with a winner’s medal, but we know we will have to be at our very best against Japan.”

Reply | Quote

Michael,

I have adapted the code I wrote for dzvpnhvdnp to extract data from the tags you have specified. Place all your xml files in a folder then click the “Get Data” Button. The HeadLine, Author, and body tags values will be extracted. In the example you posted, there was no Author value. For testing purposes, I made 3 additional copies of the file you provided and added the Author values for 2 of the 4. If there is no Author, it will be bypassed.

When you posted the contents of the XML file, you included the expand markers (“-“) from where you copied it (most likely from being displayed in a browser). They must not be present in the actual XML file:

–
–
–

–
–

42993-MB

HTH,
Maud

Reply | Quote

Thanks so much! seems to have worked but took a while to process my 14.5k XML files! Saved me so much time. Have a good holiday.

Best wishes, Mike

Reply | Quote

Hi Maud,

Massive thanks for your help again! Do you know if there is anyway I can modify it to also capture ‘Publication Name’ and ‘Publication Date’?

Best wishes,

Mike

Reply | Quote

Mike,

Attached is a spreadsheet with the modified code to pull out the values of the attributes you requested along with the values of the 3 tags. You will need to set references to Microsoft Scripting Runtime and Microsoft XML, vX.X where X.X is the highest version installed on your computer.

HTH,
Maud

43013-MB4

43011-MB2

Code:

Public row As Long
Sub ListFiles()
[COLOR=”#008000″]’LISTFILES AND LISTMYFILES MODIFIED FROM
‘http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder
‘CODE TO EXTRACT NODE AND ATTRIBUE VALUES BY MAUDIBE
‘——————————————————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim ShellApplication As Object
    Application.ScreenUpdating = False
[COLOR=”#008000″]’——————————————————————–
‘GET SOURCE FOLDER[/COLOR]
    Set ShellApplication = CreateObject(“Shell.Application”).BrowseForFolder(0, “Please choose a folder”, 0, OpenAt)
    If ShellApplication Is Nothing Then
        Exit Sub
    Else: Path = ShellApplication.self.Path
    End If
    Set ShellApplication = Nothing
[COLOR=”#008000″]’——————————————————————–
‘ADD HEADER[/COLOR]
    [a3] = “Headline”
    [b3] = “Author”
    [c3] = “Body”
    [d3] = “Publication Name”
    [e3] = “Publication Date”
    row = 4
[COLOR=”#008000″]’——————————————————————–
‘CALL ROUTINE TO CYCLE THROUGH FOLDER[/COLOR]
    Call ListMyFiles(Path, True)
End Sub


Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Application.ScreenUpdating = False
[COLOR=”#008000″]’——————————————————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim BookTypeArray() As String
    Dim xmldoc As DOMDocument
    Dim List As IXMLDOMNodeList
    Dim Node As IXMLDOMNode
    Dim Att As IXMLDOMAttribute
    Set MyObject = New Scripting.FileSystemObject
    Set MySource = MyObject.GetFolder(mySourcePath)
[COLOR=”#008000″]’——————————————————————–
‘FIND XML FILES ONLY IN SCOURCE FOLDER[/COLOR]
    For Each myfile In MySource.Files
        If Right(myfile.Name, 3) = “XML” Or Right(myfile.Name, 3) = “xml” Then
[COLOR=”#008000″]’————————————————————-
‘IMPORT XML NODE VALUES[/COLOR]
            Set xmldoc = CreateObject(“Microsoft.XMLDOM”)
            xmldoc.SetProperty “SelectionLanguage”, “XPath”
            xmldoc.async = False
            xmldoc.Load (mySourcePath & “” & myfile.Name)
            Set nodeXML1 = xmldoc.getElementsByTagName(“HeadLine”)
            Set nodeXML2 = xmldoc.getElementsByTagName(“Author”)
            Set nodeXML3 = xmldoc.getElementsByTagName(“body”)
            Cells(row, 1) = nodeXML1(0).Text
            Cells(row, 2) = nodeXML2(0).Text
            Cells(row, 3) = nodeXML3(0).Text
[COLOR=”#008000″]’————————————————————-
‘IMPORT NODE ATTRIBUTES[/COLOR]
            myvalue = “”
            Set List = xmldoc.SelectNodes(“//Property”)
            For Each Node In List
                For Each Att In Node.Attributes
                    If Att.nodeTypedValue = “Publication_Name” Then
                        Cells(row, “D”) = myvalue
                    End If
                    If Att.nodeTypedValue = “Publication_Date” Then
                        Cells(row, “E”) = Mid(myvalue, 5, 2) & “/” & Right(myvalue, 2) & “/” & Left(myvalue, 4)
                    End If
                    myvalue = Att.nodeTypedValue
                Next
            Next
            ‘End If
            row = row + 1
       End If
    Next
[COLOR=”#008000″]’————————————————————-
‘FIND XML FILES ONLY IN SOURCE SUBFOLDERS[/COLOR]
    If IncludeSubfolders Then
       For Each MySubFolder In MySource.SubFolders
          Call ListMyFiles(MySubFolder.Path, True)
       Next
    End If
[COLOR=”#008000″]’————————————————————-
‘CLEANUP[/COLOR]
    Set MyObject = Nothing
    Set MySource = Nothing
    Set xmldoc = Nothing
    Set nodeXML1 = Nothing
    Set nodeXML2 = Nothing
    Set nodeXML3 = Nothing
    Set List = Nothing
    Set Node = Nothing
    Set Att = Nothing
Application.ScreenUpdating = True
End Sub

Reply | Quote

Maud,

Thanks for the amendment to include the extra fields (Publication Name and Publication Date).

I have ran it a couple of time and seem to get blanks for the Publication Name and ‘/ll/null’ for the Publication Date. My project references seem to be the same as yours (though mine indicating ‘Microsoft Office 14.0 Object Library’, while yours is ‘15.0 Object Library’ – not sure if that makes a difference?). Sorry to ask – have I done something stupid / failed to amend code somewhere?

I have tried to do a grab indicating my current references and the errors.

Many thanks again for your advice and help and happy New Year!

Best, Mike

Reply | Quote

For some curious reason it is returning the value for the node attribute from the row above. Adjusting the Att.nodeTypedValue to the value from the next line return the correct value. I will make it my life’s challenge to try to understand why! Thanks for all the help. Mike:rolleyes:

Reply | Quote

Richard,
The are quite a few scripts in this thread. Which post are you referring to?

Reply | Quote

Hi Maudibe,

I was using a portion of the code you provided (Thank you by the way!!) but encountered a small error. I’ve currently got 9 XML files in the folder I’m pulling from and the code seems to be working however it only pulls the first line from each file.

What I’m looking to do is pull the “Container Number”, “PONumber” and “LPN” everytime the “LPN” field is not blank. Is that potentially an easy fix? Any help is appreciated!!

Public row As Long
Sub ListFiles()
‘LISTFILES AND LISTMYFILES MODIFIED FROM
‘http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder
‘CODE TO EXTRACT AND MAP DATA BY MAUDIBE
‘——————————————————————–
‘DECLARE AND SET VARIABLES
Dim ShellApplication As Object
Application.ScreenUpdating = False
‘——————————————————————–
‘GET SOURCE FOLDER
Set ShellApplication = CreateObject(“Shell.Application”).BrowseForFolder(0, “Please choose a folder”, 0, OpenAt)
If ShellApplication Is Nothing Then
Exit Sub
Else: Path = ShellApplication.self.Path
End If
Set ShellApplication = Nothing
‘——————————————————————–
‘ADD HEADER
[a3] = “ContainerNumber”
[b3] = “PONumber”
[c3] = “LPN”
row = 4
‘——————————————————————–
‘CALL ROUTINE TO CYCLE THROUGH FOLDER
Call ListMyFiles(Path, True)
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Application.ScreenUpdating = False
‘——————————————————————–
‘DECLARE AND SET VARIABLES
Dim BookTypeArray() As String
Set MyObject = New Scripting.FileSystemObject
Set MySource = MyObject.GetFolder(mySourcePath)
‘——————————————————————–
‘FIND XML FILES ONLY IN SCOURCE FOLDER
For Each myfile In MySource.Files
If Right(myfile.Name, 3) = “XML” Or Right(myfile.Name, 3) = “xml” Then
‘————————————————————-
‘IMPORT XML NODES
Set xmlDoc = CreateObject(“Microsoft.XMLDOM”)
xmlDoc.SetProperty “SelectionLanguage”, “XPath”
xmlDoc.Async = False
xmlDoc.Load (mySourcePath & “” & myfile.Name)
Set nodeXML1 = xmlDoc.getElementsByTagName(“ContainerNumber”)
Set nodeXML2 = xmlDoc.getElementsByTagName(“PONumber”)
Set nodeXML3 = xmlDoc.getElementsByTagName(“LPN”)
Cells(row, 1) = nodeXML1(0).Text
Cells(row, 2) = nodeXML2(0).Text
Cells(row, 3) = nodeXML3(0).Text
row = row + 1
End If
Next
‘————————————————————-
‘FIND XML FILES ONLY IN SOURCE SUBFOLDERS
If IncludeSubfolders Then
For Each MySubFolder In MySource.SubFolders
Call ListMyFiles(MySubFolder.Path, True)
Next
End If
‘————————————————————-
‘CLEANUP
Set MyObject = Nothing
Set MySource = Nothing
Set xmlDoc = Nothing
Set nodeXML1 = Nothing
Set nodeXML2 = Nothing
Set nodeXML3 = Nothing
Application.ScreenUpdating = True
End Sub

Public Sub reset()
Columns(“A:C”).Select
Selection.ClearContents
Range(“A1”).Select
End Sub

Reply | Quote

Hi Prichard,

Can you send me a sample of your xml files?

Maud

Reply | Quote

Hi Maud,

Please see attached for a sample XML (Had to save it as a txt). This particular example has 42 LPN Numbers that I’m trying to extract along with the corresponding Container Number’s and PO Number’s. Ideally, I’m looking to tweak this code so that it extracts all 42, and then moves to the next file in the folder and repeats the process.

Below I’ve also re-attached the code I attempted to tweak in a slightly more readable format.

Code:

Public row As Long
Sub ListFiles()
‘LISTFILES AND LISTMYFILES MODIFIED FROM
‘http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder
‘CODE TO EXTRACT AND MAP DATA BY MAUDIBE
‘——————————————————————–
‘DECLARE AND SET VARIABLES
    Dim ShellApplication As Object
    Application.ScreenUpdating = False
‘——————————————————————–
‘GET SOURCE FOLDER
    Set ShellApplication = CreateObject(“Shell.Application”).BrowseForFolder(0, “Please choose a folder”, 0, OpenAt)
    If ShellApplication Is Nothing Then
        Exit Sub
    Else: Path = ShellApplication.self.Path
    End If
    Set ShellApplication = Nothing
‘——————————————————————–
‘ADD HEADER
    [a3] = “ContainerNumber”
    [b3] = “PONumber”
    [c3] = “LPN”
    row = 4
‘——————————————————————–
‘CALL ROUTINE TO CYCLE THROUGH FOLDER
    Call ListMyFiles(Path, True)
End Sub


Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Application.ScreenUpdating = False
‘——————————————————————–
‘DECLARE AND SET VARIABLES
    Dim BookTypeArray() As String
    Set MyObject = New Scripting.FileSystemObject
    Set MySource = MyObject.GetFolder(mySourcePath)
‘——————————————————————–
‘FIND XML FILES ONLY IN SCOURCE FOLDER
    For Each myfile In MySource.Files
        If Right(myfile.Name, 3) = “XML” Or Right(myfile.Name, 3) = “xml” Then
‘————————————————————-
‘IMPORT XML NODES
           Set xmlDoc = CreateObject(“Microsoft.XMLDOM”)
           xmlDoc.SetProperty “SelectionLanguage”, “XPath”
           xmlDoc.Async = False
           xmlDoc.Load (mySourcePath & “” & myfile.Name)
           Set nodeXML1 = xmlDoc.getElementsByTagName(“ContainerNumber”)
           Set nodeXML2 = xmlDoc.getElementsByTagName(“PONumber”)
           Set nodeXML3 = xmlDoc.getElementsByTagName(“LPN”)
           Cells(row, 1) = nodeXML1(0).Text
           Cells(row, 2) = nodeXML2(0).Text
           Cells(row, 3) = nodeXML3(0).Text
           row = row + 1
       End If
    Next
‘————————————————————-
‘FIND XML FILES ONLY IN SOURCE SUBFOLDERS
    If IncludeSubfolders Then
       For Each MySubFolder In MySource.SubFolders
          Call ListMyFiles(MySubFolder.Path, True)
       Next
    End If
‘————————————————————-
‘CLEANUP
    Set MyObject = Nothing
    Set MySource = Nothing
    Set xmlDoc = Nothing
    Set nodeXML1 = Nothing
    Set nodeXML2 = Nothing
    Set nodeXML3 = Nothing
Application.ScreenUpdating = True
End Sub

Thanks for your help!!

Evan

Reply | Quote

On a similar theme to queries already answered in this thread. I have a macro which I’ve used to import one XML file (srml-8-2020-f2128559-matchresults.xml) into Excel, but how to I adapt to then automatic run the import using the next XML file in the folder…and so on until all XML files have been imported?

Reply | Quote

We would need a copy of the code to be able to advise.
Either paste the code or upload an empty spreadsheet.

cheers, Paul

Reply | Quote

Apologies – I thought I had attached this previously.

XMLImportMacro

Reply | Quote

You need to create a variable to hold the file name, then call the import in a loop.
e.g. XMLfilename = “E:\Work\My Documents\FANoty\srml-8-2020-f2128559-matchresults.xml”

To find all the filenames you need to set a directory and read the files in it. See this page.

I would create a new import sub to perform the file lookup and worksheet positioning, then call your existing ImportXML sub and pass the filename.
e.g. call ImportXML oFile.Name

Change your existing to: Sub ImportXML(XMLfilename)
Now you can replace the filename in “Source =”  with the variable XMLfilename.

cheers, Paul

Reply | Quote

I think this is probably stretching it for a VBA/Macro beginner. However, I did manage to create a list of files using the LoopThroughFiles code, just struggling to how to adapt my XML import macro to incorporate this despite your suggestion.

Thanks for trying to help, though.

Reply | Quote

If you want to provide your spreadsheet, zip it up first.
Alternatively, paste the code by going to the “Text” tab in your reply and pasting there. Then you can highlight the code and click the “CODE” button to make it look like code.

cheers, Paul

Reply | Quote

Thanks. I’ve since managed to implement a workaround outside of Excel by combining the XML files into one XML file via the command prompt and navigating to the appropriate directory:

copy *.xml combined.xml

I’ve then run a find and replace to remove the excess <?xml version=”1.0″ encoding=”UTF-8″?> tags (pasting back the one at the very beginning) and then added a <combined> node below this and a </combined> node at the very end.

I’m then able to import the data in the normal way (via Data, Get Data etc.) through Excel in one process (from combined.xml) and has produced the desired results.

Thanks again, though.

1 user thanked author for this post.

Paul T

Reply | Quote

hi, i have a lot of xml files, and some of them have different mappings so when i run code 35251 the output is not the same, i tried the code specifically for rahim but no success. Can someone help me?

Reply | Quote

We need to know what doesn’t work for you.
Can you post details of what you want and what you get that is not correct?

cheers, Paul

Reply | Quote