Forum Discussion

CBleunven's avatar
CBleunven
Contributor
12 years ago

Running Excel as COM object "disable" add-ins

Hi everybody,

We have an add-in for excel for another application and I face a problem with running Excel as a COM object using the following code:



 var Excel = Sys["OleObject"]("Excel.Application");

 Excel["Workbooks"]["Open"](anExcelFilePath);

 Excel["Visible"] = true;



The add-in is not functionnal. If I start Excel manually, the add-in is visible in the ribbon and is OK. If I start Excel through the code upstair, the add-in is not visible and not functional (formula are not calculated and return a #NAME? error).

But, from this Excel instance, if I go under File/Option/Add-Ins, the add-in is present in the "Active Application Add-ins" section.



Is there a way I can run Excel from TC with Add-ins activated ? I need to update some values in the excel sheet before getting results, so I guess DB Table or DDTDriver will not be suitable.



Thank you for help,

regards,

Christophe



EDIT : sorry I've just seen I didn't post in the correct section.
  • hlalumiere's avatar
    hlalumiere
    12 years ago
    Is there a reason you cannot just re-write the one formula in script?



    Also this is from MSDN:



    To load an add-in file when you run Excel as an OLE Automation object, load the add-in manually. Examples of this method are as follows.


    Microsoft Office Excel 2007





    Sub LoadAddin()

       ' Dimension variable xl as object type.
       Dim xl As Object

       ' Activate Microsoft Excel and assign to variable xl.
       Set XL = CreateObject("Excel.Application")

       ' Open the add-in file you want, in this example, XLQUERY.XLAM.
       XL.Workbooks.Open (XL.librarypath & "\MSQUERY\XLQUERY.XLAM")

       ' If you need to register the functions and commands
       ' contained in a resource (XLL), use the RegisterXLL method.
       ' In the example below, all functions of Analys32.xll are
       ' registered.
       ' XL.RegisterXLL "Analys32.xll"

       ' Run any auto macros contained in the add-in file
       ' Auto macros don't run when you open a file
       ' using the Open method.
       XL.Workbooks("xlquery.xlam").RunAutoMacros 1

       Set XL = Nothing
     End Sub




    Microsoft Office Excel 2003 and earlier versions of Excel





    Sub LoadAddin()

       ' Dimension variable xl as object type.
       Dim xl As Object

       ' Activate Microsoft Excel and assign to variable xl.
       Set XL = CreateObject("Excel.Application")

       ' Open the add-in file you want, in this example, XLQUERY.XLA.
       XL.Workbooks.Open (XL.librarypath & "\MSQUERY\XLQUERY.XLA")

       ' If you need to register the functions and commands
       ' contained in a resource (XLL), use the RegisterXLL method.
       ' In the example below, all functions of Analys32.xll are
       ' registered.
       ' XL.RegisterXLL "Analys32.xll"

       ' Run any auto macros contained in the add-in file
       ' Auto macros don't run when you open a file
       ' using the Open method.
       XL.Workbooks("xlquery.xla").RunAutoMacros 1

       Set XL = Nothing
     End Sub



  • hlalumiere's avatar
    hlalumiere
    Regular Contributor
    12 years ago
    Is there a reason you cannot just re-write the one formula in script?



    Also this is from MSDN:



    To load an add-in file when you run Excel as an OLE Automation object, load the add-in manually. Examples of this method are as follows.


    Microsoft Office Excel 2007





    Sub LoadAddin()

       ' Dimension variable xl as object type.
       Dim xl As Object

       ' Activate Microsoft Excel and assign to variable xl.
       Set XL = CreateObject("Excel.Application")

       ' Open the add-in file you want, in this example, XLQUERY.XLAM.
       XL.Workbooks.Open (XL.librarypath & "\MSQUERY\XLQUERY.XLAM")

       ' If you need to register the functions and commands
       ' contained in a resource (XLL), use the RegisterXLL method.
       ' In the example below, all functions of Analys32.xll are
       ' registered.
       ' XL.RegisterXLL "Analys32.xll"

       ' Run any auto macros contained in the add-in file
       ' Auto macros don't run when you open a file
       ' using the Open method.
       XL.Workbooks("xlquery.xlam").RunAutoMacros 1

       Set XL = Nothing
     End Sub




    Microsoft Office Excel 2003 and earlier versions of Excel





    Sub LoadAddin()

       ' Dimension variable xl as object type.
       Dim xl As Object

       ' Activate Microsoft Excel and assign to variable xl.
       Set XL = CreateObject("Excel.Application")

       ' Open the add-in file you want, in this example, XLQUERY.XLA.
       XL.Workbooks.Open (XL.librarypath & "\MSQUERY\XLQUERY.XLA")

       ' If you need to register the functions and commands
       ' contained in a resource (XLL), use the RegisterXLL method.
       ' In the example below, all functions of Analys32.xll are
       ' registered.
       ' XL.RegisterXLL "Analys32.xll"

       ' Run any auto macros contained in the add-in file
       ' Auto macros don't run when you open a file
       ' using the Open method.
       XL.Workbooks("xlquery.xla").RunAutoMacros 1

       Set XL = Nothing
     End Sub



  • CBleunven's avatar
    CBleunven
    Contributor
    12 years ago
    Hi Tanya,

    Effectively I dont need to use the GUI Excel for this part of the test.

    I have Excel files with known values, and I just add the name of the file that must be verified in a specific cell to get the reults in other dedicated cells.

    The problem is that the add-in introduces not only a GUI part, but also new formula that are used to extract the results values to be compared to the expected. Those formula are not recognized when starting Excel as COM object, and so produce this #NAME? error.

    I've actually a workaround, that is I first start Excel from TestedApp, use the Activate command on the main window and get the COM Object:



        //open the Excel File

        var ExcelApp = TestedApps["EXCEL"];

        var anExcelProcess = ExcelApp["Run"]();

        //register as COM object

        anExcelProcess["Window"]("XLMAIN")["Activate"]();

        //and get it

        var ExcelCOM = Sys["OleObject"]("Excel.Application");



    Like this, the add-in and its additional formula are functionnal.

    Thank you for your your help,

    Christophe
  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    12 years ago

    Hi Christophe,


     


    As a rule, using an app via COM doesn't require performing GUI testing. So, if you want to iterate with your add-on via GUI, you can run Excel as a usual tested application. You may need to enable MSAA to get access to Excel controls.


    If you want to use Excel's COM object, you may try playing with the Application.Run method.


     

  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    12 years ago
    Hi Christophe,



    It might be that something is missed in the code of your add-in that must be executed when Excel is aterted as a COM object.

    This link (http://smartbear.com/forums/f74/t50017/How-to-do-ROT-registration-Explicitly-for-an-ex) and the referenced http://blogs.msdn.com/b/andreww/archive/2008/11/30/starting-or-connecting-to-office-apps.aspx one might inspire with some ideas.



    But your approach with explicit Excel launch might appear to be the 'good enough' one.
  • CBleunven's avatar
    CBleunven
    Contributor
    12 years ago
    Hi Hugo,

    the RegisterXLL is exactly what I was looking for.



    The workaround of launching Excel is not functionnal when I want to use another account to run out application and /or Excel.



    Thanks for help,

    Christophe
  • CBleunven's avatar
    CBleunven
    Contributor
    12 years ago
    Hi again,

    Unfortunatlly it seems it doesn't work as expected.

    The result of the RegisterXLL method is "false", and the addin is not loaded.



    What I see when I inspect the ExcelCOM Object is that ExcelCOM["AddIns"]["Item"](5) corresponds to the expected add-in,  for wich the Installed property is "true" but the IsOpen property (which is ReadOnly) is "false". And there is no method to act on.



    When I test


    XL.RegisterXLL "Analys32.xll

    I aslo get a false result. Is there something in Excel I must to configure before being able to run addIn under COM object ?



    Thank you for help.

    regards,

    Christophe




  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    12 years ago

    Hi Christophe,


     


    I've found on the Internet that this may be resolved by specifying Excel's DefaultFilePath:


     




     XL.DefaultFilePath = "C:\\SomePath"


     XL.RegisterXLL "Analys32.xll"


     


      


     Any different thoughts?


      

  • Manfred_F's avatar
    Manfred_F
    Regular Contributor
    10 years ago
    Hi,

    I tried using RegisterXLL, and I tried using the Workaround from Christophe, but both failed.



    Using Excel 2013 on Win 7, RegisterXLL always Returns me FALSE, even If I set the Default File Path to the AddIn's Directory (I use an old .xla as AddIn).

    By the way, it seems as if the DefaultFilePath Setting had an effect only after quitting and restarting the Excel Application..



    If I try to use the Workaround, I get two Excel Tasks: one from TestedApps.Item(i).Run - having AddIns active - and one from Sys.OleObject(Excel.Application") - without active AddIns. As a result, I also do not get COM Access to a valid Excel application here.



    I also tried a third Approach, using Shell.application. This starts a valid Excel app with AddIns, but I do not know, how to get COM Access to it..



    Any ideas?
    • Manfred_F's avatar
      Manfred_F
      Regular Contributor
      10 years ago
      Hi,
      I finally managed to make this thing work for me as follows:
      The trick is to un-install any required AddIn and then re-install it again.
       
       
      Function CreateExcelApp( _
      )
      ' Excel öffnen mit geladenem BDE-Addin
      Const cRoutine = "CreateExcelApp"
      Dim Ergebnis
      Dim myAddIn, Ix, Found
      Const AddInName = "BDE2005.xla"
       
         Set Ergebnis = Sys.OleObject("Excel.Application")
         Ergebnis.Visible = True
         ' enable showing dialog windows on open
         Ergebnis.WindowState = -4143  ' xlNormal
           
         Found = False
         For Ix = 1 To Ergebnis.AddIns.Count
            Set myAddIn = Ergebnis.AddIns(Ix)
            If aqstring.Compare(myAddIn.Name, AddInName, false) = 0 Then
               ' neu installieren -> aktivieren
               myAddin.Installed = False
               myAddin.Installed = True
               Found = True
               Exit for
            End If
         Next     
         
         If not Found Then log.Error "BDE Addin nicht gefunden", mcModul & cRoutine
         Set CreateExcelApp = Ergebnis
      End Function
       
      Kind Regards
       
      Manfred