How to import multiple xml file into excel

Anonymous
2012-09-13T00:07:12+00:00

I have a lot of xml files which need to be imported into single excel file. I can able to import one by one but cannot select all at a time to import.

Is it possible to do through batch process or any other process?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

66 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2013-11-19T21:34:24+00:00

    TasosK,

    Thank you again for this wonderful code. I have a few questions, as I am not getting the desired results

    1. Did you mean to include the comment ".Column" half way through the code? Should it read the following instead?

    Set cH = WB.Sheets(1).Range(Cells(1, 1), Cells(1, c)).Find(What:=myWB.Sheets(1).Cells(1, x), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    1. I cannot get my columns to import. If I fill out row 1 with the column headers, after running the macro the output in blank.

    If I leave row one blank, I get only one column imported (Version) and repeated as many times as the Const N As Long column number. In this case it was 3

    A B C
    1 /my:Version /my:Version /my:Version
    2 v. AP 2.0 v. AP 2.0 v. AP 2.0
    3 /my:Version /my:Version /my:Version
    4 v. AP 2.0 v. AP 2.0 v. AP 2.0
    1. Here is a look into the XML file I am importing:

    <?xml version="1.0" encoding="utf-8"?>

    <?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:eGOR--Domestic-A-M:-myXSD-2013-06-24T11-56-50" solutionVersion="1.0.0.687" productVersion="12.0.0.0" PIVersion="1.0.0.0" href="URL HERE"?>

    <?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>

    <my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2013-06-24T11:56:50" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">

     <my:Header>

      <my:Date>2013-10-16</my:Date>

      <my:Location>AAA</my:Location>

      <my:Part>558</my:Part>

      <my:Verified>Yes</my:Verified>

      <my:Pairs>AAABBB</my:CPairs>

      <my:Room>B5</my:Room>

      <my:Name>CARLOS</my:Name>

      <my:Manager>BOB</my:Manager>

      <my:Supervisor>JEN</my:Supervisor>

     </my:Header>

     <my:Version>v. AP 2.0</my:Version>

    </my:myFields>

    1. Here is a look at how the columns import using the first macro on this thread:
    /my:myFields
    /@xml:lang /my:Header/my:Date /my:Header/my:Location /my:Header/my:Part
    en-US 10/16/2013 AAA 558

    Once again, thank you for your wisdom. What will I need to place in row one to get the columns Date, Location, and Part to import for each file. I have used this code and cannot get it to work. Does the  /Header/ folder before the field name affect the import with this code?

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2013-11-14T01:48:56+00:00

    Hello,

    I used this code and it works perfectly. The issue I am having now is that when I use it for my 27,000 XML files, it appears that the XML Map is not the same for every file. These are infopath files and some of them are from different versions with extra fields or rich text boxes with addtional data being imported.

    Since I have so many files and further sorting and filtering of the data is becoming tedious, I need to define an XML map so that each column in the workbook is showing the same field. Right now some of the newer versions of the form have data in different order, so they are importing under a different column. All the files have the same field titles, so I am hoping that defining a xml map will solve this problem and they will all import in the same order and apend to the same map.

    I would like to define an xml map in row one

    ActiveWorkbook.XmlMaps.Add( _

            "C:\Users\tim\desktop\example.xml" _

            , "myFields").Name = "myFields_Map" 

    and then import an entire folder of xml files that will be append to that same xml map. Keeping one file per row.

    Thank you for your time and wisdom,

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-11-12T21:04:34+00:00

    Works perfectly!

    Thank you for your help

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments