#1
|
|||
|
|||
Import Multiple XML Files
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... |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
excel vba xml import loop |
|
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 |