Forum Discussion

jbarrett5g's avatar
jbarrett5g
New Contributor
7 years ago
Solved

How 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 Function

 

4 Replies

    • jbarrett5g's avatar
      jbarrett5g
      New Contributor

      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

    • Manfred_F's avatar
      Manfred_F
      Regular Contributor

      Thanks so far.

      Now, I  tried to quit the Windows Task, but it didn't work correctly:

      The application window disappeared, but in the Task list, the Excel Task remained there.

      The same is Happening, when I send Alt-F4 keys to the window.

      It seems to be a common Problem to terminate Excel via vbScript.

       

      How could I kill the Excel Task f´rom the Windows tasklist?

      • AlexKaras's avatar
        AlexKaras
        Champion Level 3

        Hi,

         

        Use .Terminate() to kill the task.

        However, if you are working with Excel through UI and OLE simultaneously, then I would recommend:

        -- Set all COM references to Excel/workbooks/sheets/etc. to null in your code (syntax depends on used script language);

        -- With JScript/JavaScript you may also call GC.Collect(); to force garbage collection;

        -- Try to close Excel UI via ExcelProcess.Close(); call

        -- Wait for some time to give Excel a chance to exit.