News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • Importing multiple XML files into Excel

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Importing multiple XML files into Excel

    • This topic has 41 replies, 18 voices, and was last updated 1 day ago.
    Viewing 33 reply threads
    • Author
      Posts
      • #491542
        WSzmagic
        AskWoody Lounger

        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.

      • #1417944
        Maudibe
        AskWoody_MVP

        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

        Attachments:
        • #1442061
          WSjames003
          AskWoody Lounger

          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

        • #1527540
          WSrahim
          AskWoody Lounger

          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

      • #1418061
        WSzmagic
        AskWoody Lounger

        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.

      • #1418178
        Maudibe
        AskWoody_MVP

        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.

        Attachments:
      • #1418509
        Maudibe
        AskWoody_MVP

        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

        Attachments:
        • #1418650
          WSzmagic
          AskWoody Lounger

          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.

      • #1418979
        Maudibe
        AskWoody_MVP

        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.

        Attachments:
        • #1419235
          WSzmagic
          AskWoody Lounger

          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

        • #1468613
          WSandysimm2000
          AskWoody Lounger

          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

        • #1506684
          WSSams998
          AskWoody Lounger

          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

        • #1551764
          WSRichard1966
          AskWoody Lounger

          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

      • #1419275
        Maudibe
        AskWoody_MVP

        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

      • #1433323
        WSkalimero
        AskWoody Lounger

        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

      • #1433354
        Maudibe
        AskWoody_MVP

        Sure! Let’s take a look.

        Maud

      • #1433363
        WSkalimero
        AskWoody Lounger

        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

        Attachments:
      • #1442202
        Maudibe
        AskWoody_MVP

        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

        • #1452452
          WSstuartinglis9@hotmail.com
          AskWoody Lounger

          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.

      • #1452684
        WSlastdante
        AskWoody Lounger

        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?

      • #1454932
        WSRogerG
        AskWoody Lounger

        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.

      • #1461052
        WScrj9
        AskWoody Lounger

        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

        • #1461232
          WSPaul Faoro Mwas
          AskWoody Lounger

          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.

          • #1461233
            WScrj9
            AskWoody Lounger

            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)

      • #1527562
        Maudibe
        AskWoody_MVP

        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

        • #1529139
          WSSpyros Christodoulou
          AskWoody Lounger

          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.

          Attachments:
      • #1528184
        WSrahim
        AskWoody Lounger

        Dear Maud

        Please see the attachment and please do something for me

        Attachments:
      • #1529459
        Maudibe
        AskWoody_MVP

        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

        Attachments:
      • #1529997
        Maudibe
        AskWoody_MVP

        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

        Attachments:
        • #1535117
          WSrahim
          AskWoody Lounger

          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.

          Attachments:
          • #1535130
            WSrahim
            AskWoody Lounger

            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.

            Attachments:
      • #1530002
        Maudibe
        AskWoody_MVP

        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

        Attachments:
      • #1530005
        Maudibe
        AskWoody_MVP

        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

        Attachments:
      • #1530007
        Maudibe
        AskWoody_MVP

        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

      • #1530751
        Maudibe
        AskWoody_MVP

        Rahim,

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

        42208-Rahim

        Attachments:
      • #1535490
        Maudibe
        AskWoody_MVP

        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

      • #1540422
        Maudibe
        AskWoody_MVP

        Let’s start by uploading some samples via zip file

        Maud

      • #1541045
        Maudibe
        AskWoody_MVP

        Yes, that can be done. working on it

      • #1541052
        Maudibe
        AskWoody_MVP

        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
        
        
        
        
        Attachments:
      • #1542938
        Maudibe
        AskWoody_MVP

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

        • #1543241
          WSMichael Blowers
          AskWoody Lounger

          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.”

      • #1543329
        Maudibe
        AskWoody_MVP

        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:



        Attachments:
        • #1543419
          WSMichael Blowers
          AskWoody Lounger

          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

          • #1543421
            WSMichael Blowers
            AskWoody Lounger

            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

      • #1543764
        Maudibe
        AskWoody_MVP

        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
        
        Attachments:
        • #1544673
          WSMichael Blowers
          AskWoody Lounger

          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

          Attachments:
          • #1545004
            WSMichael Blowers
            AskWoody Lounger

            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:

      • #1551873
        Maudibe
        AskWoody_MVP

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

      • #1560711
        WSpritchard.ev
        AskWoody Lounger

        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

      • #1560916
        Maudibe
        AskWoody_MVP

        Hi Prichard,

        Can you send me a sample of your xml files?

        Maud

        • #1560933
          WSpritchard.ev
          AskWoody Lounger

          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

      • #2356073
        MrGreen365
        AskWoody Lounger

        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?

        • #2356187
          Paul T
          AskWoody MVP

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

          cheers, Paul

      • #2356595
        Paul T
        AskWoody MVP

        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

    Viewing 33 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: Importing multiple XML files into Excel

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.