jbarrett5g
New Contributor
Joined 8 years ago
User Profile
User Widgets
Contributions
Re: How to get an object reference to an existing instance of Excel?
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 Function4KViews1like0CommentsHow to get an object reference to an existing instance of Excel?
Hello, our AUT allows users to open a spreadsheet by clicking on a button/link. I'm trying to automate the validation of this function. So after the button is selected, I want to get an object reference to the Excel instance (that should be created) and validate that the correct ActiveWorkbook and ActiveSheet have, in fact, been loaded. The problem I'm having is that when I establish the object reference to Excel, it creates another Excel instance (that I can see in Task Manager), instead of referencing the already existing instance. The script validation fails at this point since the expected workbook and sheet are not loaded into the new (second) instance. I'm running on a Win7 64 bit OS with Excel 2016 (32 bit). TestComplete is 12.0.122.7. Here's the VBscript code I'm trying to use... Function WorkSheetActive2(sWorkBookName, sWorkSheetName, bCloseWorkBook) bSheetActive = True sMsg = "Expected Workbook and Worksheet [" &sWorkBookName& " : " &sWorkSheetName& "] are active." 'None of the following capture the existing Excel instance... Set Excel = Sys.OleObject("Excel.Application") 'Set Excel = GetObject("","Excel.Application") 'Set Excel = GetObject("Excel.Application") 'Set Excel = GetObject("C:\Temp\TestFile1.xls","Excel.Application") If (Excel.Workbooks.Count > 0) Then If (Ucase(Excel.ActiveWorkbook.Name) = Ucase(sWorkBookName)) then If (Ucase(Excel.ActiveSheet.Name) <> Ucase(sWorkSheetName)) then bSheetActive = False End If Else bSheetActive = False End If Else bSheetActive = False End If Call LogAction (bSheetActive, sMsg) If (bSheetActive = True) and (bCloseWorkBook = True) Then Call LogAction (bSheetActive, "Closing active workbook.") Excel.ActiveWorkbook.Close End If WorkSheetActive = bSheetActive End FunctionSolved4.5KViews0likes4Comments