This blog post continues from the previous one called Import XML File to Excel.

Now, assume we have several XML files coming in each day and we want to set up an easy procedure to bring them into Excel. We know the XML format is going to be same each time.

We have made some changes to the XML file from the previous post as shown below. We’ve added more columns. We’ve changed the root from BookInfo to Books. We’ve put in some fake data as placeholders. Here’s our file. We are saving it in a folder and naming it this way: C:\XML2XL\BookDataLayout.xml.

<?xml version='1.0'?>
<Books>
	<Book>
		<ISBN>isbnhere</ISBN>
		<Title>title</Title>
		<Author>authorhere</Author>
		<Quantity>1</Quantity>
		<YearPublished>2017</YearPublished>
		<NumberPages>345</NumberPages>
		<OtherColumns>morestuff</OtherColumns>
	</Book>
</Books>

We have made changes to our routine to reflect the new file name and location. We have also renamed our subroutine to Create_XSD2_Book().

Sub Create_XSD2_Book()
   Dim StrMyXml As String, MyMap As XmlMap
   Dim StrMySchema As String
   ' BookDataLayout.xml is the file created in section one of this topic.
   StrMyXml = "C:\XML2XL\BookDataLayout.xml"

   ' Turn off async loading.
   Application.DisplayAlerts = False
   ' Add the string to the XmlMaps collection.
   Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
   Application.DisplayAlerts = True

   ' Create an empty file and output the schema.
   StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
   Open "C:\XML2XL\BookDataLayout2.xsd" For Output As #1
   Print #1, StrMySchema
   Close #1
End Sub

Saving Our Code

In the previous post we did not save our Excel workbook with the code in it. This time we will save it but the Excel file type must enable macros. It must have a file type of xlsm.

Importing Many XML Files

We can now import many different (same format, new data) XML files over time into the same Excel Sheet. Previous data in that table will be overwritten.