|
xwb (Programmer) |
28 Apr 12 12:59 |
It will be something like this. Say your xml file looks like CODE<members>
<member>
<connectedto name="upper arm">body</connectedto>
</member>
</members> 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. CODE' 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")
end if
' Create an excel spreadsheet
objExcel.Workbooks.Add
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
WScript.echo file.name
objXML.load file.name
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
next
end if
next
excelFilenameFull = objDOS.ExpandEnvironmentStrings(excelFilename)
on error resume next
WScript.Echo "Deleting " & excelFilenameFull & " if it exists"
objFSO.DeleteFile excelFilenameFull
WScript.Echo "Saving as " & excelFilenameFull
objExcel.ActiveWorkbook.SaveAs excelFilenameFull
objExcel.ActiveWorkbook.Close
objExcel.Quit
WScript.Quit 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. |
|
Talk To Other Members
Read More Threads Like This One