What requirements are necessary and sufficient for

问题:

The Microsoft Office support article "Add or register an ActiveX control" says:

IMPORTANT: Not all ActiveX controls can be used directly on worksheets; some can be used only on Microsoft Visual Basic for Applications (VBA) UserForms. When you work with these controls, Excel displays the message Cannot insert object if you try to add them to a worksheet.

However, I cannot find documented anywhere the requirements that are necessary and sufficient for a control to be used directly on a worksheet.

I have created a new C++/ATL Project, to which I have added an ATL Control, accepting the defaults throughout. After compiling, building and registering the control, it appears in Excel's list of "More Controls" (accessed under Developer > Insert > ActiveX Controls > More Controls...) but upon attempting to insert into the worksheet one sees this "Cannot insert object" error.

What changes must I make to fix this?

OR

Where are Excel's requirements of ActiveX controls documented?

---

For what it's worth, I've verified that the control generated by the wizard does otherwise work fine (tested with ActiveX Control Test Container, which I built from the Visual C++ 2008 samples pack).

Furthermore, I'm aware that the documentation for the ATL Control wizard's "Appearance" tab describes the "Insertable" checkbox as follows:

Select this option to have your control appear in the Insert Object dialog box of applications such as Word and Excel. Your control can then be inserted by any application that supports embedded objects through this dialog box.

However, this checkbox (which simply adds the "Insertable" subkey to the registry), only causes the control to appear in the Insert > Text > Object dialog—for the avoidance of doubt, I have tried both with and without this checked and the same error is produced either way.

I'm currently comparing traces of Excel's execution paths when attempting to insert my control against that when attempting to insert a working (Forms 2.0) control. The key difference appears to lie in VBE7.dll whilst loading the type library (which the OLE/COM Object Viewer is able to load correctly from my DLL—yet after Excel has performed all the same reads therefrom, it aborts before writing out an EXD)... I'm digging through some assembly right now in the vain hope that I'll figure it out—but surely someone who has built a working control for Excel and knows what I'm missing can spare me this pain?!

---

^{Microsoft Windows 10 Pro v1511 (10.0.10586.164) 64-bit
Microsoft Excel 2016 MSO (16.0.4312.1000) 64-bit
Microsoft Visual Studio Community 2015 (14.0.24720.00 Update 1)}

回答1:

To implement an ATL ActiveX Control insertable into MS Excel Sheet, follow these steps:

1. Make sure you don't have cached ActiveX control information *.exd files in C:\Users\$(UserName)\AppData\Local\Temp\Excel8.0 which might be an unobvious obstacle on the way

2. Create an ATL DLL project with all defaults

2.1. Add x64 configuration as a copy of already existing Win32 - for 64-bit Excel you will need 64-bit ActiveX control

3. Add ATL Control class using wizard

3.1. Make sure to fill ProgID field

3.2. Add IPersistStreamInit on the Interfaces page

4. Build the DLL and have it registered (regsvr32)

5. In Excel the new control is visible in menu Developer, ..., More Controls

6. Insert it and have fun from there

Source Code: Subversion/Trac

UPDATE: A question from comments below:

...whether Excel supports windowless activation?

To see control operation in action let's add some code around there:

CSample()
{
    CTrace::SetLevel(4);

and

HRESULT OnDraw(ATL_DRAWINFO& di)
{
    const CComQIPtr<IOleInPlaceSiteWindowless> pOleInPlaceSiteWindowless = m_spClientSite;
    ATLTRACE(_T("m_spClientSite 0x%p, pOleInPlaceSiteWindowless 0x%p, m_hWnd 0x%08X\n"), m_spClientSite, pOleInPlaceSiteWindowless, m_hWnd);

This going to print out the members of the control that help identification of windowed/windowless mode. The output is (eventually after activating the object or right from the start):

...
Sample.h(118) : atlTraceGeneral - m_spClientSite 0x0000027A9CA7B460, pOleInPlaceSiteWindowless 0x0000000000000000, m_hWnd 0x0105069C
...
Sample.h(118) : atlTraceGeneral - m_spClientSite 0x0000027A9CA7B460, pOleInPlaceSiteWindowless 0x0000000000000000, m_hWnd 0x0105069C

The control can activate both windowed and windowless (unless m_bWindowOnly is set to true in which case windowed mode is forced). The trace shows that control is however in windowed mode, and that container does not have IOleInPlaceSiteWindowless, which is mandatory for windowless.

回答2:

I have some experience with ActiveX and COM technology, but haven't digged up any deeper into Excel's world. To my best understanding all COM components always smells with overcomplexity unless you know exactly what you're doing, but it's not always possible to find what needs to be done.

Anyway - I've quickly scanned / googled - these are links I was able to find:

From here: http://itdocument.com/6551512544/

Excel 97 & 2000 Issues Excel 97 & 2000 Issues Q171280 Q171280, , Error Message "Cannot insert object" in Excel97.

ActiveX controls must support aggregation to be inserted ActiveX controls must support aggregation to be inserted into a spreadsheet. If they do not support being into a spreadsheet. If they do not support being aggregated, Excel will not allow them to be inserted. aggregated, Excel will not allow them to be inserted. More information on this is provided in More information on this is provided in Q143432 Q143432 at the at the bottom of the article. bottom of the article.

Any by trying lookup of article Q143432 quite often ended up with non existing page - but was able to find out this article:

https://support.microsoft.com/en-us/kb/143432

and some problems related to it's usage is reported here:

http://www.verycomputer.com/418_e81ed24b6ac0cb79_1.htm

My recommendation is however is to avoid ActiveX technologies if possible, if not possible - try links above - or may be find some similar open source code example.

Here are some examples which I've managed to find:

This one is in russian: https://habrahabr.ru/post/149277/

This is apparently somehow google translated version of same page: http://developers-club.com/posts/149277/

And it's source code - I think: https://github.com/Lovesan/MyActiveX

I hope that this will help you, however - not sure - haven't tried by myself.