Hello Alex,
Thank you for the link to https://smartbear-cc.force.com/portal/KbArticleViewer?name=Get-COM-reference-for-a-running-Excel-instance&sp=testcomplete .
This is exactly what I was looking for!
After following the instructions to add the "EXCEL7" window class name to my TestComplete project MSAA settings, I was then able to view the NativeObject property that allowed me to access the COM reference to the Excel instance. I could then check if the appropriate workbook and sheet had been loaded...
Here's the function that I'm using...
'Verifies that the expected Excel workbook and worksheet are active.
'Input: sWorkBookName = Expected workbook name that is active (e.g. "TestFile1.xls")
' sWorkSheetName = Expected worksheet name that is active (e.g. "Execution")
' bCloseWorkBook = True to close the workbook after validation of active book/sheet.
' False to leave the workbook open after validation.
'Returns TRUE if the expected workbook sheet is active. Otherwise returns false.
Function WorkSheetActive(sWorkBookName, sWorkSheetName, bCloseWorkBook)
Dim objExcel, objWin, objApp
dim arrPropNames(2)
dim arrPropValues(2)
bSheetActive = False
If sys.Process("EXCEL").Exists Then
set objExcel = sys.Process("EXCEL")
arrPropNames(0) = "ObjectType"
arrPropValues(0) = "Panel"
arrPropNames(1) = "WndClass"
arrPropValues(1) = "EXCEL7"
arrPropNames(2) = "WndCaption"
arrPropValues(2) = sWorkBookName& "*"
Set objWin = objExcel.FindChild(arrPropNames,arrPropValues,100)
If objWin.Exists = True Then
If objWin.Focused = True Then
Set objApp = objWin.NativeObject.Application
If (objApp.Workbooks.Count > 0) Then
If (Ucase(objApp.ActiveWorkbook.Name) = Ucase(sWorkBookName)) then
If (Ucase(objApp.ActiveSheet.Name) = Ucase(sWorkSheetName)) then
bSheetActive = True
sMsg = "Expected Workbook and Worksheet [" &sWorkBookName& " : " &sWorkSheetName& "] are active."
Else
sMsg = "Expected Worksheet [" &sWorkSheetName& "] is NOT active!"
End If
Else
sMsg = "Expected WorkBook [" &sWorkBookName& "] is NOT active!"
End If
Else
sMsg = "No workbooks are open in Excel!"
End If
Else
sMsg = "Excel is not the active window! (It should be after running the open spreadsheet task!)"
End If
Else
sMsg = "Excel workbook [" &sWorkBookName& "] not found!"
End If
Else
sMsg = "Excel process not found!"
End If
Call LogAction (bSheetActive, sMsg)
If (bSheetActive = True) and (bCloseWorkBook = True) Then
Call LogAction (bSheetActive, "Closing active workbook.")
objApp.ActiveWorkbook.Close
End If
set objApp = Nothing
set objWin = Nothing
set objExcel = Nothing
WorkSheetActive = bSheetActive
End Function
