Load the Data into a Table in the Workbook Depending on the data structure, we may need to do this several times. Repeat the process for the Email column as well. Choose the Expand option and press the OK button. In the query editor, click on the expand column toggle for the Name column which contains the Table elements. To expand these tables out in the power query editor, press the Transform Data button. If our data had a simple structure without any sub-fields, we could skip the query editor and press the Load button to directly load the data into the workbook. For example, the Name field contains a First and Last field for the first and last names of the person. Since the Name and Email fields in the XML data contain sub-fields we see Table listed underneath the column headings in the preview. Notice that it’s in a nice table format? That’s automatically done by power query. The Navigator window will now open and we can see a preview of the data from the XML file. This will open a file selection window and we can navigate to the location of the XML file and press the Import button.
Most websites have a sitemap.xml file which is an XML file of the website’s link structure. You can open any of the XML files in your text editor of choice to view the inner workings of an Excel file.Īnother common example can be found across the web.
zip and then exploring the files in the zip folder. Try it for yourself by taking any Excel file and changing the extension from. You don’t need to look very far to find an example, Excel files are really just a collection of XML files in a zip folder. Load the Data into a Table in the Workbook.T = myWB.Sheets("XML Data").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious). WB.Sheets(1).UsedRange.Copy myWB.Sheets("XML Data").Cells(t, "A") R = WB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).RowĬ = WB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlB圜olumns, SearchDirection:=xlPrevious).Column
Set WB = Workbooks.OpenXML(Filename:=myPath & myFile) MyPath = "C:\Folder 1\Folder 2\Received XML Data\" ' "" In case anybody is interested, I've used the following code: I've found a solution that seems to work. I thought this might be avoidable as I already have a function to create a list of the files that I'm interested in, but I can't seem to get my head around it!Īny help you can provide would be much appreciated. I would like to import the data from each of the files listed, row by row (starting in row 2), into another sheet, "XML Data".īecause of the variable number and name of the files, I don't want to hard-code the name of the files into the macro.
I have written a quick macro that interrogates the folder that I'll use to store the data, and returns the file names of the XML files in a list in Column A to sheet called, cleverly, "Files". The number and file name of the XML files that I'll recieve will be variable and difficult to predict (but it's safe to assume that the number is normally going to be in the thousands!), but they will all be deposited in the same folder until processing has been completed. Essentially, I going to be recieving a number of XML files each week, which I'll need to import and carry out some simple analysis upon. I have a problem with importing a number of XML Files into an Excel spreadsheet (Excel 2007) for data analysis purposes.