28 Apr 12 12:59
It will be something like this. Say your xml file looks like
<connectedto name="upper arm">body</connectedto>
I know you said your xml files are only one line long - it doesn't really matter as xml only has new lines for readability
The code will look something like this. The main problem is the pathnames. If you use relative pathnames, excel's is relative to My Documents, FSO and WShell are relative to the current directory.
' This needs to be the full filepath.
const excelFilename = "%AllUsersProfile%\Documents\xlextract\collate.xls"
' This also needs to be the full path.
const xmlRepository = "%AllUsersProfile%\Documents\xlextract"
' Get all the things we need
set objFSO = CreateObject ("Scripting.FileSystemObject")
set objExcel = CreateObject ("Excel.Application")
set objXML = CreateObject ("MSXML2.DOMDocument.6.0")
set objDOS = CreateObject ("WScript.Shell")
if isNull(g_objXML ) then
' Try version 5. This is standard for XP
set objXML = CreateObject ("MSXML2.DOMDocument.5.0")
' Create an excel spreadsheet
objExcel.Cells(1,1) = "Name"
objExcel.Cells(1,2) = "Connected To"
' Get all the XML files in the current directory
xmlRepositoryFull = objDOS.ExpandEnvironmentStrings(xmlRepository)
WScript.echo "Examining files in " & xmlRepositoryFull
set objDir = objFSO.GetFolder(xmlRepositoryFull)
objXML.validateOnParse = true
objXML.async = false
row = 1
for each file in objDir.Files
if right(file.name, 4) = ".xml" then
set taglist = objXML.getElementsByTagName ("connectedto")
for each tag in taglist
name = tag.getAttribute ("name")
connect = tag.text
WScript.echo name + "=" + connect
row = row + 1
objExcel.Cells(row,1) = name
objExcel.Cells(row,2) = connect
excelFilenameFull = objDOS.ExpandEnvironmentStrings(excelFilename)
on error resume next
WScript.Echo "Deleting " & excelFilenameFull & " if it exists"
WScript.Echo "Saving as " & excelFilenameFull
If it falls over, remember to go into task manager and delete the excel process that the script created. If you don't you could end up with a lot of excel processes in the background.