none
Start Excel and get workbook handle RRS feed

  • Question

  • Hi,

    I would like to build a launcher which would start Excel 2013, and then register my xll file using "RegisterXLL".

    One of my prototypes is starting the process, using the path to Excel.exe, and then it gets the window handle (the ultimate goal is to reach the COM application object in order to invoke RegisterXLL).

    Thus I must find the child window whose name is "EXCEL7". I can get the main window handle from the process I have created, that's fine. But in order to get the child handle, I have tried 2 functions : EnumChildWindows and FindWindowExA, which both are part of "user32.dll".

    Unfortunately, I have to wait for a certain amount of time before calling these functions, otherwise they don't work. Basically, I wait for 1 or 2 seconds, then I call EnumChildWindows or FindWindowExA, and it works fine.

    But if I don't wait, then EnumChildWindows does not enumerate anything, and FindWindowExA return 0. And once I have called one of them, I can retry even 10 seconds later, they will still not work.

    Then I have no idea what I must wait for, or how long I must wait.

    I would gratefully appreciate some help !

    Thanks

    Ron


    • Edited by Ron Ldn Monday, February 16, 2015 5:33 PM
    Monday, February 16, 2015 5:32 PM

Answers

  • Hi Peter,

    Thank you for your contribution.

    Yes I have tried this as well. The problem with that approach is the installed add-ins (ticked in the add-ins manager of Excel) are not loaded when the Excel session starts.

    I can workaround this by iterating through the installed add-ins and set the installed property to false and then true.

    foreach (dynamic addIn in application.AddIns)
    {
       if (addIn.Installed)
       {
           addIn.Installed = false;
           addIn.Installed = true;
       }
    }
    It works. However, this is not very elegant, and then I am wondering if everything is really set-up properly. Surely I would need to do a lot of testing with different add-ins installed.

    Anyway, I would like to understand my initial issue. I know how to get the application handle and how to iterate through the children. I even managed to make it work. But in order to do so, I must put a Sleep of a few second between the moment I have started the Excel process and the call to EnumChildWindows or to FindWindowExA.

    I even call the method "WaitForInputIdle" of the process, but this is not enough.

    Regards,

    Ron

    • Marked as answer by Ron Ldn Wednesday, February 18, 2015 1:24 PM
    Tuesday, February 17, 2015 1:22 PM
  • >Do you see anything wrong with using the "Installed" property, like I described in my precedent post ?

    It's not wrong but normally not necessary and if possible better to avoid any addin's install event and a couple of other things behind the scenes. In most cases it's easy to get the path, aircode

    if myaddin.installed then excelApp.workbooks.open myAddin.fullname 

    That said the builtin addins may not expose a path, they will typically be in the "old" excelApp.LibraryPath, but if not toggle the Installed property if that's the only way.

    • Marked as answer by Ron Ldn Wednesday, February 18, 2015 1:23 PM
    Tuesday, February 17, 2015 5:29 PM
    Moderator
  • Going back to my first approach using Windows apis, I noticed that if I refresh the process after a while, then I get a different value for the MainWindowHandle. From that value, I can do the work. 

    Then here is my final solution :

    1) Get the MainWindowHandle

    2) Enumerate the child windows (using FindWindowExA or EnumChildWindows)

    3) If the child window called "EXCEL7" is not found then refresh the process and go to 1). If the window was found then continue.

    It works !

    ProcessStartInfo info = new ProcessStartInfo(excelPath, "/x")
                {
                    UseShellExecute = false,
                    CreateNoWindow = true
                };
    
                Process process = new Process
                {
                    StartInfo = info
                };
    
                process.Start();
    
                IntPtr hWndChild = IntPtr.Zero;
    
                do
                {
                    process.WaitForInputIdle();
    
                    StringBuilder cname = new StringBuilder(256);
                    EnumChildWindows(process.MainWindowHandle, delegate(IntPtr hWndEnum, IntPtr param)
                    {
                        GetClassNameW(hWndEnum, cname, cname.Capacity);
                        if (cname.ToString() == "EXCEL7")
                        {
                            hWndChild = hWndEnum;
                            return false;
                        }
                        return true;
                    }, IntPtr.Zero);
    
                    if (hWndChild == IntPtr.Zero)
                    {
                        Thread.Sleep(100);
                        process.Refresh();
                    }
                } while (hWndChild == IntPtr.Zero);
    
                IntPtr pUnk = IntPtr.Zero;
                int hr = AccessibleObjectFromWindow(hWndChild, OBJID_NATIVEOM, IID_IDispatchBytes, ref pUnk);
    
                if (hr >= 0)
                {
                    dynamic obj = Marshal.GetObjectForIUnknown(pUnk);
                    dynamic app = obj.Application;
                    app.RegisterXLL(xllPath);
                }
            }

    • Marked as answer by Ron Ldn Wednesday, February 18, 2015 4:22 PM
    Wednesday, February 18, 2015 4:22 PM

All replies

  • I have the same behavior with Excel 2010, by the way.
    Monday, February 16, 2015 5:36 PM
  • Hi Ron Ldn,

    According to the description, you want to start the Excel process and get the handler for the specific workbook.

    I suggest that you start the Excel application using Excel object model instead of staring the process via the Excel.exe file since the file may be different on the users's computer.

    And here is a samlpe to automate Excel application to open speicifc workbook and get its handler for your reference:

     string fileName = @"C:\Users\UserName\Desktop\Workbook1.xlsx";
                Application excelApp = new Application();
                excelApp.Visible = true;
                Workbook aWorkbook = excelApp.Workbooks.Open(fileName);
                int handler = aWorkbook.Windows[1].Hwnd;
                Console.WriteLine(handler);
    

    To run this sample code, we need to add the Excel PIAs first. You can get more detail about Office PIAs from link below:

    Welcome to the Excel 2013 Primary Interop Assembly Reference

    Office Primary Interop Assemblies

    Also if you have issues about using Windows API, I suggest that you get more effective response from General Windows Desktop Developement Issues forum.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 17, 2015 5:39 AM
    Moderator
  • Hi Fei,

    Thanks for you suggestion.

    I don't want to use the PIAs because I don't know what can be the environment of the user (what version of Excel, what version of the .Net framework, etc.).

    Moreover, by using the PIAs, I will have to bother about the life cycle of my process and my COM objects. For instance, if I dispose of the Application object, then Excel will close.

    Basically, what I want is to start Excel, register my xll, then exit the program and let Excel run until the user decides to close it.

    My tentative is to get the handle to the window, and finally marshal the Application object so I can call to Application.RegisterXLL. I understand that in order to do it, I must get the handle to a workbook, but I don't need a particular one. The default blank workbook opened by Excel in a new session is fine.

    The thing is I have to wait for a few seconds between the starting of Excel and the call the apis to get the workbook handle. I guess I must wait for the workbook to be loaded. But anyway, if I started looked for the window's children too early, then it is not possible, even later.

    I will try on the Windows desktop development forum as you suggested.

    Regards,

    Ron

    Tuesday, February 17, 2015 8:56 AM
  • I can't answer your xll/RegisterXLL question but just to point out a couple of things.

    To find Workbook EXCEL7 windows from the application handle or XLMAIN window first you need to find the XLDESK window (there's only one), and from that find any or all of its child EXCEL7 windows.

    When you automate an Excel instance no workbooks or addins are loaded, not even the Personal or the default, but easy enough to add it (see below). If you want to hand it to the user in it's normal state you can explicitly load all installed addins, Personal, and the default if you want to.

    You can release your reference to the automated instance but before doing so hand it to the user so it stays open, try this in any Office app with VBA 

    Sub Test()
    Dim objXL As Object
        Set objXL = CreateObject("excel.application")
        
        objXL.Workbooks.Add
        objXL.Visible = True
        objXL.UserControl = True
    End Sub

    Tuesday, February 17, 2015 12:09 PM
    Moderator
  • Hi Peter,

    Thank you for your contribution.

    Yes I have tried this as well. The problem with that approach is the installed add-ins (ticked in the add-ins manager of Excel) are not loaded when the Excel session starts.

    I can workaround this by iterating through the installed add-ins and set the installed property to false and then true.

    foreach (dynamic addIn in application.AddIns)
    {
       if (addIn.Installed)
       {
           addIn.Installed = false;
           addIn.Installed = true;
       }
    }
    It works. However, this is not very elegant, and then I am wondering if everything is really set-up properly. Surely I would need to do a lot of testing with different add-ins installed.

    Anyway, I would like to understand my initial issue. I know how to get the application handle and how to iterate through the children. I even managed to make it work. But in order to do so, I must put a Sleep of a few second between the moment I have started the Excel process and the call to EnumChildWindows or to FindWindowExA.

    I even call the method "WaitForInputIdle" of the process, but this is not enough.

    Regards,

    Ron

    • Marked as answer by Ron Ldn Wednesday, February 18, 2015 1:24 PM
    Tuesday, February 17, 2015 1:22 PM
  • Indeed need to explicitly open addins etc as I said. Unfortunately it does mean a bit of testing but I wouldn't toggle the Installed state as in your example, at least not with xla/m's.  If the addin is installed just load (ie open) the workbook using the addin's fullname property (quicker and avoids any addin install events). Skip anything with a .dll extension; if the addin's name is ANALYS32.XLL open the funcres.xlam in the same path; any other installed xll's toggle installed, and similar with the COMAddins collection, .Connect = false/true. Disable alerts. Don't forget Personal in the XLSTART folder and "Add" a default workbook (if not loading another). The whole process shouldn't take any longer that shelling excel.exe, in effect does the same.

    Presumably with the direct reference to the automated instance you can then do simply excelApp.RegisterXLL "my.xll"

    Tuesday, February 17, 2015 3:26 PM
    Moderator
  • Do you see anything wrong with using the "Installed" property, like I described in my precedent post ?

    In term of code, it will be a lot simpler than finding the path to the add-in and have different way of loading according to the type of add-in. I'd like to keep it as simple as possible.

    If this solution works fine, then I might give up the other solution with the window handles.

    Tuesday, February 17, 2015 4:43 PM
  • >Do you see anything wrong with using the "Installed" property, like I described in my precedent post ?

    It's not wrong but normally not necessary and if possible better to avoid any addin's install event and a couple of other things behind the scenes. In most cases it's easy to get the path, aircode

    if myaddin.installed then excelApp.workbooks.open myAddin.fullname 

    That said the builtin addins may not expose a path, they will typically be in the "old" excelApp.LibraryPath, but if not toggle the Installed property if that's the only way.

    • Marked as answer by Ron Ldn Wednesday, February 18, 2015 1:23 PM
    Tuesday, February 17, 2015 5:29 PM
    Moderator
  • Going back to my first approach using Windows apis, I noticed that if I refresh the process after a while, then I get a different value for the MainWindowHandle. From that value, I can do the work. 

    Then here is my final solution :

    1) Get the MainWindowHandle

    2) Enumerate the child windows (using FindWindowExA or EnumChildWindows)

    3) If the child window called "EXCEL7" is not found then refresh the process and go to 1). If the window was found then continue.

    It works !

    ProcessStartInfo info = new ProcessStartInfo(excelPath, "/x")
                {
                    UseShellExecute = false,
                    CreateNoWindow = true
                };
    
                Process process = new Process
                {
                    StartInfo = info
                };
    
                process.Start();
    
                IntPtr hWndChild = IntPtr.Zero;
    
                do
                {
                    process.WaitForInputIdle();
    
                    StringBuilder cname = new StringBuilder(256);
                    EnumChildWindows(process.MainWindowHandle, delegate(IntPtr hWndEnum, IntPtr param)
                    {
                        GetClassNameW(hWndEnum, cname, cname.Capacity);
                        if (cname.ToString() == "EXCEL7")
                        {
                            hWndChild = hWndEnum;
                            return false;
                        }
                        return true;
                    }, IntPtr.Zero);
    
                    if (hWndChild == IntPtr.Zero)
                    {
                        Thread.Sleep(100);
                        process.Refresh();
                    }
                } while (hWndChild == IntPtr.Zero);
    
                IntPtr pUnk = IntPtr.Zero;
                int hr = AccessibleObjectFromWindow(hWndChild, OBJID_NATIVEOM, IID_IDispatchBytes, ref pUnk);
    
                if (hr >= 0)
                {
                    dynamic obj = Marshal.GetObjectForIUnknown(pUnk);
                    dynamic app = obj.Application;
                    app.RegisterXLL(xllPath);
                }
            }

    • Marked as answer by Ron Ldn Wednesday, February 18, 2015 4:22 PM
    Wednesday, February 18, 2015 4:22 PM