Function to export App data in Excel is throwing VBscriptRunTimeError on TestCompleteVersion12
Hi Smartbear,
I upgraded my TestComplete from 8.7 to version 12 and once I run the VB script that was working perfectly on version 8.7, I am getting VB script runtime error on TC version 12.
The Function is written to copy the data from application and export it in to excel sheet, but as soon I run the script it throws VB runtime error while adding another workbook in excel sheet (i.e "Sheet2" in our script)
Error Line- Set dataSheet1=excelOpenFile.Sheets("Sheet2")
Function to dump the data in excel workbook is written as below. Please Help me with your comments.
Function ScheduledTests
Set testPane=Sys.FindChild("Uid", "extendedTreeView", 30).WPFObject("TreeViewItem", "", 2)
PropName=Array("ClrClassName","DataContext.Appearance.DescriptionStyle")
PropVal=Array("TextBlock","GroupHeader")
Tests=testPane.FindAllChildren(PropName,PropVal,40)
ArrayReverse Tests
Set msExcel=Sys.OleObject("Excel.Application")
Set objWorkbook = msExcel.Workbooks.Add()
objWorkbook.SaveAs("D:\ScheduledTests.xlsx")
'Call objWorkbook.SaveAs("D:\ScheduledTests.xlsx")
Set excelOpenFile=msExcel.Workbooks.Open("D:\ScheduledTests.xlsx")
Set dataSheet=excelOpenFile.Sheets("Sheet1")
Set dataSheet1=excelOpenFile.Sheets("Sheet2")
dataSheet.Cells(1, 1)=Tests(0).Text
k=1
For i=1 to UBound(Tests)
dataSheet.Cells(i+1, 1)=Tests(i).Text
Call Tests(i).DblClick
GroupTree1=Split(Tests(i).FullName,testPane.FullName+".")
GroupTree=Split(GroupTree1(1),".WPFObject(""Grid")
Set RootTestPane=testPane.FindChild("Name",GroupTree(0),1)
PropName=Array("ClrClassName","DataContext.Appearance.DescriptionStyle")
PropVal=Array("TextBlock","SubHeader")
SubTests=RootTestPane.FindAllChildren(PropName,PropVal,40)
ArrayReverse SubTests
For j=0 to UBound(SubTests)
dataSheet.Cells(i+1, j+2)=SubTests(j).Text
dataSheet1.Cells(k, 1)=Tests(i).Text&": "&SubTests(j).Text
k=k+1
Next
Call Tests(i).DblClick
Next
msExcel.ActiveWorkbook.Save
msExcel.ActiveWorkbook.close
msExcel.Quit
End Function
Function ArrayReverse( ByRef myArray )
Dim i, j, idxLast, idxHalf, strHolder
idxLast = UBound( myArray )
idxHalf = Int( idxLast / 2 )
For i = 0 To idxHalf
Set strHolder = myArray( i )
Set myArray(i)= myArray( idxLast - i )
Set myArray( idxLast - i ) = strHolder
Next
End Function
What is the error message text?
You say it throws and error, but haven't said what the error is! Kind of hard to fix a problem when you don't know exactly what it is! :smileywink:
Looking at your code ......
You declare an Excel object.
Then you add a workbook to it.
Then you save it. (Well, SaveAs, but same thing)
You then re-open it - which I don't think you need to do. It's already open.
Then you try and assign the two sheets.
So ...
Obvious thing - your Excel file does have more than one sheet right? And one of those worksheets is called "Sheet2"?
Default behaviour for Excel when you create a new workbook (manually) is that it is created with three sheets. Not sure if this is the case for all versions or if it's done through scripts?
Given that you save(as) the new workbook, then immediately re-open it, without closing the original file first, where does this leave Excel? Do it load a copy?, not load the second version?, I'm not sure what it'll do exactly. But you should be able to see.
