Retrieve data from multiple XML files

[ninjoe8]

Hi,I have a problem where i have multiple XML files on a remote server. Each XML file contains hundreds of tags but only one is of significance to this particular project and each of the files contain that specific tag. We have created a macro in excel to open each file remotely to pull that necessary value except it takes FOREVER because of the number of files and because it is being done from halfway across the country.We are unable to install excel on the remote server because of some copyright reason, else the macro would work in seconds. Anyone have any suggestions on how to simplify the process without installing anything on the remote machine?

Edited August 8, 2011 by ninjoe8

[aalbetski]

This looks like it could be done with a windows script host (WSH) file and an instance of an XML parser. As long as the appropriate permissions are in place, this would be a simple solution. I have attached a complete example (you will need to add the appropriate pieces to pass the filenames etc...)

// WSH Example writte in javascript// this should be saved with a .js extension WScript //H:CScriptvar xmlFreeThreadDocProgID 	=	"Msxml2.FreeThreadedDOMDocument.4.0";var xmlTmp =  new ActiveXObject(xmlFreeThreadDocProgID)xmlTmp.async=falsexmlTmp.setProperty('NewParser', true) var sFileName  = "\\\\xx.xxx.xx.xxx\\d$\\xxxxx\\xxxxx\\Data\\xxxx.xml";WScript.Echo(sFileName)if (xmlTmp.load(sFileName) == false) {	WScript.Echo("failed to load")	WScript.Quit(1);}var oNode = xmlTmp.selectSingleNode("//Codes/C[V='0001']/D");WScript.Echo(oNode.text);

I have, of course, xxxed out all references to my remote location and structure