Import Multiple XML Files

TallKewlOnez · #1 03-29-2012, 03:27 PM

Hello MsOfficeForums

I need to import multiple XML files into a single Excel Sheet programatically. I need to do this via code, because I need to append a column to the end of each row that indicates the file name that data came from.

So, I need help, 1) I tried using Workbooks.OpenXML , but that opens a new workbook . I need to loop through all xml files in a folder into 1 worksheet. and after each file update a column with the file name.

So I think this is what i need to do...-
1) I have one file imported already so I have the mapping already.

2) write code to get a file list to loop through
3) import a xml file from the file list, then update a column with that file name
4) go to the bottom of the sheet
5) repeat steps 3 & 4 until all files are imported

Thoughts,

many thanks...

macropod · #2 04-09-2012, 05:19 PM

Hi TallKewlOnez,

Try something along the following lines:

Code:

Sub ImportXMLData()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String
Dim xlWkBk As Workbook, xmlFile As Workbook, LastRow As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.xml", vbNormal)
Set xlWkBk = ThisWorkbook
While strFile <> ""
  LastRow = xlWkBk.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1
  Set xmlFile = Workbooks.OpenXML(Filename:=strFolder & "\" & strFile)
  xmlFile.Sheets(1).UsedRange.Copy
  xlWkBk.Sheets(1).Cells(LastRow, 1).Paste
  xmlFile.Close SaveChanges:=False
  strFile = Dir()
Wend
Set xmlFile = Nothing: Set xlWkBk = Nothing
Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

You might want to experiment with the XlXmlLoadOptions, which are optional parameters for the OpenXML function.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to import multiple text file data into word	mizankabir	Word VBA	7	09-15-2023 10:27 PM
convert multiple csv files to multiple excel files	mit	Excel	1	06-14-2011 10:15 AM
Multiple PPT files in different windows?	Caroline	PowerPoint	0	04-11-2011 09:40 AM
How to import .contact files into outlook 2007	eekie	Outlook	1	07-02-2010 05:40 AM
import multiple categories	stormin.norm	Outlook	0	01-30-2006 01:36 PM