Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-29-2012, 03:27 PM
TallKewlOnez TallKewlOnez is offline Import Multiple XML Files Windows 7 64bit Import Multiple XML Files Office 2007
Novice
Import Multiple XML Files
 
Join Date: Mar 2012
Posts: 1
TallKewlOnez is on a distinguished road
Default 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...
Reply With Quote
  #2  
Old 04-09-2012, 05:19 PM
macropod's Avatar
macropod macropod is offline Import Multiple XML Files Windows 7 64bit Import Multiple XML Files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
excel vba xml import loop

Thread Tools
Display Modes


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
Import Multiple XML Files 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
Import Multiple XML Files 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft