Parsing XML File and Writing to Excel

Hello guys

I seem to be having an issue writing to an excel file in my script which parses an XML file and returns data which I want to be saved to a new excel file. I am confused because when I use code such as this:

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "VBS_Excel_Example"

'Set the save location
strExcelPath = "c:\Vbs_Excel_Example.xls"

'--------------------------------------------------------
'Populate the worksheet with data
'--------------------------------------------------------

'Add some titles to row 1
objSheet.Cells(1, 1).Value = "Name" 'Row 1 Column 1 (A)
objSheet.Cells(1, 2).Value = "Description" 'Row 1 Column 2 (B)
objSheet.Cells(1, 3).Value = "Something Else" 'Row 1 Column 3 (C)

'Add some data using a loop
For row = 2 to 10
	objSheet.Cells(row, 1).Value = "Item " & row & " Name"
	objSheet.Cells(row, 2).Value = "Item " & row & " Description"
	objSheet.Cells(row, 3).Value = "Item " & row & " Something Else"
Next

'--------------------------------------------------------
' Save the spreadsheet and close the workbook
'--------------------------------------------------------
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

'Quit Excel
objExcel.Application.Quit

'Clean Up
Set objSheet = Nothing
Set objExcel = Nothing

EVERYTHING WORKS FINE! It populates the Cells, Saves Changes and Successfully closes the file. Now when I try integrate that functionality within my code. I can write changes to the new file when I create it. But now when I want to add the results for the XML file parsed in my method. Weirdly enough It doesn't save those changes. Though I can see sometimes that the file was last modified around the time the script ended, when I try open the file it tells me that Excel has recovered a number of files matching the name of my excel file. Even when I do try open these, still no changes are persisted.

Ultimately what the script is meant to do is Run Test Cases specified in a file on a server, parse the results.xml file of that test case into errors, warnings, successes etc. And these values will be put in a new excel file with the format (ScriptName, Result, Passes, Fails, Warnings) in an excel file. Below is the process:

  1. Run Test Cases specified in an excel file on a server "qtpSchedule.xls"
  2. Get scriptNames and put them in an array
  3. Close the qtpSchedule.xls file
  4. Create a new file qtpScheduleResults.xls locally on running users machine
  5. Run the test case
  6. Parse the results from the results.xml file
  7. Save those results in the qtpScheduleResults.xls fie

I understand its a bit of code but if you can help id greatly appreciate it. Below is my code:

Option Explicit

Dim strFilePathS, strFilePathL, strFileNameS, strFileNameL, strFileS, strFileL, objExcel, objFSO, objSheet, intRows, intRow
Dim WSHShell
Dim strDate, strExecutionDate, App, resultsFolder, objWorkbook, scriptName
Dim i, j, k
Dim sResults, qtpTest, qtpResults, qtpAutoExportResults
Dim arrResult(6)
Dim sTime, eTime, timeInterval, passed, failed, warnings

'Call cleanup processes
Call cleanUpProcesses("'chrome.exe'") 'close all chrome processes
Call cleanUpProcesses("'UFT.exe'") 'close all UFT processes

'Excel variables - Server
'strFilePathS = "\\10.1.46.74\C:\AutomationVariables"
strFilePathS = "C:\AutomationVariables"
strFileNameS = "qtpSchedule.xls"
strFileS = strFilePathS & "\" & strFileNameS

'Excel variables - Local
Set WSHShell = CreateObject("WScript.Shell")
strFilePathL = WSHShell.SpecialFolders("Desktop")
strFileNameL = "qtpScheduleResults.xls"
strFileL = strFilePathL & "\" & strFileNameL

'Create timestamp and folder location
strDate = Replace(CStr(Now), "/", "")
strDate = Replace(strDate, " ", "")
strDate = Replace(strDate, ":", "")
strExecutionDate = CStr(strDate)  

'Create objects
Set App = CreateObject("QuickTest.Application")
Set objFSO   = CreateObject("Scripting.FileSystemObject")

'Determine results folder for this run
'resultsFolder = "\\10.1.46.74\C:\KWE\QTPScripts\NihilentScripts\Results\" & strExecutionDate 
resultsFolder = "C:\KWE\QTPScripts\NihilentScripts\Results\" & strExecutionDate 

'Check that the folder exists, exit script 
If Not objFSO.FolderExists(strFilePathS) Then
	WScript.Echo "Folder does not exist"
	WScript.Quit 1
Else
	'If file does not exist, exit script
	If Not objFSO.FileExists(strFileS) Then 
		WScript.Echo "File does not exist"
		WScript.Quit 1
		Else
			'Open Excel File on server
			Set objExcel = CreateObject("Excel.Application")
			Set objWorkbook = objExcel.Workbooks.Open(strFileS)
			Set objSheet = objWorkbook.Worksheets(1)
			intRows = objSheet.UsedRange.Rows.Count	
			'Declare scriptName array
			Dim arrScripts()
			ReDim Preserve arrScripts(intRows - 1)
			'Get script names 
			For i = 2 to intRows
				scriptName = Trim(objSheet.Cells(i, 1).Value)	
				'Add script names to scriptName array
				arrScripts(i - 2) = scriptName
			Next
			'Close Excel file on server
			objWorkbook.Close
			objExcel.Quit
			WScript.Echo "Scripts added to array and server file has been closed successfully"

			'Create new Excel file locally
			If objFSO.FileExists(strFileL) Then
				'Delete old file
				objFSO.DeleteFile(strFileL)
			End If
			'Create Excel object 
			Set objExcel = CreateObject("Excel.Application")
			Set objWorkbook = objExcel.Workbooks.Add
			'Bind to Worksheet
			Set objSheet = objWorkbook.Worksheets(1)
			objSheet.Name = "Results"
			'Populate the worksheet with data 
			objSheet.Cells(1,1).Value = "Name"
			objSheet.Cells(1,2).Value = "Result"
			objSheet.Cells(1,3).Value = "Start Time"
			objSheet.Cells(1,4).Value = "End Time"
			objSheet.Cells(1,5).Value = "Excecution Time/s"
			objSheet.Cells(1,6).Value = "Passed"
			objSheet.Cells(1,7).Value = "Failed"
			objSheet.Cells(1,8).Value = "Warnings"
			objSheet.Cells(1,9).Value = "Results"
			'Save file
			objWorkbook.SaveAs(strFileL)
			objWorkbook.Close
			objExcel.Quit
			WScript.Echo "Local file has been created successfully"
			'Add script names and process scripts
			'Open Excel File on local
			Set objExcel = CreateObject("Excel.Application")
			objExcel.Visible = True
			Set objWorkbook = objExcel.Workbooks.Open(strFileL)
			Set objSheet = objWorkbook.Worksheets(1)
			'intRows = objSheet.UsedRange.Rows.Count
			intRow = 2			
			For i = 0 To UBound(arrScripts) - 1
				'Add script name to cell
				objSheet.Cells(intRow,1) = arrScripts(i)
				intRow = intRow + 1
				objWorkbook.Save
				'Create a folder for the results
				resultsFolder = resultsFolder & " " & scriptName	
				objFSO.CreateFolder(resultsFolder)
				'Results variables
				sResults = resultsFolder & "\Report\Results.xml"
				'WScript.Echo "Results mapping created"
				'Pre-Test Configuration
				'If QTP is not open then open it
				If App.Launched <> True Then
					App.Launch
				End If				
				'Make the UFT application visible
				App.Visible = True				
				'Set UFT run options
				App.Options.Run.ImageCaptureForTestResults = "OnError"
				App.Options.Run.RunMode = "Fast"
				App.Options.Run.ViewResults = False				
				App.WindowState = "Maximized"
				App.ActivateView "ExpertView"				
				'Open the test in read-only mode
				On Error Resume Next
				App.Open "\\10.1.46.74\c$\KWE\QTPScripts\NihilentScripts\"& scriptName, True
				
				'set run settings for the test
				Set qtpTest = App.Test
				'Instruct UFT to perform next step when error occurs
				qtpTest.Settings.Run.OnError = "Stop"
				'qtpTest.Settings.Run.DisableSmartIdentification = True				
				'Create the Run Results Options object
				Set qtpResults = CreateObject("QuickTest.RunResultsOptions")				
				'Set the results location
				qtpResults.ResultsLocation = resultsFolder				
				'Set options for automatic export of run results at the end of every session
				Set qtpAutoExportResults = App.Options.Run.AutoExportReportConfig
				qtpAutoExportResults.AutoExportResults = True
				qtpAutoExportResults.StepDetailsReport = True
				qtpAutoExportResults.DataTableReport  = False
				qtpAutoExportResults.LogTrackingReport  = False
				qtpAutoExportResults.ScreenRecorderReport   = True
				qtpAutoExportResults.SystemMonitorReport    = False
				qtpAutoExportResults.ExportLocation    = resultsFolder
				qtpAutoExportResults.ExportForFailedRunsOnly = False
				qtpAutoExportResults.StepDetailsReportFormat = "Short"
				qtpAutoExportResults.StepDetailsReportType   = "HTML"						
				'Run the test
				qtpTest.Run qtpResults
				'Close test
				qtpTest.Close
				'Write the details of test execution				
				parseXMLResult(sResults)
				objSheet.Cells(i, 3) = sTime
				objSheet.Cells(i, 4).Value = eTime
				objSheet.Cells(i, 5).Value = timeInterval
				objSheet.Cells(i, 6).Value = passed
				objSheet.Cells(i, 7).Value = failed
				objSheet.Cells(i, 8).Value = warnings
				'Save changes to file
				objWorkbook.Save							
			Next
			'Close the workbook
			objWorkbook.Close
			WScript.Echo "Local file has been closed successfully"
			'Quit Excel
			objExcel.Quit
			WScript.Echo "Application has exited successfully"
	End If
End If









'-----------------------------------------Procedures--------------------------------------------
Sub cleanupProcesses(strProcess)
	Dim strComputer, objWMIService, objProcess, strQuery, colProcessList ', arrCounter

	strComputer = "."
	Set objWMIService   = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
	strQuery	=  "SELECT * FROM Win32_Process WHERE name = " & strProcess
	Set colProcessList = objWMIService.ExecQuery(strQuery)

	'Kill processes
	For Each objProcess in colProcessList
		'arrCounter = arrCounter + 1
	
		On Error Resume Next
		objProcess.Terminate()
	
		If Err.Description = "Not Found" Then 
			'Do Nothing
		End If
	Next 

	Set objWMIService   = Nothing
	Set colProcessList  = Nothing
End Sub

Sub memoryDump()
	'Memory dump
	Set objSheet = Nothing
	Set qtpTest = Nothing
	Set App = Nothing
	Set objExcel = Nothing
	Set objFSO = Nothing
End Sub

Sub parseXMLResult(xmlResultFile)
	Dim xmlDoc, strResult

	Set xmlDoc = CreateObject("Microsoft.XMLDOM")
	xmlDoc.Async = "False"
	xmlDoc.Load(xmlResultFile)

	'BEGIN FILTER
	Dim reportNodeList

	Set reportNodeList = xmlDoc.selectNodes _
	("/Report/*")

	Dim numNodes
	numNodes = reportNodeList.length

	Dim reportNode
	For each reportNode in reportNodeList	
		'Process General node
		If reportNode.nodeName = "General" Then
			Dim productName, productVer, os, host
			productName = reportNode.getAttribute("productName")
			productVer = reportNode.getAttribute("productVer") 
			os = reportNode.getAttribute("os")
			host = reportNode.getAttribute("host")
		End If
		'Process Doc node
		If reportNode.nodeName = "Doc" Then		
			Dim docNodeList
			Set docNodeList = reportNode.childNodes
			numNodes = docNodeList.length
			Dim docNode
			For each docNode in docNodeList
				'Process DName node
				If docNode.nodeName = "DName" Then
				
				End If
				'Process DIter node
				If docNode.nodeName = "DIter" Then
					'Get iteration id
					Dim iterID
					iterID = docNode.getAttribute("iterID")
					Dim diterNodeList
					Set diterNodeList = docNode.childNodes
					Dim diterNode
					For each diterNode in diterNodeList
						If diterNode.nodeName = "NodeArgs" Then
							'Get iteration status
							Dim iterationStatus
							iterationStatus = diterNode.getAttribute("status")
						End If
					Next
					Set diterNodeList = Nothing
				End If	
				'Process Summary node
				If docNode.nodeName = "Summary" Then
				'Get sTime, eTime, passed, failed, warnings
					sTime = Right(docNode.getAttribute("sTime"), 8)
					eTime = Right(docNode.getAttribute("eTime"), 8)
					passed = docNode.getAttribute("passed")
					failed = docNode.getAttribute("failed")
					warnings = docNode.getAttribute("warnings")
					If eTime > sTime Then
						timeInterval = DateDiff("s", sTime, eTime)
					Else	
						timeInterval = DateDiff("s", "00:00:00", eTime) + DateDiff("s", "23:59:59", sTime)
					End If
				End If		
			Next
			Set docNodeList = Nothing
		End If

	Next
	
	Set xmlDoc = Nothing
	Set reportNodeList = Nothing
	
	'parseXMLResult = strResult
End Sub

Also attached a Sample Results.xml file that is being processed:

<?xml version="1.0"?>
<!DOCTYPE Report
[
<!ELEMENT Report (General ,(Doc|BPT)) >
<!ATTLIST Report ver CDATA #REQUIRED tmZone CDATA #REQUIRED>

<!ELEMENT General ( DocLocation ) >
<!ATTLIST General productName CDATA #REQUIRED productVer CDATA #REQUIRED os CDATA #REQUIRED host CDATA #REQUIRED qcserver CDATA #IMPLIED qcproject CDATA #IMPLIED SolManSolutionId CDATA #IMPLIED SolManProjectId CDATA #IMPLIED SolManTestPlanId CDATA #IMPLIED SolManTestPackageId CDATA #IMPLIED SolManUserInfoData CDATA #IMPLIED  >

<!ELEMENT BPT (DName,Res,DVer?,TSet?,TInst?,NodeArgs,AdditionalInfo*,Doc*) >
<!ATTLIST BPT rID ID #REQUIRED >

<!ELEMENT Doc (DName,ConfName?,Res,DVer?,TSet?,TInst?,RunType?,DT?,AdditionalInfo*,Step*,DIter*,Step*,Action*,Doc*,Summary?,TestMaintenanceSummary*,NodeArgs?) >
<!ATTLIST Doc rID ID #REQUIRED type (Test|BC|BPTWrapperTest|Flow|Group|Action) "Test" productName CDATA #REQUIRED BCIter CDATA #IMPLIED >

<!ELEMENT RunType ( #PCDATA )>
<!ATTLIST RunType fmStep (False|True) "False" batch (False|True) "False" upDesc (False|True) "False" upChk (False|True) "False" upAS (False|True) "False">

<!ELEMENT DName ( #PCDATA ) >

<!ELEMENT ConfName ( #PCDATA ) >

<!ELEMENT Res ( #PCDATA ) >

<!ELEMENT AdditionalInfo (AdditionalDataName,AdditionalDataValue ) >

<!ELEMENT AdditionalDataName ( #PCDATA ) >

<!ELEMENT AdditionalDataValue ( #PCDATA ) >

<!ELEMENT DVer ( #PCDATA ) >

<!ELEMENT TSet ( #PCDATA ) >

<!ELEMENT TInst ( #PCDATA ) >

<!ELEMENT DIter (Step*,Action+,Summary?,NodeArgs)>
<!ATTLIST DIter rID ID #REQUIRED iterID CDATA #REQUIRED>

<!ELEMENT DocLocation ( #PCDATA )>

<!ELEMENT Action (AName,AIter*,(Step|HtmlStep|Action)*, Summary,ActionMaintenanceSummary*, NodeArgs ) >
<!ATTLIST Action rID ID #REQUIRED>

<!ELEMENT AIter ((Step|HtmlStep|Action)*,DataMapping?, Summary?,NodeArgs) >
<!ATTLIST AIter rID ID #REQUIRED iterID CDATA #REQUIRED isCountable (False|True) "False" >

<!ELEMENT AName ( #PCDATA ) >

<!ELEMENT TestMaintenanceSummary (ActionMaintenanceSummary*) >
<!ATTLIST TestMaintenanceSummary ObjectsAdded CDATA #REQUIRED ObjectsUpdated CDATA #REQUIRED StepsUpdated CDATA #REQUIRED StepsComments CDATA #REQUIRED><!ELEMENT ActionMaintenanceSummary (ObjectChange* ) >
<!ATTLIST ActionMaintenanceSummary Action CDATA #REQUIRED Objects CDATA #REQUIRED Updated CDATA #REQUIRED LinesTotal CDATA #REQUIRED Added CDATA #REQUIRED LinesUpdated CDATA #REQUIRED>

<!ELEMENT ObjectChange  (Hierarchy, PropertyChangeList* ) >
<!ATTLIST ObjectChange Operation CDATA #IMPLIED OriginalRepository CDATA #IMPLIED>

<!ELEMENT PropertyChangeList (PropertyDef*) >

<!ELEMENT PropertyDef (OriginalValue, NewValue) >
<!ATTLIST PropertyDef PropName CDATA #REQUIRED OriginRegularExpression (True|False) "False" NewRegularExpression (True|False) "False" AddedProperty (True|False) "False" >

<!ELEMENT OriginalValue ( #PCDATA )>

<!ELEMENT NewValue ( #PCDATA )>

<!ELEMENT Hierarchy  (ObjectName, Hierarchy*) >
<!ATTLIST Hierarchy MicClass CDATA #REQUIRED>

<!ELEMENT ObjectName  ( #PCDATA ) >

<!ELEMENT Step (Obj,Details,Time,TimeTick* ,(Step|HtmlStep|Doc)*, DataMapping?, NodeArgs) >
<!ATTLIST Step rID ID #REQUIRED retval CDATA #IMPLIED>

<!ELEMENT HtmlStep (HTML,TimeTick*,(Step|HtmlStep|Doc)*,NodeArgs) >
<!ATTLIST HtmlStep rID ID #REQUIRED >

<!ELEMENT Obj ( #PCDATA ) >
<!ATTLIST Obj plainTxt (False|True) "True">

<!ELEMENT Details ( #PCDATA ) >
<!ATTLIST Details plainTxt (False|True) "True">

<!ELEMENT Time ( #PCDATA ) >

<!ELEMENT HTML ( #PCDATA ) >

<!ELEMENT Disp ( #PCDATA ) >

<!ELEMENT TimeTick ( #PCDATA ) >

<!ELEMENT DataMapping ( DataMappedItem* )>
<!ELEMENT DataMappedItem EMPTY >
<!ATTLIST DataMappedItem dsName CDATA #REQUIRED >
<!ATTLIST DataMappedItem rowId CDATA #REQUIRED >

<!ELEMENT NodeArgs (Disp,TopPane?,BtmPane?,ExtendedInfo? )>
<!ATTLIST NodeArgs eType CDATA #REQUIRED icon CDATA #REQUIRED nRep CDATA #REQUIRED UserNote CDATA #IMPLIED filter (False|True) "True">
<!ATTLIST NodeArgs status (Passed|Failed|Done|Warning|Information) "Done">
<!ATTLIST NodeArgs iconSel CDATA #IMPLIED nType CDATA #IMPLIED MovieMarker CDATA "">
<!ATTLIST NodeArgs Source CDATA #IMPLIED SourceLine CDATA #IMPLIED StepFlowItemKey CDATA #IMPLIED >

<!ELEMENT TopPane (Path)>

<!ELEMENT BtmPane ( (Path|WR)?,ASHilite?)>
<!ATTLIST BtmPane vType CDATA "HTML">

<!ELEMENT Path ( #PCDATA ) >

<!ELEMENT ASHilite ( #PCDATA ) >

<!ELEMENT WR ( #PCDATA ) >

<!ELEMENT ExtendedInfo ( StepData ) >

<!ATTLIST ExtendedInfo StepType CDATA #REQUIRED>
<!ELEMENT DT (NodeArgs) >
<!ATTLIST DT rID ID #REQUIRED>

<!ELEMENT Summary (Param*)>
<!ATTLIST Summary sTime CDATA #IMPLIED eTime CDATA #IMPLIED passed CDATA #IMPLIED failed CDATA #IMPLIED warnings CDATA #IMPLIED retval CDATA #IMPLIED stopped (False|True) "False" >

<!ELEMENT Param (ParamName,ParamVal)+ >
<!ATTLIST Param paramInOut (In|Out) "In">

<!ELEMENT ParamName ( #PCDATA ) >

<!ELEMENT ParamVal ( #PCDATA ) >

]
>
<Report ver="2.0" tmZone="South Africa Standard Time">
<General productName="HP Unified Functional Testing" productVer="12.02" os="Windows 8" host="NELSON-TETRAD"><DocLocation><![CDATA[\\10.1.46.74\c$\KWE\QTPScripts\NihilentScripts\TST M3 146 Display the current date]]></DocLocation></General>
<Doc rID="T1"   productName= "HP Unified Functional Testing"  >
<DName><![CDATA[TST M3 146 Display the current date]]></DName>
<Res><![CDATA[2015-06-29041203PM TST M3 146 Display the current date]]></Res>
<DT rID="T2">
<NodeArgs eType="Table" icon="2" nRep="4" filter="False" >
<Disp><![CDATA[Run-Time Data Table]]></Disp>
<BtmPane vType="Table" >
<Path><![CDATA[Default.xls]]></Path>
</BtmPane>
</NodeArgs>
</DT>
<DIter rID="T3" iterID="1" >
<Action rID="T4">
<AName><![CDATA[TST M3 146 Display the current date]]></AName>
<Action rID="T5">
<AName><![CDATA[TST LoginAuto [TST LoginAuto]]]></AName>
<Step rID="T6">
<Obj plainTxt="False" ><![CDATA[Siebel Communications]]></Obj>
<Details plainTxt="False" ><![CDATA[Browser]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:30]]></Time>
<TimeTick>1435587150</TimeTick>
<Step rID="T7">
<Obj plainTxt="False" ><![CDATA[Siebel Communications]]></Obj>
<Details plainTxt="False" ><![CDATA[Page]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:30]]></Time>
<TimeTick>1435587150</TimeTick>
<Step rID="T8">
<Obj plainTxt="False" ><![CDATA[icon_sitemap_1.Exist]]></Obj>
<Details plainTxt="False" ><![CDATA["Object exists"]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:30]]></Time>
<TimeTick>1435587150</TimeTick>
<NodeArgs eType="Replay" icon="8" nRep="12" Source="TST LoginAuto [TST LoginAuto]" SourceLine="-1" >
<Disp><![CDATA[icon_sitemap_1.Exist]]></Disp>
</NodeArgs>
</Step>
<NodeArgs eType="Context" icon="7" nRep="11" Source="TST LoginAuto [TST LoginAuto]" SourceLine="-1" >
<Disp><![CDATA[Siebel Communications]]></Disp>
</NodeArgs>
</Step>
<Step rID="T9">
<Obj plainTxt="False" ><![CDATA[WinObject_Browser_URLBar.Click]]></Obj>
<Details plainTxt="False" ><![CDATA[]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:51]]></Time>
<TimeTick>1435587171</TimeTick>
<NodeArgs eType="Replay" icon="9" nRep="15" status="Warning" Source="TST LoginAuto [TST LoginAuto]" SourceLine="-1" >
<Disp><![CDATA[WinObject_Browser_URLBar.Click]]></Disp>
</NodeArgs>
</Step>
<Step rID="T10">
<Obj plainTxt="False" ><![CDATA[WinObject_Browser_URLBar]]></Obj>
<Details plainTxt="False" ><![CDATA[Cannot identify the object "WinObject_Browser_URLBar" (of class WinObject). Verify that this object's properties match an object currently displayed in your application.]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:51]]></Time>
<TimeTick>1435587171</TimeTick>
<Step rID="T11">
<Obj plainTxt="False" ><![CDATA[WinObject_Browser_URLBar]]></Obj>
<Details plainTxt="False" ><![CDATA[Object's physical description:<br>regexpwndclass = Chrome_OmniboxView<br>]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:51]]></Time>
<TimeTick>1435587171</TimeTick>
<NodeArgs eType="Replay" icon="10" nRep="17" status="Warning" nType="Description" Source="TST LoginAuto [TST LoginAuto]" SourceLine="-1" >
<Disp><![CDATA[WinObject_Browser_URLBar]]></Disp>
</NodeArgs>
</Step>
<NodeArgs eType="Replay" icon="9" nRep="16" status="Failed" nType="Run Error" Source="TST LoginAuto [TST LoginAuto]" SourceLine="-1" >
<Disp><![CDATA[WinObject_Browser_URLBar]]></Disp>
</NodeArgs>
</Step>
<NodeArgs eType="Context" icon="6" nRep="10" status="Failed" Source="TST LoginAuto [TST LoginAuto]" SourceLine="-1" >
<Disp><![CDATA[Siebel Communications]]></Disp>
</NodeArgs>
</Step>
<Step rID="T12">
<Obj plainTxt="False" ><![CDATA[Run Error]]></Obj>
<Details plainTxt="False" ><![CDATA[Cannot identify the object &quot;WinObject_Browser_URLBar&quot; (of class WinObject). Verify that this object's properties match an object currently displayed in your application.<BR>Line (35): &quot;Browser(&quot;Siebel Communications&quot;).WinObject(&quot;WinObject_Browser_URLBar&quot;).Click&quot;. ]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:51]]></Time>
<TimeTick>1435587171</TimeTick>
<NodeArgs eType="Replay" icon="10" nRep="18" status="Failed" Source="TST LoginAuto [TST LoginAuto]" SourceLine="34" >
<Disp><![CDATA[Run Error]]></Disp>
</NodeArgs>
</Step>
<Step rID="T13">
<Obj plainTxt="False" ><![CDATA[Stop Run]]></Obj>
<Details plainTxt="False" ><![CDATA[Run stopped by user.]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:51]]></Time>
<TimeTick>1435587171</TimeTick>
<NodeArgs eType="General" icon="11" nRep="19" >
<Disp><![CDATA[Stop Run]]></Disp>
</NodeArgs>
</Step>
<Summary sTime="2015-06-29 - 16:12:30" eTime="2015-06-29 - 16:12:51" passed="0" failed="2" warnings="2" ><Param ><ParamName><![CDATA[userName]]></ParamName><ParamVal><![CDATA[TESTENGM]]></ParamVal>
</Param>
<Param ><ParamName><![CDATA[strProcess]]></ParamName><ParamVal><![CDATA[Login]]></ParamVal>
</Param>
</Summary>
<NodeArgs eType="StartAction" icon="5" nRep="8" status="Failed" >
<Disp><![CDATA[TST LoginAuto [TST LoginAuto] Summary]]></Disp>
</NodeArgs>
</Action>
<Step rID="T14">
<Obj plainTxt="False" ><![CDATA[Stop Run]]></Obj>
<Details plainTxt="False" ><![CDATA[Run stopped by user.]]></Details>
<Time><![CDATA[2015-06-29 - 16:12:51]]></Time>
<TimeTick>1435587171</TimeTick>
<NodeArgs eType="General" icon="11" nRep="21" >
<Disp><![CDATA[Stop Run]]></Disp>
</NodeArgs>
</Step>
<Summary sTime="2015-06-29 - 16:12:28" eTime="2015-06-29 - 16:12:51" passed="0" failed="0" warnings="0" ></Summary>
<NodeArgs eType="StartAction" icon="4" nRep="7" status="Failed" >
<Disp><![CDATA[TST M3 146 Display the current date Summary]]></Disp>
</NodeArgs>
</Action>
<NodeArgs eType="StartIteration" icon="3" nRep="6" status="Failed" >
<Disp><![CDATA[TST M3 146 Display the current date Iteration 1 (Row 1)]]></Disp>
</NodeArgs>
</DIter>
<Summary sTime="2015-06-29 - 16:12:28" eTime="2015-06-29 - 16:12:51" passed="0" failed="2" warnings="2" ></Summary>
<NodeArgs eType="StartTest" icon="1" nRep="3" status="Failed" >
<Disp><![CDATA[Test TST M3 146 Display the current date Summary]]></Disp>
</NodeArgs>
</Doc>
</Report>

Regards


June 30th, 2015 4:16am

You need to ask a specific question.  We cannot provide general consulting services to review your code. YOu mmust provide a simple example of your issue.

If you cannot find the issue then you will need to use a debugger or learn to use trace statements to discover your issue.

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 7:12am

My question is basically then how to write values in an excel sheet. Given 4 or so variables returned from an method.

'Create new Excel file locally
			If objFSO.FileExists(strFileL) Then
				'Delete old file
				objFSO.DeleteFile(strFileL)
			End If
			'Create Excel object 
			Set objExcel = CreateObject("Excel.Application")
			Set objWorkbook = objExcel.Workbooks.Add
			'Bind to Worksheet
			Set objSheet = objWorkbook.Worksheets(1)
			objSheet.Name = "Results"
			'Populate the worksheet with data 
			objSheet.Cells(1,1).Value = "Name"
			objSheet.Cells(1,2).Value = "Result"
			objSheet.Cells(1,3).Value = "Start Time"
			objSheet.Cells(1,4).Value = "End Time"
			objSheet.Cells(1,5).Value = "Excecution Time/s"
			objSheet.Cells(1,6).Value = "Passed"
			objSheet.Cells(1,7).Value = "Failed"
			objSheet.Cells(1,8).Value = "Warnings"
			objSheet.Cells(1,9).Value = "Results"
			'Save file
			objWorkbook.SaveAs(strFileL)
			objWorkbook.Close
			objExcel.Quit
			WScript.Echo "Local file has been created successfully"
			'Add script names and process scripts
			'Open Excel File on local
			Set objExcel = CreateObject("Excel.Application")
			objExcel.Visible = True
			Set objWorkbook = objExcel.Workbooks.Open(strFileL)
			Set objSheet = objWorkbook.Worksheets(1)
intRow = 2			
			For i = 0 To UBound(arrScripts) - 1
				'Add script name to cell
				objSheet.Cells(intRow,1) = arrScripts(i)
				intRow = intRow + 1
				objWorkbook.Save
'Write the details of test execution				
				parseXMLResult(sResults)
				objSheet.Cells(i, 3) = sTime
				objSheet.Cells(i, 4).Value = eTime
				objSheet.Cells(i, 5).Value = timeInterval
				objSheet.Cells(i, 6).Value = passed
				objSheet.Cells(i, 7).Value = failed
				objSheet.Cells(i, 8).Value = warnings
				'Save changes to file
				objWorkbook.Save
Next
			'Close the workbook
			objWorkbook.Close
			WScript.Echo "Local file has been closed successfully"
			'Quit Excel
			objExcel.Quit
			WScript.Echo "Application has exited successfully"

June 30th, 2015 7:21am

Hi Nelson,

In my opinion you are going too fast. Try running only the parsing part first, similarly all the parts in smaller sections. Then later integrate or use it as separate scripts.

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 7:39am

Hey Satyajit

Thanks for the advice. I am completely new to scripting especially using VBScript. That said how would I go about doing this. Ideally I would like to just open on script or something like that that handles the entire process. It should be lightweight and require no input from the users side. Perhaps your suggestion about splitting everything could work.

I was thinking something like

  1. Script to run the tests and generate xml file for parsing
  2. Script to parse xml file and return results
  3. Script to create new excel file and write results to file

However all these need to be controlled by a loop as the file im reading from has a number of tests I want to run

June 30th, 2015 8:09am

If you are new to scripting then don't waste you time on VBScript.  It is fast becoming obsolete and requires a lot of effort compared to PowerShell.

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 8:26am

THe XML also has a very screwy schema and will not walk easily.

In PowerShell:

PS C:\scripts> $xml=[xml](cat report.xml)
PS C:\scripts> $xml.report[1].Doc.diter.action.action.Step


rID      : T6
Obj      : Obj
Details  : Details
Time     : Time
TimeTick : 1435587150
Step     : {Step, Step, Step}
NodeArgs : NodeArgs

rID      : T12
Obj      : Obj
Details  : Details
Time     : Time
TimeTick : 1435587171
NodeArgs : NodeArgs

rID      : T13
Obj      : Obj
Details  : Details
Time     : Time
TimeTick : 1435587171
NodeArgs : NodeArgs

This can be directly exported to Excel from PowerShell.

June 30th, 2015 8:47am

PS C:\scripts> $xml.report[1].General productName : HP Unified Functional Testing productVer : 12.02 os : Windows 8 host : NELSON-TETRAD DocLocation : DocLocation

#or this way
PS C:\scripts> $product=$xml.report[1].General.productName
PS C:\scripts> $version=$xml.report[1].General.productVersion
PS C:\scripts> $os=$xml.report[1].General.os

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 8:51am

My question is basically then how to write values in an excel sheet. Given 4 or so variables returned from an method.

  


A function can only return one variable. It can also return an object or an array.  You  are not returning anything from your function.

June 30th, 2015 9:00am

#To store values from XML into Excel you only need to do this
$worksheet.Cells.Item($i, 1) = $xml.report[1].General.productName
$worksheet.Cells.Item($i, 2) = $xml.report[1].General.productVersion
$worksheet.Cells.Item($i, 3) = $xml.report[1].General.os

$xl.SaveAs('c:\mybook.xlsx')

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 9:05am

Hey

What Im doing here is reading the XML file and extracting particular information from it. Mainly, StartTime, EndTime, TimeInterval, StepsPassed, StepsFailed, StepsWarnings. My function returns a single string which is then split into an array:

Function parseXMLResult(strResultsPath) Dim xmlDoc, strResult Set xmlDoc = CreateObject("Microsoft.XMLDOM") xmlDoc.Async = "False" xmlDoc.Load(strResultsPath) 'BEGIN FILTER Dim reportNodeList Set reportNodeList = xmlDoc.selectNodes _ ("/Report/*") Dim numNodes numNodes = reportNodeList.length Dim reportNode For each reportNode in reportNodeList 'Process General node If reportNode.nodeName = "General" Then Dim productName, productVer, os, host productName = reportNode.getAttribute("productName") productVer = reportNode.getAttribute("productVer") os = reportNode.getAttribute("os") host = reportNode.getAttribute("host") End If 'Process Doc node If reportNode.nodeName = "Doc" Then Dim docNodeList Set docNodeList = reportNode.childNodes numNodes = docNodeList.length Dim docNode For each docNode in docNodeList 'Process DName node If docNode.nodeName = "DName" Then End If 'Process DIter node If docNode.nodeName = "DIter" Then 'Get iteration id Dim iterID iterID = docNode.getAttribute("iterID") Dim diterNodeList Set diterNodeList = docNode.childNodes Dim diterNode For each diterNode in diterNodeList If diterNode.nodeName = "NodeArgs" Then 'Get iteration status Dim iterationStatus iterationStatus = diterNode.getAttribute("status") End If Next Set diterNodeList = Nothing End If 'Process Summary node If docNode.nodeName = "Summary" Then 'Get sTime, eTime, passed, failed, warnings Dim sTime, eTime, passed, failed, warnings, timeInterval sTime = Right(docNode.getAttribute("sTime"), 8) eTime = Right(docNode.getAttribute("eTime"), 8) passed = docNode.getAttribute("passed") failed = docNode.getAttribute("failed") warnings = docNode.getAttribute("warnings") If eTime > sTime Then timeInterval = DateDiff("s", sTime, eTime) Else timeInterval = DateDiff("s", "00:00:00", eTime) + DateDiff("s", "23:59:59", sTime) End If strResult = sTime & "|" & eTime & "|" & timeInterval & "|" & passed & "|" & failed & "|" & warnings End If Next Set docNodeList = Nothing End If Next Set xmlDoc = Nothing Set reportNodeList = Nothing parseXMLResult = strResult End Function

arrResult = Split(parseXMLResult(sResults), "|")

Now for each value at array[index] I want to write that in an excel spreadsheet. So First Row would have the Titles/Headings, then underneath on the specified cell it will put that array value.

June 30th, 2015 9:14am

What is the issue.  Just write the array values to the cells.  What is your error?  What is it that you do not understand?

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 9:39am

You also need to remove this "On Error Resume Next"

It will mask all errors so you won't know why things are failing.

June 30th, 2015 9:42am

Thats the thing! When I use for example:

objSheet.Cells(i, 4).Value = sTime

objWorkbook.Save


It doesnt save the values of the array into the excel sheet. Everywhere before the loop that same methodology would work, and save the file. However as soon as I start looping and I parse the xml etc It doesnt save it anymore. I get no errors whatsoever and when I do try open the output file, all I get is the prompt to recover the file, which makes no difference because even the opened one and the recovered one are both blank. Funny enough the last modified date corresponds to the time the script ends approximately, so Im assuming something does happen to the file, however nothing is reflected.

I tried writing to a text file and that was successful so there isnt an issue with the parsing of the xml but rather saving onto the specified cells in excel.

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 10:16am

"sTime" is not an array.  Why would you think it would work? Your array is "arrResult"

June 30th, 2015 10:19am

No, sTime is the first value of my arrResult, Im looping the arrResult and trying to add the values within the array onto cells within Excel. So sTime(StartTime) is the first value in the array. I have a variable sTime which takes that value hence why I am using that code. I was avoiding posting more code over and over again
Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 10:22am

There is no way for us to know that.

Why are you overcomplicating this?

objSheet.Cells(i, 4).Value =arrResult(1)

June 30th, 2015 10:24am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics