Import XML into worksheet not workbook

Question

I am trying to have Excel import 200 .xml files into separate worksheets. I have some code that creates new worksheets, and that works. However I can't seem to find the proper code to import an .xml file into a worksheet rather then a workbook. I was hoping:

Workbooks.Worksheets.OpenXML

would work, but alas.

Is there a way to do this, or should I import them into a new workbook, copy that worksheet, close the newly created workbook without saving and so on?

For those interested: I found the quick and dirty method on this site as well: How to fetch data from XML file to Excel sheet using VBA

"should I import them into a new workbook, copy that worksheet" Sure, why not? Have you tried? — Jean-François Corbett
Because that is a lot of processing that when not necessary can be avoided to speed up things. — Luuklag
And why build a dirty code when there is (possibly) a clean solution? — Luuklag

Luuklag Luuklag · Accepted Answer · 2015-04-23T13:14:59

Here is the dirty workaround. This works for me as for now my number of lookups remains low. However I still wonder if there is a neat way to do this.

How to fetch data from XML file to Excel sheet using VBA

Import XML into worksheet not workbook

1 Answers