How to import multiple xml file into excel

For the correct sheet, I think it is just modifying

wb1.Sheets(1).UsedRange.Copy wb.ActiveSheet.Range("A1")

to

wb1.Sheets(2).UsedRange.Copy wb.ActiveSheet.Range("A1")

or perhaps more reliably

wb1.Sheets("Posted Transactions").UsedRange.Copy wb.ActiveSheet.Range("A1")

The destination workbook would only have one sheet. The table there being appended.

Typical instance of an imported table will only be 20 rows but as much as 200.

(Don't think I need to test how ridiculous having 300+ worksheets in a single workbook would be. An instance for almost every day of the year except Sundays.)

tasosK

So the idea is to have each sheet inside of a workbook contain a different .xml file.  Is that correct?

Since I have hundreds of .xml files is there a way to modify the macro script you wrote that I was using before to import the .xml files each into a different sheet as opposed to one right after the other in one sheet?   Will these sheets be exportable as .xml files?

Thank you!

Jonathan

Hi,

try to load only one xml file in a new sheet

Sub Load_XML_in_a_new_Sht()

'Mar 13, 2015

Dim wb As Workbook, wb1 As Workbook

Set wb = ThisWorkbook

Dim sPath

sPath = "c:\folder1\folder2" '<< change path, change

Dim sFile

sFile = "sample xml.xml" '<< xml file name, change

Application.ScreenUpdating = False

Set wb1 = Workbooks.OpenXML(Filename:=sPath & sFile, LoadOption:=xlXmlLoadImportToList)

wb.Sheets.Add

wb1.Sheets(1).UsedRange.Copy wb.ActiveSheet.Range("A1")

wb1.Close False

wb.Save

Application.ScreenUpdating = True

End Sub

If the XML (XML Spreadsheet 2003) data defines multiple worksheets, can a specific worksheet be imported other than the first one. (The sourcer actually sends the file with an XLS extension :( ! )

(Goal is to import from multiple files to single file, with appending enabled for future files. Desired worksheet is named "Posted Transactions" and also "always" appears as second worksheet .